Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste sheets to new workbook
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste sheets to new workbook
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" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste sheets to new workbook
On Jun 7, 2:00*pm, "steve" 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" wrote in message ... 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... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste sheets to new workbook
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste sheets to new workbook
On Jun 7, 2:53*pm, "Don Guillett" 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 "Stav19" wrote in message ... On Jun 7, 2:00 pm, "steve" 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" wrote in message .... 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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste sheets to new workbook
Hi,
Just a few questions to help me understand the layout. Are the 4 sheets next to each other in the original workbook. When you copy the information to the new work book do the sheet names stay the same, and are they in the same order.Where are the "formulas" in the new workbook. Do you need to copy the complete sheets or is it just a range from them. Regards Steve "Stav19" wrote in message ... On Jun 7, 2:00 pm, "steve" 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" wrote in message ... 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... |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste sheets to new workbook
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste sheets to new workbook
On Jun 7, 3:44*pm, "steve" wrote:
Hi, * Just a few questions to help me understand the layout. Are the 4 sheets next to each other in the original workbook. When you copy the information to the new work book do the sheet names stay the same, and are they in the same order.Where are the "formulas" in the new workbook. Do you need to copy the complete sheets or is it just a range from them. Regards * Steve "Stav19" wrote in message ... On Jun 7, 2:00 pm, "steve" 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" wrote in message .... 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 - The Sheets aren't next to each other in the original workbook, but that could be easily changed, they will be next to each other in the new workbook. In the new workbook, I do not have any formulas, just values as I'm using the INDIRECT function in the original workbook and it won't work in the new work book without the source data being moved as well I don't think. Thx Pete |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste sheets to new workbook
I tried this in 2007 and it works
If in the original you have the four sheets next to each other and select all of them and click in the square at the top left of the sheet, click Copy or Ctrl+v. Then in the new workbook select cell A1 in the first sheet (it should have 4 sheets) click paste values. The sheets don't have to have the same names But if they do keep them in the same order. The sheets in the original have to be next to each other. If you are happy using macro's I can post that. Workbook names and sheet names would be helpfull. Regards Steve "Stav19" wrote in message ... On Jun 7, 3:44 pm, "steve" wrote: Hi, Just a few questions to help me understand the layout. Are the 4 sheets next to each other in the original workbook. When you copy the information to the new work book do the sheet names stay the same, and are they in the same order.Where are the "formulas" in the new workbook. Do you need to copy the complete sheets or is it just a range from them. Regards Steve "Stav19" wrote in message ... On Jun 7, 2:00 pm, "steve" 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" wrote in message ... 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 - The Sheets aren't next to each other in the original workbook, but that could be easily changed, they will be next to each other in the new workbook. In the new workbook, I do not have any formulas, just values as I'm using the INDIRECT function in the original workbook and it won't work in the new work book without the source data being moved as well I don't think. Thx Pete |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste sheets to new workbook
On Jun 7, 5:16*pm, "steve" wrote:
I tried this in 2007 and it works If in the original you have the four sheets next to each other and select all of them and click in the square at the top left of the sheet, click Copy or Ctrl+v. Then in the new workbook select cell A1 in the first sheet (it should have 4 sheets) click paste values. The sheets don't have to have the same names But if they do keep them in the same order. The sheets in the original have to be next to each other. *If you are happy using macro's I can post that. Workbook names and sheet names would be helpfull. Regards *Steve "Stav19" wrote in message ... On Jun 7, 3:44 pm, "steve" wrote: Hi, Just a few questions to help me understand the layout. Are the 4 sheets next to each other in the original workbook. When you copy the information to the new work book do the sheet names stay the same, and are they in the same order.Where are the "formulas" in the new workbook. Do you need to copy the complete sheets or is it just a range from them. Regards Steve "Stav19" wrote in message ... On Jun 7, 2:00 pm, "steve" 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" wrote in message .... 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 - The Sheets aren't next to each other in the original workbook, but that could be easily changed, they will be next to each other in the new workbook. *In the new workbook, I do not have any formulas, just values as I'm using the INDIRECT function in the original workbook and it won't work in the new work book without the source data being moved as well I don't think. Thx Pete- Hide quoted text - - Show quoted text - Hi Steve I think i'd tried that before, and it does work, but the problem is that it doesn't copy over the page layouts for each page, and the headers etc. The only way i could think to do it was to copy & move the pages from the original workbook (once I had copied and pasted them as values) to a new workbook. I'd be interested in the best way to do it with a macro... The sheet names a "Client Wkly Mvmts - EUR", "Client Wkly Mvmts - GBP", "Client Wkly Mvmts - USD", "Daily Movements" Workbook is called "June Reports" The macro I'd recorded (whilst not being pretty, and not really sure how to make it pretty!) does what I want, but leaves me with the problem of having to close and reopen the original file so that I have the formulas on the pages again. Don mentioned possibly using a third file, but that's a little beyond my abilities I think! Alternatively I can stick with closing and reopening the file! Thanks Pete |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste sheets to new workbook
Hi,
Try this on a backup first just in case it creates unexpected results. I've called the second workbook "WorkBook2.xlsx" this needs to be open so change the name below to suit. Basically it just copies the four sheets to the 2nd workbook and then reselects these sheets and copies and pastes the values. See if it does what you are expecting, and if not let me know where it does things you are not expecting. By the way the 2nd workbook doesn,t need the sheet names in there it will create them. Sub CopyAndMakeValues() Workbooks("June Reports.xlsx").Activate 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 Selection.Copy 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 _ Befo=Workbooks("WorkBook2.xlsx").Sheets(1) 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 Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False End Sub Regards Steve "Stav19" wrote in message ... On Jun 7, 5:16 pm, "steve" wrote: I tried this in 2007 and it works If in the original you have the four sheets next to each other and select all of them and click in the square at the top left of the sheet, click Copy or Ctrl+v. Then in the new workbook select cell A1 in the first sheet (it should have 4 sheets) click paste values. The sheets don't have to have the same names But if they do keep them in the same order. The sheets in the original have to be next to each other. If you are happy using macro's I can post that. Workbook names and sheet names would be helpfull. Regards Steve "Stav19" wrote in message ... On Jun 7, 3:44 pm, "steve" wrote: Hi, Just a few questions to help me understand the layout. Are the 4 sheets next to each other in the original workbook. When you copy the information to the new work book do the sheet names stay the same, and are they in the same order.Where are the "formulas" in the new workbook. Do you need to copy the complete sheets or is it just a range from them. Regards Steve "Stav19" wrote in message ... On Jun 7, 2:00 pm, "steve" 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" wrote in message ... 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 - The Sheets aren't next to each other in the original workbook, but that could be easily changed, they will be next to each other in the new workbook. In the new workbook, I do not have any formulas, just values as I'm using the INDIRECT function in the original workbook and it won't work in the new work book without the source data being moved as well I don't think. Thx Pete- Hide quoted text - - Show quoted text - Hi Steve I think i'd tried that before, and it does work, but the problem is that it doesn't copy over the page layouts for each page, and the headers etc. The only way i could think to do it was to copy & move the pages from the original workbook (once I had copied and pasted them as values) to a new workbook. I'd be interested in the best way to do it with a macro... The sheet names a "Client Wkly Mvmts - EUR", "Client Wkly Mvmts - GBP", "Client Wkly Mvmts - USD", "Daily Movements" Workbook is called "June Reports" The macro I'd recorded (whilst not being pretty, and not really sure how to make it pretty!) does what I want, but leaves me with the problem of having to close and reopen the original file so that I have the formulas on the pages again. Don mentioned possibly using a third file, but that's a little beyond my abilities I think! Alternatively I can stick with closing and reopening the file! Thanks Pete |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste sheets to new workbook
On Jun 8, 10:56*am, "steve" wrote:
Hi, * *Try this on a backup first just in case it creates unexpected results. I've called the second workbook "WorkBook2.xlsx" this needs to be open so change the name below to suit. Basically it just copies the four sheets to the 2nd workbook and then reselects these sheets and copies and pastes the values. See if it does what you are expecting, and if not let me know where it does things you are not expecting. By the way the 2nd workbook doesn,t need the sheet names in there it will create them. Sub CopyAndMakeValues() * * Workbooks("June Reports.xlsx").Activate * * 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 * * Selection.Copy * * 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 _ * * * * Befo=Workbooks("WorkBook2.xlsx").Sheets(1) * * 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 * * Selection.Copy * * Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False End Sub Regards * Steve "Stav19" wrote in message ... On Jun 7, 5:16 pm, "steve" wrote: I tried this in 2007 and it works If in the original you have the four sheets next to each other and select all of them and click in the square at the top left of the sheet, click Copy or Ctrl+v. Then in the new workbook select cell A1 in the first sheet (it should have 4 sheets) click paste values. The sheets don't have to have the same names But if they do keep them in the same order. The sheets in the original have to be next to each other. If you are happy using macro's I can post that. Workbook names and sheet names would be helpfull. Regards Steve "Stav19" wrote in message ... On Jun 7, 3:44 pm, "steve" wrote: Hi, Just a few questions to help me understand the layout. Are the 4 sheets next to each other in the original workbook. When you copy the information to the new work book do the sheet names stay the same, and are they in the same order.Where are the "formulas" in the new workbook. Do you need to copy the complete sheets or is it just a range from them. Regards Steve "Stav19" wrote in message .... On Jun 7, 2:00 pm, "steve" 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" wrote in message ... 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. TheworksheetsI'mcopyingare 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 ofcopyingthe 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 - The Sheets aren't next to each other in the original workbook, but that could be easily changed, they will be next to each other in the new workbook. In the new workbook, I do not have any formulas, just values as I'm using the INDIRECT function in the original workbook and it won't work in the new work book without the source data being moved as well I don't think. Thx Pete- Hide quoted text - - Show quoted text - Hi Steve I think i'd tried that before, and it does work, but the problem is that it doesn't copy over the page layouts for each page, and the headers etc. *The only way i could think to do it was to copy & move the pages from the original workbook (once I had copied and pasted them as values) to a new workbook. I'd be interested in the best way to do it with a macro... The sheet names a "Client Wkly Mvmts - EUR", "Client Wkly Mvmts - GBP", "Client Wkly Mvmts - USD", "Daily Movements" Workbook is called "June Reports" The macro I'd recorded (whilst not being pretty, and not really sure how to make it pretty!) does what I want, but leaves me with the problem of having to close and reopen the original file so that I have the formulas on the pages again. Don mentioned possibly using a third file, but that's a little beyond my abilities I think! Alternatively I can stick with closing and reopening the file! Thanks Pete- Hide quoted text - - Show quoted text - Thanks Steve Sorry for the delay in getting back to you, it got stuck on this bit: Sheets(Array("Client Wkly Mvmts - EUR", "Client Wkly Mvmts - GBP", "Client Wkly Mvmts - USD", "Daily Movements")).Copy _ Befo=Workbooks("WorkBook2.xlsx").Sheets(1) and gave me a Runtime error "Subscript out of Range" is it anything to do with the ".Sheets(1)? Regards Peter |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste sheets to new workbook
Hi Peter,
That just tells it to copy the sheets infront of the first sheet in the second workbook. Do you have a second workbook open and is it called Workbook2.xlsx Regards Steve "Stav19" wrote in message ... On Jun 8, 10:56 am, "steve" wrote: Hi, Try this on a backup first just in case it creates unexpected results. I've called the second workbook "WorkBook2.xlsx" this needs to be open so change the name below to suit. Basically it just copies the four sheets to the 2nd workbook and then reselects these sheets and copies and pastes the values. See if it does what you are expecting, and if not let me know where it does things you are not expecting. By the way the 2nd workbook doesn,t need the sheet names in there it will create them. Sub CopyAndMakeValues() Workbooks("June Reports.xlsx").Activate 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 Selection.Copy 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 _ Befo=Workbooks("WorkBook2.xlsx").Sheets(1) 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 Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False End Sub Regards Steve "Stav19" wrote in message ... On Jun 7, 5:16 pm, "steve" wrote: I tried this in 2007 and it works If in the original you have the four sheets next to each other and select all of them and click in the square at the top left of the sheet, click Copy or Ctrl+v. Then in the new workbook select cell A1 in the first sheet (it should have 4 sheets) click paste values. The sheets don't have to have the same names But if they do keep them in the same order. The sheets in the original have to be next to each other. If you are happy using macro's I can post that. Workbook names and sheet names would be helpfull. Regards Steve "Stav19" wrote in message ... On Jun 7, 3:44 pm, "steve" wrote: Hi, Just a few questions to help me understand the layout. Are the 4 sheets next to each other in the original workbook. When you copy the information to the new work book do the sheet names stay the same, and are they in the same order.Where are the "formulas" in the new workbook. Do you need to copy the complete sheets or is it just a range from them. Regards Steve "Stav19" wrote in message ... On Jun 7, 2:00 pm, "steve" 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" wrote in message ... 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. TheworksheetsI'mcopyingare 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 ofcopyingthe 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 - The Sheets aren't next to each other in the original workbook, but that could be easily changed, they will be next to each other in the new workbook. In the new workbook, I do not have any formulas, just values as I'm using the INDIRECT function in the original workbook and it won't work in the new work book without the source data being moved as well I don't think. Thx Pete- Hide quoted text - - Show quoted text - Hi Steve I think i'd tried that before, and it does work, but the problem is that it doesn't copy over the page layouts for each page, and the headers etc. The only way i could think to do it was to copy & move the pages from the original workbook (once I had copied and pasted them as values) to a new workbook. I'd be interested in the best way to do it with a macro... The sheet names a "Client Wkly Mvmts - EUR", "Client Wkly Mvmts - GBP", "Client Wkly Mvmts - USD", "Daily Movements" Workbook is called "June Reports" The macro I'd recorded (whilst not being pretty, and not really sure how to make it pretty!) does what I want, but leaves me with the problem of having to close and reopen the original file so that I have the formulas on the pages again. Don mentioned possibly using a third file, but that's a little beyond my abilities I think! Alternatively I can stick with closing and reopening the file! Thanks Pete- Hide quoted text - - Show quoted text - Thanks Steve Sorry for the delay in getting back to you, it got stuck on this bit: Sheets(Array("Client Wkly Mvmts - EUR", "Client Wkly Mvmts - GBP", "Client Wkly Mvmts - USD", "Daily Movements")).Copy _ Befo=Workbooks("WorkBook2.xlsx").Sheets(1) and gave me a Runtime error "Subscript out of Range" is it anything to do with the ".Sheets(1)? Regards Peter |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste sheets to new workbook
On Jun 9, 1:47*pm, "steve" wrote:
Hi Peter, * That just tells it to copy the sheets infront of the first sheet in the second workbook. Do you have a second workbook open and is it called Workbook2.xlsx Regards * Steve "Stav19" wrote in message ... On Jun 8, 10:56 am, "steve" wrote: Hi, Try this on a backup first just in case it creates unexpected results. I've called the second workbook "WorkBook2.xlsx" this needs to be open so change the name below to suit. Basically it just copies the four sheets to the 2nd workbook and then reselects these sheets and copies and pastes the values. See if it does what you are expecting, and if not let me know where it does things you are not expecting. By the way the 2nd workbook doesn,t need the sheet names in there it will create them. Sub CopyAndMakeValues() Workbooks("June Reports.xlsx").Activate 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 Selection.Copy 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 _ Befo=Workbooks("WorkBook2.xlsx").Sheets(1) 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 Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False End Sub Regards Steve "Stav19" wrote in message .... On Jun 7, 5:16 pm, "steve" wrote: I tried this in 2007 and it works If in the original you have the four sheets next to each other and select all of them and click in the square at the top left of the sheet, click Copy or Ctrl+v. Then in the new workbook select cell A1 in the first sheet (it should have 4 sheets) click paste values. The sheets don't have to have the same names But if they do keep them in the same order. The sheets in the original have to be next to each other. If you are happy using macro's I can post that. Workbook names and sheet names would be helpfull. Regards Steve "Stav19" wrote in message .... On Jun 7, 3:44 pm, "steve" wrote: Hi, Just a few questions to help me understand the layout. Are the 4 sheets next to each other in the original workbook. When you copy the information to the new work book do the sheet names stay the same, and are they in the same order.Where are the "formulas" in the new workbook. Do you need to copy the complete sheets or is it just a range from them. Regards Steve "Stav19" wrote in message ... On Jun 7, 2:00 pm, "steve" 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" wrote in message ... 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. TheworksheetsI'mcopyingare 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 ofcopyingthe 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 - The Sheets aren't next to each other in the original workbook, but that could be easily changed, they will be next to each other in the new workbook. In the new workbook, I do not have any formulas, just values as I'm using the INDIRECT function in the original workbook and it won't work in the new work book without the source data being moved as well I don't think. Thx Pete- Hide quoted text - - Show quoted text - Hi Steve I think i'd tried that before, and it does work, but the problem is that it doesn't copy over the page layouts for each page, and the headers etc. The only way i could think to do it was to copy & move the pages from the original workbook (once I had copied and pasted them as values) to a new workbook. I'd be interested in the best way to do it with a macro... The sheet names a "Client Wkly Mvmts - EUR", "Client Wkly Mvmts - GBP", "Client Wkly Mvmts - USD", "Daily Movements" Workbook is called "June Reports" The macro I'd recorded (whilst not being pretty, and not really sure how to make it pretty!) does what I want, but leaves me with the problem of having to close and reopen the original file so that I have the formulas on the pages again. Don mentioned possibly using a third file, but that's a little beyond my abilities I think! Alternatively I can stick with closing and reopening the file! Thanks Pete- Hide quoted text - - Show quoted text - Thanks Steve Sorry for the delay in getting back to you, it got stuck on this bit: * * Sheets(Array("Client Wkly Mvmts - EUR", "Client Wkly Mvmts - GBP", "Client Wkly Mvmts - USD", "Daily Movements")).Copy _ * * * * Befo=Workbooks("WorkBook2.xlsx").Sheets(1) and gave me a Runtime error "Subscript out of Range" is it anything to do with the ".Sheets(1)? Regards Peter aha that could well be it, to be honest can't remember if I did have another workbook open when I tried, I probably did, but would it matter what the name was if I did? Regards Peter |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste sheets to new workbook
"Stav19" wrote in message ... On Jun 9, 1:47 pm, "steve" wrote: Hi Peter, That just tells it to copy the sheets infront of the first sheet in the second workbook. Do you have a second workbook open and is it called Workbook2.xlsx Regards Steve "Stav19" wrote in message ... On Jun 8, 10:56 am, "steve" wrote: Hi, Try this on a backup first just in case it creates unexpected results. I've called the second workbook "WorkBook2.xlsx" this needs to be open so change the name below to suit. Basically it just copies the four sheets to the 2nd workbook and then reselects these sheets and copies and pastes the values. See if it does what you are expecting, and if not let me know where it does things you are not expecting. By the way the 2nd workbook doesn,t need the sheet names in there it will create them. Sub CopyAndMakeValues() Workbooks("June Reports.xlsx").Activate 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 Selection.Copy 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 _ Befo=Workbooks("WorkBook2.xlsx").Sheets(1) 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 Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False End Sub Regards Steve "Stav19" wrote in message ... On Jun 7, 5:16 pm, "steve" wrote: I tried this in 2007 and it works If in the original you have the four sheets next to each other and select all of them and click in the square at the top left of the sheet, click Copy or Ctrl+v. Then in the new workbook select cell A1 in the first sheet (it should have 4 sheets) click paste values. The sheets don't have to have the same names But if they do keep them in the same order. The sheets in the original have to be next to each other. If you are happy using macro's I can post that. Workbook names and sheet names would be helpfull. Regards Steve "Stav19" wrote in message ... On Jun 7, 3:44 pm, "steve" wrote: Hi, Just a few questions to help me understand the layout. Are the 4 sheets next to each other in the original workbook. When you copy the information to the new work book do the sheet names stay the same, and are they in the same order.Where are the "formulas" in the new workbook. Do you need to copy the complete sheets or is it just a range from them. Regards Steve "Stav19" wrote in message ... On Jun 7, 2:00 pm, "steve" 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" wrote in message ... 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. TheworksheetsI'mcopyingare 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 ofcopyingthe 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 - The Sheets aren't next to each other in the original workbook, but that could be easily changed, they will be next to each other in the new workbook. In the new workbook, I do not have any formulas, just values as I'm using the INDIRECT function in the original workbook and it won't work in the new work book without the source data being moved as well I don't think. Thx Pete- Hide quoted text - - Show quoted text - Hi Steve I think i'd tried that before, and it does work, but the problem is that it doesn't copy over the page layouts for each page, and the headers etc. The only way i could think to do it was to copy & move the pages from the original workbook (once I had copied and pasted them as values) to a new workbook. I'd be interested in the best way to do it with a macro... The sheet names a "Client Wkly Mvmts - EUR", "Client Wkly Mvmts - GBP", "Client Wkly Mvmts - USD", "Daily Movements" Workbook is called "June Reports" The macro I'd recorded (whilst not being pretty, and not really sure how to make it pretty!) does what I want, but leaves me with the problem of having to close and reopen the original file so that I have the formulas on the pages again. Don mentioned possibly using a third file, but that's a little beyond my abilities I think! Alternatively I can stick with closing and reopening the file! Thanks Pete- Hide quoted text - - Show quoted text - Thanks Steve Sorry for the delay in getting back to you, it got stuck on this bit: Sheets(Array("Client Wkly Mvmts - EUR", "Client Wkly Mvmts - GBP", "Client Wkly Mvmts - USD", "Daily Movements")).Copy _ Befo=Workbooks("WorkBook2.xlsx").Sheets(1) and gave me a Runtime error "Subscript out of Range" is it anything to do with the ".Sheets(1)? Regards Peter aha that could well be it, to be honest can't remember if I did have another workbook open when I tried, I probably did, but would it matter what the name was if I did? Regards Peter Hi, Peter Yes it would It's hard coded as WorkBook2.xlsx but you can change that to whatever you want. Hope you get going Regards Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy, Paste Special Values all sheets in workbook | Excel Programming | |||
Copy and paste sheets between workbook | Excel Programming | |||
copy&paste of several sheets in a folder to a workbook with somesh | Excel Programming | |||
copy all and paste values for all sheets in a workbook | Excel Worksheet Functions | |||
copy/paste values for all sheets in workbook | Excel Programming |