VBA Declaring a workbook

[rplohocky]

Hello,
I found this code below and modified it to work for my purpose. It goes into a workbook and copies a range into another workbook in a specific sheet.

I keep getting an error on the "Set wb1 = Workbooks" line. It appears it can't find the WB. Can someone tell me what I'm missing?

Sub CopyFromClosedCombinedInventoryWB()
'This macro copies the combined data fropm a closed WB which holds the inventory data.

' Defines variables
    Dim wb1 As Workbook, wb2 As Workbook
    Dim dteProcess As String
    dteProcess = Format(Date, "dd-mmm-yyyy") & ".xls"
    
' Disable screen updating to reduce screen flicker
    Application.ScreenUpdating = False

' Define which workbook is which
    Set wb1 = Workbooks("Securitas Rental Report " & dteProcess) '*****HERE IS WHERE THE ERROR OCCURS************
    Set wb2 = Workbooks.Open("\\fleet.ad\data\Data1\VMSSHARE\FS\FPSCOEASSO\Temporary Fleet Reports\Securitas Rental Report\Inventory Report Depository\Combined Inventory Report.xlsx")

' Copy range A1:W70 from the Data sheet of wb2
    wb2.Sheets("Combined").Range("A1:D65000").Copy
' Paste the copied data to range A1 of the Data sheet in wb1
    wb1.Sheets("Temp Inventory").Range("A1").Paste

' Close wb2
    wb2.Close

' Re-enable screen updating
    Application.ScreenUpdating = True

End Sub

 

[RoryA]

Either the workbook name is wrong, or it isn't currently open in that instance of Excel.

 

[rplohocky]

Either the workbook name is wrong, or it isn't currently open in that instance of Excel.

So you're saying that you don't see anything wrong with this code and it should work as long as the WB in question is open??? The one in question is open which is why I'm having difficulty finding the problem. Here is the code I use to save the WB.

Sub SaveAsGambleFinalReport()

'Below does a saveas for the final Gamble report into a final folder.
    Application.DisplayAlerts = False ' suppress overwrite warning message
    ActiveWorkbook.Saveas Filename:="\\fleet.ad\data\Data1\VMSSHARE\FS\FPSCOEASSO\Temporary Fleet Reports\Securitas Rental Report\Final Report\Securitas Rental Report " & Format(Date, "dd-mmm-yyyy") & ".xls"
    Application.DisplayAlerts = True
        
End Sub

I believe it may have something to do with the date format which may be why it can't find it.

' Defines variables
    Dim wb1 As Workbook, wb2 As Workbook
    Dim dteProcess As String
    dteProcess = Format(Date, "dd-mmm-yyyy") & ".xls"

' Define which workbook is which
    Set wb1 = Workbooks("Securitas Rental Report " & dteProcess)
    Set wb2 = Workbooks.Open("\\fleet.ad\data\Data1\VMSSHARE\FS\FPSCOEASSO\Temporary Fleet Reports\Securitas Rental Report\Inventory Report Depository\Combined Inventory Report.xlsx")

If this you can't see anything wrong with this do you have another suggestion to copy one sheet from a closed WB to an open one? I looked for a few hours but can't find anything, this code was the closest thing I could find.

 

[RoryA]

What is the actual name of the workbook as it appears in the application caption?

 

[Greg Truby]

The best way to avoid getting entangled in this kinda briar is to simply create a common function to generate the filename consistently, i.e.

Function fnGenerateSecRentFilename(ByVal dtmReportDate As Date) As String


    Let fnGenerateSecRentFilename = "Securitas Rental Report " & Format(dtmReportDate, "dd-mmm-yyyy") & ".xls"


End Function


Sub SaveAsGambleFinalReport()


    Const c_strPath As String = "\\fleet.ad\data\Data1\VMSSHARE\FS\FPSCOEASSO\Temporary Fleet Reports\Securitas Rental Report\Final Report\"
    
    Dim strFilename As String
    
    Let strFilename = fnGenerateSecRentFilename(Date)


'Below does a saveas for the final Gamble report into a final folder.
    Application.DisplayAlerts = False ' suppress overwrite warning message
    ActiveWorkbook.SaveAs Filename:=c_strPath & strFilename
    Application.DisplayAlerts = True
        
End Sub


'...... snip ...........
' Defines variables
    Dim wb1 As Workbook, wb2 As Workbook
    Dim strFilename  As String
    
    Let strFilename = fnGenerateSecRentFilename(Date)


' Define which workbook is which
    Set wb1 = Workbooks(strFilename)
'...... snip ...........

Truth be told, I just stopped by to say "howdy" to ol' Rory... Top o' the morning (at least over here on this side of the Atlantic), Mr. Archibald!

 

[rplohocky]

What ius the actual name of the workbook as it appears in the application caption?

The name of the WB for where the data should be pasted to is "Securitas Rental Report 31-Aug-2018.xls".

I ran the macro again and the highlighted area moved to this spot now.

 wb1.Sheets("Temp Inventory").Range("A1").Paste

I verified that WB has a sheet named "Temp Inventory". I know I'm not the brightest guy around but I can clearly see that there's a sheet there, so confused.

Here's the new warning I get.

Object doesn't support this property or method

 

[Fluff]

Change Paste to PasteSpecial

 

[Greg Truby]

Edit -- Fluff's on the right track... Paste ain't a RANGE method. It's a worksheet method.

 

[rplohocky]

Change Paste to PasteSpecial

That did it! Thanks to both of you guys for all the help!