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 Before:=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!

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 Before:=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!

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 Before:=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!