Results 1 to 11 of 11
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy form user defined file (Excel 2003)

    The declaration

    Dim Fullfie As Workbook

    contains a typo (the name should be FullFile) and an error: the variable should be declared as a Variant, not as a Workbook.

    You forgot to exit the macro if the user doesn't select a workbook.

    Also, it's possible (and more efficient) to avoid selecting sheets and cells explicitly while copying and pasting. Try this version:

    Sub Special()
    Dim n As Long
    Dim FullFile As Variant
    Dim WshSource As Worksheet
    Dim WshTarget As Worksheet
    Dim Caption As String
    Dim TheUser
    Caption = "Please Select a File" & TheUser
    FullFile = Application.GetOpenFilename _
    ("Excel files (*.xl*), *.xl*", 1, Caption, , False)
    If VarType(FullFile) = vbBoolean Then
    MsgBox "No File Specified", vbExclamation
    Exit Sub
    End If
    Set WshTarget = Workbooks("fit_assessment_allocation_template.xls" ) _
    .Worksheets("scenario 1 -9and3")
    Set WshSource = Workbooks.Open(Filename:=FullFile).Worksheets("She et1")
    n = WshSource.Cells(WshSource.Rows.Count, 2).End(xlUp).Row
    WshSource.Range("B12:B" & n).End(xlUp).Copy _
    Destination:=WshTarget.Range("B24")
    End Sub

  2. #2
    Star Lounger
    Join Date
    Nov 2003
    Location
    Tampa, Florida, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy form user defined file (Excel 2003)

    Hans:
    The code worked great up to the point:
    Set WshTarget = Workbooks("fit_assessment_allocation_template.xls" ) _
    .Worksheets("scenario 1 -9and3")
    Where there is a error code #9 Subscript out of range.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy form user defined file (Excel 2003)

    That means that the name of either the workbook or the worksheet is misspelled.

  4. #4
    Star Lounger
    Join Date
    Nov 2003
    Location
    Tampa, Florida, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copy from user defined file (Excel 2003)

    Edited by HansV to correct spelling error in subject

    I am trying to complete the code that will ask the user to find and open a workbook then copy the data from the source workbook sheet 1 to a specified workbook "fit_assessment_allocation_template2.xls." sheet "scenario 1 -9and3". The workbook "fit_assessment_allocation_template2.xls." and sheet "scenario 1 -9and3" is already open and holds the code in Module 1.
    The following code gives a runtime error 13 Type mismatch at "WshSource.Range("B12:B" + n).End(xlUp).Copy".
    Can you help with a correction.I've been tweaking and adjusting and googling for hours to no avail. Attached is the "fit_assessment_allocation_template2.xls." sheet "scenario 1 -9and3" destination file.

    Sub Special()
    Dim n As Long
    Dim Fullfie As Workbook
    Dim WshSource As Worksheet
    Dim WshTarget As Worksheet
    Caption = "Please Select a File" & TheUser
    Fullfile = Application.GetOpenFilename _
    ("Excel files (*.xl*), *.xl*", 1, Caption, , False)
    If Fullfile = False Then
    MsgBox "No File Specified", vbExclamation
    End If
    Workbooks.Open Filename:=Fullfile
    Set WshSource = Worksheets("Sheet1")
    WshSource.Activate
    n = Cells(Rows.Count, 2).End(xlUp).Row
    WshSource.Range("B12:B" + n).End(xlUp).Copy
    Windows("fit_assessment_allocation_template.xls"). Activate
    Set WshTarget = Worksheets("scenario 1 -9and3")
    WshTarget.Activate
    Range("B24").Select
    ActiveSheet.Paste
    End Sub
    Attached Files Attached Files

  5. #5
    Star Lounger
    Join Date
    Nov 2003
    Location
    Tampa, Florida, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy form user defined file (Excel 2003)

    Hans:
    You were right as always. I corrected the spelling and tried it out.

    It worked but only copied Cell B12 and not the range B12:B +n

    I've attached a sample file that I tried the code against if it helps
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy form user defined file (Excel 2003)

    Sorry, I should have seen that. Change the instruction

    WshSource.Range("B12:B" & n).End(xlUp).Copy _
    Destination:=WshTarget.Range("B24")

    to

    WshSource.Range("B12:B" & n).Copy _
    Destination:=WshTarget.Range("B24")

    i.e. remove .End(xlUp) because it collapses the range to its first cell.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy form user defined file (Excel 2003)

    No, you don't have to use Application.CutCopyMode = False in between, only at the very end.

  8. #8
    Star Lounger
    Join Date
    Nov 2003
    Location
    Tampa, Florida, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy form user defined file (Excel 2003)

    Hans:
    That was perfect!! Thank-you

    Now if I want to next loop through each column until last column with data and for each row from row12 to the last row with data
    would I first code CutCopyMode=False
    Then copy the initial code again for each column?

  9. #9
    Star Lounger
    Join Date
    Nov 2003
    Location
    Tampa, Florida, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy form user defined file (Excel 2003)

    Thanks Hans.

  10. #10
    Star Lounger
    Join Date
    Nov 2003
    Location
    Tampa, Florida, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy form user defined file (Excel 2003)

    I have tried the code out at work and find I have a problem.
    The code section
    n=WshSource.Cells(WshSource.Rows.Count, 2).End(xlup).Row
    WshSource.Range("B12:B" & n).Copy _

    copies all of the cells in the column including some close to the bottom that have instructions for completing the worksheet in them.

    How can the code be changed to copy down to the first cell with no data in it ? Or some way so that the instructions for completing the worksheet are not copied along with the rest of the data

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy form user defined file (Excel 2003)

    Put the instructions in another column than column B.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •