Worksheet range copy
 

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

Worksheet range copy
 

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

Worksheet range copy
 

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

Worksheet range copy
 

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