I am trying to copy a range across to some worksheets so that these are basically a
replicate of the source worksheet initially. This does effectively what I am after

Sheets("Sheet1").Cells.Copy Destination:=Sheets("Sheet3").Cells

I just wonder if this is not a hammer to crack a nut. The exact range tranfer required is

Sheets("Sheet1").Range("A1:P40").Copy Destination:=Sheets("Sheet3").Range("A1:P40")

This however does not copy across column widths etc although it takes all the text and
formulas. The first transfer does this however. Is there a tidier way to do this
remembering that I will be going through a loop to copy the source to several worksheets.
I value any help.

Graham

You can copy the whole worksheet or use copy and pastespecial

BTW you need not copy range to range. Just copy range to cell
Sheets("Sheet1").Cells.Copy Destination:=Sheets("Sheet3").range("a1")'cells
Sheets("Sheet1").Range("A1:P40").Copy
Destination:=Sheets("Sheet3").Range("A1")


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Graham H" wrote in message
...
I am trying to copy a range across to some worksheets so that these are
basically a replicate of the source worksheet initially. This does
effectively what I am after

Sheets("Sheet1").Cells.Copy Destination:=Sheets("Sheet3").Cells

I just wonder if this is not a hammer to crack a nut. The exact range
tranfer required is

Sheets("Sheet1").Range("A1:P40").Copy
Destination:=Sheets("Sheet3").Range("A1:P40")

This however does not copy across column widths etc although it takes all
the text and formulas. The first transfer does this however. Is there a
tidier way to do this remembering that I will be going through a loop to
copy the source to several worksheets.
I value any help.

Graham

One way

Sub marine()
Sheets("Sheet1").Range("A1:P40").Copy
With Worksheets("Sheet3").Range("A1")
.PasteSpecial Paste:=xlPasteAll
.PasteSpecial Paste:=xlPasteColumnWidths
.PasteSpecial Paste:=xlPasteFormats
End With
End Sub

Mike

"Graham H" wrote:

I am trying to copy a range across to some worksheets so that these are basically a
replicate of the source worksheet initially. This does effectively what I am after

Sheets("Sheet1").Cells.Copy Destination:=Sheets("Sheet3").Cells

I just wonder if this is not a hammer to crack a nut. The exact range tranfer required is

Sheets("Sheet1").Range("A1:P40").Copy Destination:=Sheets("Sheet3").Range("A1:P40")

This however does not copy across column widths etc although it takes all the text and
formulas. The first transfer does this however. Is there a tidier way to do this
remembering that I will be going through a loop to copy the source to several worksheets.
I value any help.

Graham

Thanks to you both, Mike and Don. I just wanted the confidence to make sure I was going
down the right road and I am very grateful for your help and guidance.

Graham

Mike H wrote:
One way

Sub marine()
Sheets("Sheet1").Range("A1:P40").Copy
With Worksheets("Sheet3").Range("A1")
.PasteSpecial Paste:=xlPasteAll
.PasteSpecial Paste:=xlPasteColumnWidths
.PasteSpecial Paste:=xlPasteFormats
End With
End Sub

Mike

"Graham H" wrote:

I am trying to copy a range across to some worksheets so that these are basically a
replicate of the source worksheet initially. This does effectively what I am after

Sheets("Sheet1").Cells.Copy Destination:=Sheets("Sheet3").Cells

I just wonder if this is not a hammer to crack a nut. The exact range tranfer required is

Sheets("Sheet1").Range("A1:P40").Copy Destination:=Sheets("Sheet3").Range("A1:P40")

This however does not copy across column widths etc although it takes all the text and
formulas. The first transfer does this however. Is there a tidier way to do this
remembering that I will be going through a loop to copy the source to several worksheets.
I value any help.

Graham