VBA to copy worksheets from another workbook

[asad]

Hello All,

i am trying to copy all the worksheets from and external workbook saved in another folder into current workbook. So far, I have this:

Sub CopyWorksheets()

Dim sh As Worksheet, srcwb As Workbook, trgtwb As Workbook, strng As String
strng = "S:\Planning and scheduling\Rosters\Master Dispatch\Master Dispatch - " & Format(ThisWorkbook.Worksheets("Express").Range("K3").Value, "d mmmm yyyy") & ".xlsm"
[COLOR=#ff0000][B]srcwb = Workbooks(strng)[/B][/COLOR]
trgtwb = ThisWorkbook
For Each sh In Workbooks(srcwb).Worksheets

   sh.Copy After:=trgtwb.Sheets(trgtwb.Sheets.Count)
Next sh


End Sub

The line in red above gets highlighted and the code does not give the intended result. How can I fix this?

Thanks
Asad

 

[Trebor76]

You need to use the Set command when opening a workbook where you're using a workbook variable like you are i.e.:

Set srcwb = Workbooks(strng).Open

Robert

 

[asad]

Thanks for the reply Robert. But it still did not work. The code now reads:

Sub CopyWorksheets()

Dim sh As Worksheet, srcwb As Workbook, trgtwb As Workbook, strng As String
strng = "S:\Planning and scheduling\Rosters\Master Dispatch\Master Dispatch - " & Format(ThisWorkbook.Worksheets("Express").Range("K3").Value, "d mmmm yyyy") & ".xlsm"
Set srcwb = Workbooks(strng).Open
trgtwb = ThisWorkbook
For Each sh In Workbooks(srcwb).Worksheets
   sh.Copy After:=trgtwb.Sheets(trgtwb.Sheets.Count)
Next sh


End Sub

May be I did not get what you tried telling me.

 

[Norie]

Try this.

Sub CopyWorksheets()

Dim sh As Worksheet, srcwb As Workbook, trgtwb As Workbook, strng As String

    strng = "S:\Planning and scheduling\Rosters\Master Dispatch\Master Dispatch - " & Format(ThisWorkbook.Worksheets("Express").Range("K3").Value, "d mmmm yyyy") & ".xlsm"

    Set srcwb = Workbooks.Open(strng)

    srcwb.Sheets.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

End Sub

 

[asad]

Perfect Norie.
Worked like a charm.

Thanks a lot.
Asad

 

[asad]

Try this.

Sub CopyWorksheets()

Dim sh As Worksheet, srcwb As Workbook, trgtwb As Workbook, strng As String

    strng = "S:\Planning and scheduling\Rosters\Master Dispatch\Master Dispatch - " & Format(ThisWorkbook.Worksheets("Express").Range("K3").Value, "d mmmm yyyy") & ".xlsm"

    Set srcwb = Workbooks.Open(strng)

    srcwb.Sheets.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

End Sub

One more favour please. After the sheets have been copied, can I get the source workbook to close again. I don't want it to leave it open.

 

[Norie]

Add this at the end.

srcwb.Close SaveChanges:=False

 

[asad]

Add this at the end.

srcwb.Close SaveChanges:=False

Thanks a lot Norie.