Need to identify source of 424 VBA error

[VAPALIPOT]

Hi guys,
I have not posted recently or worked with VBA in some time and am now trying to write this VBA program to transfer a copy of the same profile limits from a static file ("Profile" file) to a series of result files (listed as Gen5 Result files or Destination). I wrote this code but it is giving me a 424 error at the first point I am trying to paste into the destination workbook ("wbDest"). I think that the Paste statement is written incorrectly but not sure.

Sub DAS_Test()
    
        Dim wbDest As Workbook
        Dim fDestFileNameAndPath As Variant
     'Opens Destination file (Gen5 Results File)
         fDestFileNameAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls),*.xls", Title:="Select Gen5 Result File To Be Opened")
            If fDestFileNameAndPath = False Then Exit Sub
            Set wbDest = Workbooks.Open(fDestFileNameAndPath)
    'Opens Profile source File ("**MethodProfile"
        Workbooks.Open Filename:="C:\Result Review Profiles\ProreninMethodProfile.xls"
    'Copy data from the Profile file------------------------------
        Windows("ProreninMethodProfile.xls").Activate
        Range("A1:D15").Copy
    'Paste into the Lab Result file------------------------
        wbDest("Sheet1").Range("M17:P31").PasteSpecial xlPasteAll
    'Copy data from Profile file------------------------------
        Windows("ProreninMethodProfile.xls").Activate
            Range("B19:B93").Select
            Application.CutCopyMode = False
            Selection.Copy
    'Paste into the Lab Result file------------------------
        wbDest("Sheet1").Activate
            Range("I76:I150").Select
            Range("I76:I150").PasteSpecial xlPasteAll
    'Close the Profile file without saving it-------------------
         Windows("ProreninMethodProfile.xls").Activate
            ActiveWindow.Close
    'Close the Gen5 Result file, save it, then close it-------------------
        Application.CutCopyMode = False
        wbDest("Sheet1").Activate
        ActiveWorkbook.Save
        ActiveWindow.Close

    End Sub

Thanks for your help.

 

[btorrean]

Sorry, I only have a second to post, but it looks like you are not setting the wbDest variable properly. Before the line:

wbDest("Sheet1").Range("M17:P31").PasteSpecial xlPasteAll

You would need a line of code like:

Set wbDest = ActiveWorkbook.Sheets("Sheet1")

Give that a shot. Sorry, don't have time to test it right now.

Brian

Brian J. Torreano

 

[JLGWhiz]

or
syntax problem. This

wbDest("Sheet1")

Should be this

wbDest.Sheets("Sheet1")

The wbDest set statement is OK.

 

[VAPALIPOT]

My thanks for responses from btorrean and JLGWhiz. Unfotrunately, neither suggestion resolved the problem I tried both and each one triggered a new error. Here is the revised code with btorrean code commented out. I did try the code from JLGWhiz as well and here it is in the revised list:

 Sub DAS_Test()
    
        Dim wbDest As Workbook
        Dim fDestFileNameAndPath As Variant
     'Opens Destination file (Gen5 Results File)
         fDestFileNameAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls),*.xls", Title:="Select Analyte Profile File To Be Opened")
            If fDestFileNameAndPath = False Then Exit Sub
            Set wbDest = Workbooks.Open(fDestFileNameAndPath)
            'Set wbDest = ActiveWorkbook.Sheets("Sheet1")
    'Opens Source File ("**MethodProfile"
        Workbooks.Open Filename:="C:\Result Review Profiles\ProreninMethodProfile.xls"
    'Copy data from the Profile file------------------------------
        Windows("ProreninMethodProfile.xls").Activate
        Range("C1:C15").Copy
    'Paste into the Lab Result file------------------------
        wbDest.Sheets("Sheet1").Range("O17:O31").PasteSpecial xlPasteAll
    'Copy data from Profile file------------------------------
        Windows("ProreninMethodProfile.xls").Activate
            Range("B19:B93").Select
            Application.CutCopyMode = False
            Selection.Copy
    'Paste into the Lab Result file------------------------
        wbDest.Sheets("Sheet1").Range("I76:I150").PasteSpecial xlPasteAll
    'Close the Profile file without saving it-------------------
         Windows("ProreninMethodProfile.xls").Activate
            ActiveWindow.Close
    'Close the Gen5 Result file, save it, then close it-------------------
        Application.CutCopyMode = False
        wbDest("Sheet1").Activate
        ActiveWorkbook.Save
        ActiveWindow.Close

    End Sub

[\code]

Here is the error I got from GLGWhiz code change: 
   Method 'Sheets' of object "_workbook failed
  Should I instead only keep one workbook open at a time and close the other?  Maybe copy the contents of the "PreoreninProfile" onto the desktop and then close the workbook and copy from the desktop to the openfile?
Thanks for your help,

 

[Caleeco]

Hello,

As stated above, you have incorrectly defined where the worksheet is. I have done a QUICK edit, untested, but should give you an idea how to set workbooks and worksheets. I have assumed you are working with SHEET1 on the MethodProfile workbook.

Sub DAS_Test()






Dim wbDest As Workbook, wbMethod As Workbook
Dim wsDest As Worksheet, wsMethod As Workbook
Dim fDestFileNameAndPath As Variant


'Opens Destination file (Gen5 Results File)
fDestFileNameAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls),*.xls", Title:="Select Analyte Profile File To Be Opened")
If fDestFileNameAndPath = False Then Exit Sub
Workbooks.Open (fDestFileNameAndPath)


Set wbDest = ActiveWorkbook
Set wsDest = wbDest.Sheets("Sheet1")
'Opens Source File ("**MethodProfile"
Workbooks.Open Filename:="C:\Result Review Profiles\ProreninMethodProfile.xls"
'Copy data from the Profile file------------------------------
Windows("ProreninMethodProfile.xls").Activate
Set wbMethod = ActiveWorkbook
Set wsMethod = wbMethod.Sheets("Sheet1")
wsMethod.Range("C1:C15").Copy
'Paste into the Lab Result file------------------------
wbDest.Activate
wsDest.Range("O17:O31").PasteSpecial xlPasteAll
'Copy data from Profile file------------------------------
wbMethod.Activate
wsMethod.Range("B19:B93").Copy
'Paste into the Lab Result file------------------------
wsDest.Range("I76:I150").PasteSpecial xlPasteAll
'Close the Profile file without saving it-------------------
wbMethod.Activate
ActiveWindow.Close
'Close the Gen5 Result file, save it, then close it-------------------
Application.CutCopyMode = False
wbDest("Sheet1").Activate
ActiveWorkbook.Save
ActiveWindow.Close


End Sub

Caleeco

 

[JLGWhiz]

See if this works.

Sub DAS_Test()
Dim wbDest As Workbook, destSh As Worksheet, srcSh As Worksheet
Dim fDestFileNameAndPath As Variant
'Opens Destination file (Gen5 Results File)
fDestFileNameAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls),*.xls", Title:="Select Analyte Profile File To Be Opened")
    If fDestFileNameAndPath = False Then Exit Sub
Set wbDest = Workbooks.Open(fDestFileNameAndPath)
Set destSh = wbDest.Sheets(1)
'Opens Source File ("**MethodProfile"
Workbooks.Open Filename:="C:\Result Review Profiles\ProreninMethodProfile.xls"
Set srcSh = Workbooks("ProreninMethodProfile.xls").Sheets("Sheet1")
'Copy data from the Profile file------------------------------
srcSh.Range("C1:C15").Copy destSh.Range("O17")
'Copy data from Profile file------------------------------
Workbooks("ProreninMethodProfile.xls").Range("B19:B93").Copy destSh.Range("I76")
'Close the Profile file without saving it-------------------
Workbooks("ProreninMethodProfile.xls").Close False
'Close the Gen5 Result file, save it, then close it-------------------
wbDesrt.Close True
End Sub

 

[btorrean]

Thanks, Caleeco and JLGWhiz! In my rush, I didn't read the code as thoroughly as I should have. Mistakenly thought at the time that wbDest was a worksheet and not a workbook. My bad!

Brian

Brian J. Torreano

 

[JLGWhiz]

This is bad syntax and will generate an error.

wbDest("Sheet1").Activate

This is the correct syntax for a workbook and sheet reference

wbDest.Sheets("Sheet1").Activate

 

[VAPALIPOT]

My apologies for not responding to the contributions from JLGWhiz, Caleeco and btorrean as I went on vacation right after my last post. Since reading your replies, I got the program up and running and your suggestions are certainly welcomed and appreciated.
Best regards,
VAPALIPOT