Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Worksheets in Different Books to One Workbook
I have a list of Workbook paths and adjacent to it a list of Worksheets. I
want to copy the listed worksheets which are all in different workbooks into a single, new workbook. This is the code I have so far: Sub MasterBook() ActiveWorkbook.Sheets("Master").Select Cells.Select Selection.Copy Set wso = Application.Workbooks.Add Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=Fasle, Transpose:=False RowCount = 11 Do While Range("A" & RowCount) < "" If Range("D" & RowCount) = "yes" Then Set BookName = Range("L" & RowCount) Set SheetName = Range("M" & RowCount) Workbooks(BookName).Sheets(SheetName).Copy Befo=wso.Sheets(1) End If RowCount = RowCount + 1 Loop End Sub Range L has the workbook name in the form of a full file path. The file path has worked for another macro which I used to create that file. Range M has the worksheet name. In column D is where I identify whether I want to bring in that specific sheet or not. It's getting hung up in the Workbooks(BookName)... line with a type mismatch. Please help with this code or suggest an alternative way of doing this. Thank you very much for your help! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Worksheets in Different Books to One Workbook
You can certainly do this, but you'll have to put in a little time to get it
right. Look at these resources: http://www.rondebruin.nl/copy2.htm http://www.rondebruin.nl/copy3.htm http://www.rondebruin.nl/fso.htm HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ajd" wrote: I have a list of Workbook paths and adjacent to it a list of Worksheets. I want to copy the listed worksheets which are all in different workbooks into a single, new workbook. This is the code I have so far: Sub MasterBook() ActiveWorkbook.Sheets("Master").Select Cells.Select Selection.Copy Set wso = Application.Workbooks.Add Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=Fasle, Transpose:=False RowCount = 11 Do While Range("A" & RowCount) < "" If Range("D" & RowCount) = "yes" Then Set BookName = Range("L" & RowCount) Set SheetName = Range("M" & RowCount) Workbooks(BookName).Sheets(SheetName).Copy Befo=wso.Sheets(1) End If RowCount = RowCount + 1 Loop End Sub Range L has the workbook name in the form of a full file path. The file path has worked for another macro which I used to create that file. Range M has the worksheet name. In column D is where I identify whether I want to bring in that specific sheet or not. It's getting hung up in the Workbooks(BookName)... line with a type mismatch. Please help with this code or suggest an alternative way of doing this. Thank you very much for your help! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Worksheets in Different Books to One Workbook
I was able to adapt that to generally achieve what I was looking for. Thanks.
"ryguy7272" wrote: You can certainly do this, but you'll have to put in a little time to get it right. Look at these resources: http://www.rondebruin.nl/copy2.htm http://www.rondebruin.nl/copy3.htm http://www.rondebruin.nl/fso.htm HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ajd" wrote: I have a list of Workbook paths and adjacent to it a list of Worksheets. I want to copy the listed worksheets which are all in different workbooks into a single, new workbook. This is the code I have so far: Sub MasterBook() ActiveWorkbook.Sheets("Master").Select Cells.Select Selection.Copy Set wso = Application.Workbooks.Add Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=Fasle, Transpose:=False RowCount = 11 Do While Range("A" & RowCount) < "" If Range("D" & RowCount) = "yes" Then Set BookName = Range("L" & RowCount) Set SheetName = Range("M" & RowCount) Workbooks(BookName).Sheets(SheetName).Copy Befo=wso.Sheets(1) End If RowCount = RowCount + 1 Loop End Sub Range L has the workbook name in the form of a full file path. The file path has worked for another macro which I used to create that file. Range M has the worksheet name. In column D is where I identify whether I want to bring in that specific sheet or not. It's getting hung up in the Workbooks(BookName)... line with a type mismatch. Please help with this code or suggest an alternative way of doing this. Thank you very much for your help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying Worksheets from one workbook to another | Excel Programming | |||
copying all worksheets into one workbook | Excel Programming | |||
copying worksheets to a new workbook without formulae referencing original workbook | Excel Programming | |||
Copying worksheets to a new workbook | Excel Discussion (Misc queries) | |||
Trouble copying worksheets between books - is there size limit? | Excel Worksheet Functions |