Thinking further, you entire code can be reduced to a single line of
code:


Worksheets("VAT Invoice").Range("A1:J37").Copy _
Destination:=Worksheets(Worksheets("VAT Invoice") _
.Range("B4").Text).Range("A1")

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Thu, 19 Feb 2009 09:32:54 -0600, Chip Pearson
wrote:

I think you'll find things much easier and more clear if you get rid
of all your Select and Activate statements. It is (almost) never
necessary to Select anything. Instead, declare variables for the
worksheets and the range. For example,


Sub AAA()

Dim VATSheet As Worksheet
Dim DestSheet As Worksheet
Dim Dest As Range

Set VATSheet = Worksheets("VAT Invoice")
Set DestSheet = Worksheets(VATSheet.Range("B4").Value)
Set Dest = DestSheet.Range("A1") '<<< Destination of copy
VATSheet.Range("A1:J37").Copy Destination:=Dest

End Sub

Here, VATSheet gets set to the "VAT Invoice" worksheet. DestSheet
gets set to the worksheet named in cell B4 of the VATSheet. Dest, the
destination of the Copy operation, gets set to A1 of DestSheet.
Finally, the code copies A1:J37 to the location of Dest.

No Activates, Selects, or Pastes are required. Moreover, the code
will work regardless of which worksheet is active, which makes the
code more robust and flexible.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




On Thu, 19 Feb 2009 05:14:01 -0800, Buzz
wrote:

I have an excel macro the when a cell is triggered it creates and names a new
worksheet. what I want to do is in that worksheet run a macro that copies all
from "VAT Invoice" into the active sheet.

What i've recorded is

Sheets("VAT Invoice").Select
Range("A1:J37").Select
Range("J37").Activate
Selection.Copy
Sheets("989989").Select
ActiveSheet.Paste

Sheet "980089" is the issue, as it will never be the same. The sheet name is
created from Cell B4 in "VAT Invoice"

How can I tell the macro to find the active worksheet name and then put the
data there.

Thanks for your help

John