error "Set wb = Workbooks(FilePathName)"

[Sharon]

I'm sorry, wasn't sure how to catagorize this in the subject line.........
I'm trying to copy tabs from a non-active workbook and paste them to an
active workbook, however I must be doing this all wrong.
Can anyone help me? Thanks.




Dim FilePathName As String
FilePathName = "Q:\Finance\Forecast\2007\" & TimePeriod & "\Cons Fin\" &
strName & ".xls"

'clears/resets objects for finding latest modified file
Set oFSO = Nothing
Set oFile = Nothing
Set oFolder = Nothing


' copy ConsFin Tabs to Tri Interest & Interco Report
Dim wb As Workbook
Set wb = Workbooks(FilePathName)
wb.Sheets(5).Copy ActiveWorkbook.Sheets("TRI - Dec Int Inc")
wb.Sheets(7).Copy ActiveWorkbook.Sheets("TRI - Int Abov EBITDA")
wb.Close True
Set wb = Nothing

 

[Jay]

Hi Sharon -

You have the general idea, but you just have some of the object-oriented
programming concepts wrong. The primary problem with the code you posted was
that the statement "Set wb=Workbooks(FilePathName)" is trying to capture the
unopened workbook with the variable 'wb' (which is good), but the Workbooks
property that you are using to do that only applies to open workbooks.
Therefore, if you open the workbook first and make a minor syntax change,
this portion of your code will run properly. This can be done with
"Workbooks.Open" statement as in the code below.

One other minor problem is that your Copy statements need an adjustment to
tell VBA where to insert the copy (see the 'before:=' argument in the code
below). You also can't name the sheet in the copy statement. The copied
sheet carries the name from the source workbook, so just add a statement to
rename the sheet after it is copied to its destination.

I think the code below will do the job for you, but you need to supply
values for strName and TimePeriod. Also, the code copies the worksheets to
the front of the ActiveWorkbook; adjust the index on
"...before:=wb1.Worksheets(1)" to copy the worksheet to some other location.
----------------------------------------
Sub Sharon()

Dim FilePathName As String
Dim strName As String
Dim wb1 As Workbook
Dim wb2 As Workbook

Set wb1 = ActiveWorkbook

strName = "Your File Name Here" '<<=====modify to suit
TimePeriod = "Your Time Period Here" ''<<=====modify to suit
FilePathName = "Q:\Finance\Forecast\2007\" & TimePeriod & "\Cons Fin\" & _
strName & ".xls"

Workbooks.Open FilePathName

' copy ConsFin Tabs to Tri Interest & Interco Report
Set wb2 = ActiveWorkbook 'source workbook just opened
wb2.Sheets(5).Copy before:=wb1.Worksheets(1)
wb1.ActiveSheet.Name = "TRI - Dec Int Inc"

wb2.Sheets(7).Copy before:=wb1.Worksheets(1)
wb1.ActiveSheet.Name = "TRI - Int Abov EBITDA"

'Wrap procedure
wb2.Close savechanges:=False
FilePathName = Empty
strName = Empty
Set wb1 = Nothing
Set wb2 = Nothing

End Sub

 

[Jon Peltier]

A bit shorter to post the corrected line:

Set wb=Workbooks.Open(FilePathName)

which opens the workbook in question and sets the variable to it. If the
workbook is already open, you need only the workbook name without the path:

Dim FileName As String
Dim wb As Workbook
FileName = strName & ".xls"
Set wb = Workbooks(FilePathName)

If necessary, you could then compare the already open wb.FullName with
FilePathName to make sure it's not just a file with the same name from a
different directory.

- Jon

 

[Sharon]

Thank you Jon. Jay's "fix" worked with your below correction.

Regards,
Sharon

 

[Sharon]

Thank you Jay! Works like a charm. Yes, I definately need to study further
regards to progamming concepts....maybe find some tutorials on the web.

Regards,
Sharon