VBA Declaring a workbook

rplohocky

Active Member
Joined
Sep 25, 2005
Messages
292
Office Version
  1. 365
Platform
  1. Windows
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?
Code:
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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Either the workbook name is wrong, or it isn't currently open in that instance of Excel.
 
Upvote 0
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.
Code:
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.
Code:
' 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.
 
Upvote 0
What is the actual name of the workbook as it appears in the application caption?
 
Last edited:
Upvote 0
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.


Code:
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!
 
Upvote 0
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.
Code:
 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
 
Upvote 0
Change Paste to PasteSpecial
 
Upvote 0
Edit -- Fluff's on the right track... Paste ain't a RANGE method. It's a worksheet method.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top