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!

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" <(E-Mail Removed)> wrote in message
news:393dc15e-4e06-42c0-8c43-(E-Mail Removed)...
> 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!

On Jun 7, 2:00*pm, "steve" <steven_gr...@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...@gmail.com> wrote in message
>
> news:393dc15e-4e06-42c0-8c43-(E-Mail Removed)...
>
>
>
> > 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...

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
(E-Mail Removed)
"Stav19" <(E-Mail Removed)> wrote in message
news:2a4dd812-b795-465c-b484-(E-Mail Removed)...
On Jun 7, 2:00�pm, "steve" <steven_gr...@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...@gmail.com> wrote in message
>
> news:393dc15e-4e06-42c0-8c43-(E-Mail Removed)...
>
>
>
> > 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...

On Jun 7, 2:53*pm, "Don Guillett" <dguill...@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...@gmail.com"Stav19" <pete.rebe...@gmail.com> wrote in message
>
> news:2a4dd812-b795-465c-b484-(E-Mail Removed)...
> On Jun 7, 2:00 pm, "steve" <steven_gr...@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...@gmail.com> wrote in message
>
> >news:393dc15e-4e06-42c0-8c43-(E-Mail Removed)....
>
> > > 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!

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" <(E-Mail Removed)> wrote in message
news:2a4dd812-b795-465c-b484-(E-Mail Removed)...
On Jun 7, 2:00 pm, "steve" <steven_gr...@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...@gmail.com> wrote in message
>
> news:393dc15e-4e06-42c0-8c43-(E-Mail Removed)...
>
>
>
> > 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 third workbook
open file to copy/save>do it >close it>>open again if desired...

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Stav19" <(E-Mail Removed)> wrote in message
news:dcbaed1c-5940-4cde-a950-(E-Mail Removed)...
On Jun 7, 2:53�pm, "Don Guillett" <dguill...@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...@gmail.com"Stav19" <pete.rebe...@gmail.com> wrote in message
>
> news:2a4dd812-b795-465c-b484-(E-Mail Removed)...
> On Jun 7, 2:00 pm, "steve" <steven_gr...@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...@gmail.com> wrote in message
>
> >news:393dc15e-4e06-42c0-8c43-(E-Mail Removed)...
>
> > > 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!

On Jun 7, 3:44*pm, "steve" <steven_gr...@hotmail.com> 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" <pete.rebe...@gmail.com> wrote in message
>
> news:2a4dd812-b795-465c-b484-(E-Mail Removed)...
> On Jun 7, 2:00 pm, "steve" <steven_gr...@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...@gmail.com> wrote in message
>
> >news:393dc15e-4e06-42c0-8c43-(E-Mail Removed)....
>
> > > 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

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" <(E-Mail Removed)> wrote in message
news:64a81465-5b2a-434e-b204-(E-Mail Removed)...
On Jun 7, 3:44 pm, "steve" <steven_gr...@hotmail.com> 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" <pete.rebe...@gmail.com> wrote in message
>
> news:2a4dd812-b795-465c-b484-(E-Mail Removed)...
> On Jun 7, 2:00 pm, "steve" <steven_gr...@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...@gmail.com> wrote in message
>
> >news:393dc15e-4e06-42c0-8c43-(E-Mail Removed)...
>
> > > 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

On Jun 7, 5:16*pm, "steve" <steven_gr...@hotmail.com> 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" <pete.rebe...@gmail.com> wrote in message
>
> news:64a81465-5b2a-434e-b204-(E-Mail Removed)...
> On Jun 7, 3:44 pm, "steve" <steven_gr...@hotmail.com> 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" <pete.rebe...@gmail.com> wrote in message
>
> >news:2a4dd812-b795-465c-b484-(E-Mail Removed)...
> > On Jun 7, 2:00 pm, "steve" <steven_gr...@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...@gmail.com> wrote in message
>
> > >news:393dc15e-4e06-42c0-8c43-(E-Mail Removed)....
>
> > > > 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 are:
"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