Copy and paste sheets to new workbook
in [Programming]
|
Prev: Shading cells that contain formulas and conditional formatting at the same time.
Next: macro to insert row and type data
From: Stav19 on 7 Jun 2010 07:57 Hi All What i would like to do is to select 4 sheets and copy them into a new workbook. I would like the sheets to be values and to have the same page layout as the original sheets. The worksheets I'm copying are formula driven, so I have to copy and paste value those sheets first, and then move them to a new workbook, the problem is that I don't know how to undo that "copy and paste value" in my original workbook without closing the file and opening it again, which is a little annoying. The code I have so far is just from the macro recorder: Sub CPVNW() ' ' CPVNW Macro ' ' Sheets(Array("Client Wkly Mvmts - EUR", "Client Wkly Mvmts - GBP", _ "Client Wkly Mvmts - USD", "Daily Movements")).Select Sheets("Client Wkly Mvmts - EUR").Activate Cells.Select Range("B4").Activate Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets(Array("Client Wkly Mvmts - EUR", "Client Wkly Mvmts - GBP", _ "Client Wkly Mvmts - USD", "Daily Movements")).Select Sheets("Client Wkly Mvmts - EUR").Activate Application.CutCopyMode = False Sheets(Array("Client Wkly Mvmts - EUR", "Client Wkly Mvmts - GBP", _ "Client Wkly Mvmts - USD", "Daily Movements")).Copy End Sub I'm pretty sure there's a way of copying the data without having to actually do it to the file i'm working on, can anyone suggest the best way to do that pls? Thanks!
From: steve on 7 Jun 2010 09:00 In the workbook you want to copy from select the first sheet tab then holding down the ctrl key select the other 3 sheets. Right mouse click on any of the selected sheet tabs, pick 'move or copy'. In the dialogue box pick the workbook you want to copy to, and select "Create a copy" at the bottom and press ok. In the new workbook again select all 4 sheet tabs click in the square to the left of "A" and above"1" which will select everything in the four sheets and then just select copy, followed by paste values. Regards Steve "Stav19" <pete.rebello(a)gmail.com> wrote in message news:393dc15e-4e06-42c0-8c43-69289c20806c(a)c33g2000yqm.googlegroups.com... > Hi All > > What i would like to do is to select 4 sheets and copy them into a new > workbook. I would like the sheets to be values and to have the same > page layout as the original sheets. The worksheets I'm copying are > formula driven, so I have to copy and paste value those sheets first, > and then move them to a new workbook, the problem is that I don't know > how to undo that "copy and paste value" in my original workbook > without closing the file and opening it again, which is a little > annoying. > > The code I have so far is just from the macro recorder: > > Sub CPVNW() > ' > ' CPVNW Macro > ' > > ' > Sheets(Array("Client Wkly Mvmts - EUR", "Client Wkly Mvmts - GBP", > _ > "Client Wkly Mvmts - USD", "Daily Movements")).Select > Sheets("Client Wkly Mvmts - EUR").Activate > Cells.Select > Range("B4").Activate > Selection.Copy > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, > SkipBlanks _ > :=False, Transpose:=False > Sheets(Array("Client Wkly Mvmts - EUR", "Client Wkly Mvmts - GBP", > _ > "Client Wkly Mvmts - USD", "Daily Movements")).Select > Sheets("Client Wkly Mvmts - EUR").Activate > Application.CutCopyMode = False > Sheets(Array("Client Wkly Mvmts - EUR", "Client Wkly Mvmts - GBP", > _ > "Client Wkly Mvmts - USD", "Daily Movements")).Copy > End Sub > > I'm pretty sure there's a way of copying the data without having to > actually do it to the file i'm working on, can anyone suggest the best > way to do that pls? > > Thanks!
From: Stav19 on 7 Jun 2010 09:16 On Jun 7, 2:00 pm, "steve" <steven_gr...(a)hotmail.com> wrote: > In the workbook you want to copy from select the first sheet tab then > holding down the ctrl key select the other 3 sheets. Right mouse click on > any of the selected sheet tabs, pick 'move or copy'. In the dialogue box > pick the workbook you want to copy to, and select "Create a copy" at the > bottom and press ok. In the new workbook again select all 4 sheet tabs click > in the square to the left of "A" and above"1" which will select everything > in the four sheets and then just select copy, followed by paste values. > > Regards > Steve > > "Stav19" <pete.rebe...(a)gmail.com> wrote in message > > news:393dc15e-4e06-42c0-8c43-69289c20806c(a)c33g2000yqm.googlegroups.com... > > > > > Hi All > > > What i would like to do is to select 4 sheets and copy them into a new > > workbook. I would like the sheets to be values and to have the same > > page layout as the original sheets. The worksheets I'm copying are > > formula driven, so I have to copy and paste value those sheets first, > > and then move them to a new workbook, the problem is that I don't know > > how to undo that "copy and paste value" in my original workbook > > without closing the file and opening it again, which is a little > > annoying. > > > The code I have so far is just from the macro recorder: > > > Sub CPVNW() > > ' > > ' CPVNW Macro > > ' > > > ' > > Sheets(Array("Client Wkly Mvmts - EUR", "Client Wkly Mvmts - GBP", > > _ > > "Client Wkly Mvmts - USD", "Daily Movements")).Select > > Sheets("Client Wkly Mvmts - EUR").Activate > > Cells.Select > > Range("B4").Activate > > Selection.Copy > > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, > > SkipBlanks _ > > :=False, Transpose:=False > > Sheets(Array("Client Wkly Mvmts - EUR", "Client Wkly Mvmts - GBP", > > _ > > "Client Wkly Mvmts - USD", "Daily Movements")).Select > > Sheets("Client Wkly Mvmts - EUR").Activate > > Application.CutCopyMode = False > > Sheets(Array("Client Wkly Mvmts - EUR", "Client Wkly Mvmts - GBP", > > _ > > "Client Wkly Mvmts - USD", "Daily Movements")).Copy > > End Sub > > > I'm pretty sure there's a way of copying the data without having to > > actually do it to the file i'm working on, can anyone suggest the best > > way to do that pls? > > > Thanks!- Hide quoted text - > > - Show quoted text - Thanks Steve! Unfortunately I have to paste values on the original workbook first, and then create copies of the sheets. If I create copies of the sheets to a new workbook, the formulas in the new workbook would not work...
From: Don Guillett on 7 Jun 2010 09:53 Then you will have to close withOUT saving and re-open. You may want to run from a THIRD workbook so the macro can do it for you. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett(a)gmail.com "Stav19" <pete.rebello(a)gmail.com> wrote in message news:2a4dd812-b795-465c-b484-59406312ded0(a)u7g2000yqm.googlegroups.com... On Jun 7, 2:00�pm, "steve" <steven_gr...(a)hotmail.com> wrote: > In the workbook you want to copy from select the first sheet tab then > holding down the ctrl key select the other 3 sheets. Right mouse click on > any of the selected sheet tabs, pick 'move or copy'. In the dialogue box > pick the workbook you want to copy to, and select "Create a copy" at the > bottom and press ok. In the new workbook again select all 4 sheet tabs > click > in the square to the left of "A" and above"1" which will select everything > in the four sheets and then just select copy, followed by paste values. > > Regards > � Steve > > "Stav19" <pete.rebe...(a)gmail.com> wrote in message > > news:393dc15e-4e06-42c0-8c43-69289c20806c(a)c33g2000yqm.googlegroups.com... > > > > > Hi All > > > What i would like to do is to select 4 sheets and copy them into a new > > workbook. �I would like the sheets to be values and to have the same > > page layout as the original sheets. �The worksheets I'm copying are > > formula driven, so I have to copy and paste value those sheets first, > > and then move them to a new workbook, the problem is that I don't know > > how to undo that "copy and paste value" in my original workbook > > without closing the file and opening it again, which is a little > > annoying. > > > The code I have so far is just from the macro recorder: > > > Sub CPVNW() > > ' > > ' CPVNW Macro > > ' > > > ' > > � �Sheets(Array("Client Wkly Mvmts - EUR", "Client Wkly Mvmts - GBP", > > _ > > � � � �"Client Wkly Mvmts - USD", "Daily Movements")).Select > > � �Sheets("Client Wkly Mvmts - EUR").Activate > > � �Cells.Select > > � �Range("B4").Activate > > � �Selection.Copy > > � �Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, > > SkipBlanks _ > > � � � �:=False, Transpose:=False > > � �Sheets(Array("Client Wkly Mvmts - EUR", "Client Wkly Mvmts - GBP", > > _ > > � � � �"Client Wkly Mvmts - USD", "Daily Movements")).Select > > � �Sheets("Client Wkly Mvmts - EUR").Activate > > � �Application.CutCopyMode = False > > � �Sheets(Array("Client Wkly Mvmts - EUR", "Client Wkly Mvmts - GBP", > > _ > > � � � �"Client Wkly Mvmts - USD", "Daily Movements")).Copy > > End Sub > > > I'm pretty sure there's a way of copying the data without having to > > actually do it to the file i'm working on, can anyone suggest the best > > way to do that pls? > > > Thanks!- Hide quoted text - > > - Show quoted text - Thanks Steve! Unfortunately I have to paste values on the original workbook first, and then create copies of the sheets. If I create copies of the sheets to a new workbook, the formulas in the new workbook would not work...
From: Stav19 on 7 Jun 2010 10:31
On Jun 7, 2:53 pm, "Don Guillett" <dguill...(a)gmail.com> wrote: > Then you will have to close withOUT saving and re-open. You may want to run > from a THIRD workbook so the macro can do it for you. > > -- > Don Guillett > Microsoft MVP Excel > SalesAid Software > dguill...(a)gmail.com"Stav19" <pete.rebe...(a)gmail.com> wrote in message > > news:2a4dd812-b795-465c-b484-59406312ded0(a)u7g2000yqm.googlegroups.com... > On Jun 7, 2:00 pm, "steve" <steven_gr...(a)hotmail.com> wrote: > > > > > > > In the workbook you want to copy from select the first sheet tab then > > holding down the ctrl key select the other 3 sheets. Right mouse click on > > any of the selected sheet tabs, pick 'move or copy'. In the dialogue box > > pick the workbook you want to copy to, and select "Create a copy" at the > > bottom and press ok. In the new workbook again select all 4 sheet tabs > > click > > in the square to the left of "A" and above"1" which will select everything > > in the four sheets and then just select copy, followed by paste values. > > > Regards > > Steve > > > "Stav19" <pete.rebe...(a)gmail.com> wrote in message > > >news:393dc15e-4e06-42c0-8c43-69289c20806c(a)c33g2000yqm.googlegroups.com.... > > > > Hi All > > > > What i would like to do is to select 4 sheets and copy them into a new > > > workbook. I would like the sheets to be values and to have the same > > > page layout as the original sheets. The worksheets I'm copying are > > > formula driven, so I have to copy and paste value those sheets first, > > > and then move them to a new workbook, the problem is that I don't know > > > how to undo that "copy and paste value" in my original workbook > > > without closing the file and opening it again, which is a little > > > annoying. > > > > The code I have so far is just from the macro recorder: > > > > Sub CPVNW() > > > ' > > > ' CPVNW Macro > > > ' > > > > ' > > > Sheets(Array("Client Wkly Mvmts - EUR", "Client Wkly Mvmts - GBP", > > > _ > > > "Client Wkly Mvmts - USD", "Daily Movements")).Select > > > Sheets("Client Wkly Mvmts - EUR").Activate > > > Cells.Select > > > Range("B4").Activate > > > Selection.Copy > > > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, > > > SkipBlanks _ > > > :=False, Transpose:=False > > > Sheets(Array("Client Wkly Mvmts - EUR", "Client Wkly Mvmts - GBP", > > > _ > > > "Client Wkly Mvmts - USD", "Daily Movements")).Select > > > Sheets("Client Wkly Mvmts - EUR").Activate > > > Application.CutCopyMode = False > > > Sheets(Array("Client Wkly Mvmts - EUR", "Client Wkly Mvmts - GBP", > > > _ > > > "Client Wkly Mvmts - USD", "Daily Movements")).Copy > > > End Sub > > > > I'm pretty sure there's a way of copying the data without having to > > > actually do it to the file i'm working on, can anyone suggest the best > > > way to do that pls? > > > > Thanks!- Hide quoted text - > > > - Show quoted text - > > Thanks Steve! > > Unfortunately I have to paste values on the original workbook first, > and then create copies of the sheets. If I create copies of the > sheets to a new workbook, the formulas in the new workbook would not > work...- Hide quoted text - > > - Show quoted text - Hi Don Thanks for the suggestion, in all honesty, I'm not sure how that would work, closing the file is fine, just a bit of a pain that's all! |