Copy sheets based on cell value and paste to a separate excel

[Stallios]

Hi Guys

I have a a few string stored in column A of a excel workbook "Book1"

I want VBA to pick up the names and copy the same named sheets that i have in another excel "Book2" and paste it to book1

Set ListC = Range("C2:C100")
Windows("Book2").Activate
For Each c In ListC

Sheets(c).Copy After:=Workbooks("Book1.xlsm").Sheets(Workbooks("Book1.xlsm").Sheets.Count)
Next c

End Sub

 

[Fluff]

Hi & welcome to MrExcel.
In what way doesn't your code work?

 

[Stallios]

in modified the code

Sub Button2_Click()
Windows("Book1.xlsm").Activate
Set ListC = Range("C2:C100")

For Each c In ListC

Windows("Book 2").Activate
FormulaString = Replace(c, Chr(126), Chr(34))
Sheets(FormulaString).Activate
Cells.Select
Range("A19").Activate
Selection.Copy
Windows("Book1.xlsm").Activate
Sheets.Add After:=ActiveSheet
ActiveSheet.Paste
Range("J10").Select
Next c

End Sub

getting a run time error

 

[Fluff]

What is the error & which line doe it fail on?