Macro Active Page Name

[Buzz]

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

 

[Stefi]

Try this:
Sheets(Sheets("VAT Invoice").Range("B4")).Select

Regards,
Stefi

„Buzz†ezt írta:

 

[JLR-Mart]

capture the value of the cell and assign it to a variable. Then use the
variable name to describe the workshet you want to activate:

nextsht=activecell.value
worksheets(nextsht).select

 

[Buzz]

Thanks for the replies.

I've tried them both but they both debug on the Select line for the new
worksheet. This is something dont get involved in so be gentle. what i've
tried is

Sheets("VAT Invoice").Select
Range("A1:J37").Select
Range("J37").Activate
Selection.Copy
ActiveCellValue = Sheets("VAT Invoice").Range("B4").Select
nextsht = ActiveCell.Value
Sheets(ActiveCell.Value).Select
'Sheets(Sheets("VATInvoice").Range("B4")).Select
'Sheets("nextsht").Select
'ActiveSheet.Paste

The nextsht and activecell values are the correct value when i highlight the
VB code.

Any ideas?

John

 

[JLR-Mart]

Try this

Sheets("VAT Invoice").Select
Range("A1:J37").Select
Selection.Copy
nextsht = Sheets("VAT Invoice").Range("B4").value
worksheets(nextsht).avctivate
ActiveSheet.Paste

 

[Chip Pearson]

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)

 

[Chip Pearson]

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)

 

[Buzz]

Excellent Thanks Chip!

John