Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
MAcro to copy and insert a sheet
I want to asigna macro to a button that will copy a hiden sheet, and paste it
in a certain spot among among other sheets, Example; I have the following sheets: (these are recipes) Chicken soup Beef Soup Potato soup Clam soup What I need is to insert the new sheet after potato soup and clam soup, keeing in mind that the names of the recipes will continually change. Is this clear? Any help or ideas? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
MAcro to copy and insert a sheet
It's not clear to me how you could insert one sheet after both potato
soup and clam soup... What if the sheets were rearranged so that clam soup was first? In article , JackR wrote: What I need is to insert the new sheet after potato soup and clam soup, keeing in mind that the names of the recipes will continually change. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
MAcro to copy and insert a sheet
It would insert between Potato and clam soup
"JE McGimpsey" wrote: It's not clear to me how you could insert one sheet after both potato soup and clam soup... What if the sheets were rearranged so that clam soup was first? In article , JackR wrote: What I need is to insert the new sheet after potato soup and clam soup, keeing in mind that the names of the recipes will continually change. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
MAcro to copy and insert a sheet
In general:
Sub abc() Dim sh As Worksheet, sh1 As Worksheet Set sh = Worksheets("Template") sh.Visible = xlSheetVisible sh.Copy After:=Worksheets("Potato soup") Set sh1 = ActiveSheet sh.Visible = xlSheetHidden sh1.Name = "ABC" End Sub so if you mean before the last sheet Sub abc() Dim sh As Worksheet, sh1 As Worksheet Set sh = Worksheets("Template") sh.Visible = xlSheetVisible sh.Copy Befo=Worksheets(Worksheets.count) Set sh1 = ActiveSheet sh.Visible = xlSheetHidden sh1.Name = "ABC" End Sub -- Regards, Tom Ogilvy "JackR" wrote in message ... I want to asigna macro to a button that will copy a hiden sheet, and paste it in a certain spot among among other sheets, Example; I have the following sheets: (these are recipes) Chicken soup Beef Soup Potato soup Clam soup What I need is to insert the new sheet after potato soup and clam soup, keeing in mind that the names of the recipes will continually change. Is this clear? Any help or ideas? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
MAcro to copy and insert a sheet
Your second option almost works, I get an error on this line" sh1.Name = "ABC"
also what if I wanted it at the end of all the sheets rather than befroe the last sheet? Maybe I did not understand your reference to "ABC"?? "Tom Ogilvy" wrote: In general: Sub abc() Dim sh As Worksheet, sh1 As Worksheet Set sh = Worksheets("Template") sh.Visible = xlSheetVisible sh.Copy After:=Worksheets("Potato soup") Set sh1 = ActiveSheet sh.Visible = xlSheetHidden sh1.Name = "ABC" End Sub so if you mean before the last sheet Sub abc() Dim sh As Worksheet, sh1 As Worksheet Set sh = Worksheets("Template") sh.Visible = xlSheetVisible sh.Copy Befo=Worksheets(Worksheets.count) Set sh1 = ActiveSheet sh.Visible = xlSheetHidden sh1.Name = "ABC" End Sub -- Regards, Tom Ogilvy "JackR" wrote in message ... I want to asigna macro to a button that will copy a hiden sheet, and paste it in a certain spot among among other sheets, Example; I have the following sheets: (these are recipes) Chicken soup Beef Soup Potato soup Clam soup What I need is to insert the new sheet after potato soup and clam soup, keeing in mind that the names of the recipes will continually change. Is this clear? Any help or ideas? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
MAcro to copy and insert a sheet
And if the sheets were rearranged so that clam soup was the first sheet
and potato soup the last? In article , JackR wrote: It would insert between Potato and clam soup |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
MAcro to copy and insert a sheet
Sub abc()
Dim sh As Worksheet, sh1 As Worksheet Set sh = Worksheets("Template") sh.Visible = xlSheetVisible sh.Copy After:=Worksheets(Worksheets.count) Set sh1 = ActiveSheet sh.Visible = xlSheetHidden End Sub -- Regards, Tom Ogilvy "JackR" wrote in message ... Your second option almost works, I get an error on this line" sh1.Name = "ABC" also what if I wanted it at the end of all the sheets rather than befroe the last sheet? Maybe I did not understand your reference to "ABC"?? "Tom Ogilvy" wrote: In general: Sub abc() Dim sh As Worksheet, sh1 As Worksheet Set sh = Worksheets("Template") sh.Visible = xlSheetVisible sh.Copy After:=Worksheets("Potato soup") Set sh1 = ActiveSheet sh.Visible = xlSheetHidden sh1.Name = "ABC" End Sub so if you mean before the last sheet Sub abc() Dim sh As Worksheet, sh1 As Worksheet Set sh = Worksheets("Template") sh.Visible = xlSheetVisible sh.Copy Befo=Worksheets(Worksheets.count) Set sh1 = ActiveSheet sh.Visible = xlSheetHidden sh1.Name = "ABC" End Sub -- Regards, Tom Ogilvy "JackR" wrote in message ... I want to asigna macro to a button that will copy a hiden sheet, and paste it in a certain spot among among other sheets, Example; I have the following sheets: (these are recipes) Chicken soup Beef Soup Potato soup Clam soup What I need is to insert the new sheet after potato soup and clam soup, keeing in mind that the names of the recipes will continually change. Is this clear? Any help or ideas? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
MAcro to copy and insert a sheet
Thank you that works great
"Tom Ogilvy" wrote: Sub abc() Dim sh As Worksheet, sh1 As Worksheet Set sh = Worksheets("Template") sh.Visible = xlSheetVisible sh.Copy After:=Worksheets(Worksheets.count) Set sh1 = ActiveSheet sh.Visible = xlSheetHidden End Sub -- Regards, Tom Ogilvy "JackR" wrote in message ... Your second option almost works, I get an error on this line" sh1.Name = "ABC" also what if I wanted it at the end of all the sheets rather than befroe the last sheet? Maybe I did not understand your reference to "ABC"?? "Tom Ogilvy" wrote: In general: Sub abc() Dim sh As Worksheet, sh1 As Worksheet Set sh = Worksheets("Template") sh.Visible = xlSheetVisible sh.Copy After:=Worksheets("Potato soup") Set sh1 = ActiveSheet sh.Visible = xlSheetHidden sh1.Name = "ABC" End Sub so if you mean before the last sheet Sub abc() Dim sh As Worksheet, sh1 As Worksheet Set sh = Worksheets("Template") sh.Visible = xlSheetVisible sh.Copy Befo=Worksheets(Worksheets.count) Set sh1 = ActiveSheet sh.Visible = xlSheetHidden sh1.Name = "ABC" End Sub -- Regards, Tom Ogilvy "JackR" wrote in message ... I want to asigna macro to a button that will copy a hiden sheet, and paste it in a certain spot among among other sheets, Example; I have the following sheets: (these are recipes) Chicken soup Beef Soup Potato soup Clam soup What I need is to insert the new sheet after potato soup and clam soup, keeing in mind that the names of the recipes will continually change. Is this clear? Any help or ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selection copy and insert to other sheet (Macro) | Excel Discussion (Misc queries) | |||
Selection copy and insert to other sheet | Excel Discussion (Misc queries) | |||
Macro: Insert, copy and past data from sheet | Excel Discussion (Misc queries) | |||
create a copy of a sheet and insert it into the same workbook. | Excel Programming | |||
create a copy of a sheet and insert it into the same workbook. | Excel Programming |