Copying Worksheet from one workbook to another

[Barb Reinhardt]

I have the following bit of code

Set newWS =
myWS.Copy(After:=CombinedWB.Sheet(CombinedWB.Worksheets.Count))

newWS and myWS are worksheets and CombinedWB is a workbook.

I'm getting a compile error (Expected Function or Variable) on COPY. What
am I missing? I've done this before and can't find the example.

Thanks,

Barb Reinhardt

 

[Jim Thomlinson]

Copy does not return the new worksheet that it creates (unlike the add
method) so you can not set your worksheet reference to it. So for example
this works

Dim wks As Worksheet

Sheet1.Copy After:=Sheet3
Set wks = ActiveSheet

In your case it is going to be a tad bit more complicated. You can use the
index number of the sheet though to set your worksheet object. Untested but
this might do it...

myWS.Copy(After:=CombinedWB.Sheet(CombinedWB.Worksheets.Count))
Set newWS = CombinedWB.Sheet(CombinedWB.Worksheets.Count)