Worksheet range copy

[Graham H]

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("A140").Copy Destination:=Sheets("Sheet3").Range("A140")

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

 

[Don Guillett]

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("A140").Copy
Destination:=Sheets("Sheet3").Range("A1")

 

[Mike H]

One way

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

Mike

 

[Graham H]

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