| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Active Page Name
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro Active Page Name
Try this:
Sheets(Sheets("VAT Invoice").Range("B4")).Select Regards, Stefi €žBuzz€ť ezt Ă*rta: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro Active Page Name
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" 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro Active Page Name
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro Active Page Name
Try this
Sheets("VAT Invoice").Select Range("A1:J37").Select Selection.Copy nextsht = Sheets("VAT Invoice").Range("B4").value worksheets(nextsht).avctivate ActiveSheet.Paste "Buzz" wrote: 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro Active Page Name
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro Active Page Name
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro Active Page Name
Excellent Thanks Chip!
John |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| run macro although blinking cursor is active in an active cell | Excel Programming | |||
| sending active sheet by mail without macro (from Ron's page) | Excel Programming | |||
| Button to print non-active page - How? | Excel Programming | |||
| which page is active | Excel Programming | |||
| Make excel the active page | Excel Programming | |||
Linear Mode
