Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
error "Set wb = Workbooks(FilePathName)"
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
error "Set wb = Workbooks(FilePathName)"
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 'befo=' 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 "...befo=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 befo=wb1.Worksheets(1) wb1.ActiveSheet.Name = "TRI - Dec Int Inc" wb2.Sheets(7).Copy befo=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 -- Jay "Sharon" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
error "Set wb = Workbooks(FilePathName)"
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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Jay" wrote in message ... 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 'befo=' 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 "...befo=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 befo=wb1.Worksheets(1) wb1.ActiveSheet.Name = "TRI - Dec Int Inc" wb2.Sheets(7).Copy befo=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 -- Jay "Sharon" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
error "Set wb = Workbooks(FilePathName)"
Thank you Jon. Jay's "fix" worked with your below correction.
Regards, Sharon "Jon Peltier" wrote: 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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Jay" wrote in message ... 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 'befo=' 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 "...befo=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 befo=wb1.Worksheets(1) wb1.ActiveSheet.Name = "TRI - Dec Int Inc" wb2.Sheets(7).Copy befo=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 -- Jay "Sharon" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
error "Set wb = Workbooks(FilePathName)"
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 "Jay" wrote: 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 'befo=' 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 "...befo=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 befo=wb1.Worksheets(1) wb1.ActiveSheet.Name = "TRI - Dec Int Inc" wb2.Sheets(7).Copy befo=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 -- Jay "Sharon" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking workbooks; experiencing random "#n/a" error | Excel Discussion (Misc queries) | |||
Multiple "source" workbooks linked to single "destination" workboo | Excel Worksheet Functions | |||
What is Error "Method "Paste" of object "_Worksheet" failed? | Excel Programming | |||
use variable in Workbooks("book1").Worksheets("sheet1").Range("a1" | Excel Programming | |||
"Subscript out of range" error for: Workbooks("Test1.xls").Save | Excel Programming |