Copy

[TOM]

Hello,

I have a one litlle problem. I wnat copy some range for ex. A1:I22 to
another sheet. I need copy evrything (data,format...), but excel copy all
except width row.
Is there some way how can I copy evrythnig? In VB of course.

Thanks for your advice
TOM

 

[Jerry W. Lewis]

Rows don't have width, columns do. If you copy/paste entire columns,
the width will also be transrerred. Otherwise you will have to manually
adjust the destination width.

Jerry

 

[Norman Jones]

Hi Tom,

Sub CopyAll()
Dim sourceRng As Range, destRng As Range
Dim i As Long
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = ActiveWorkbook.Worksheets("Sheet1") '<<<< CHANGE
Set ws2 = ActiveWorkbook.Worksheets("Sheet2") '<<<< CHANGE

Set sourceRng = ws1.Range("A1:I22") '<<<< CHANGE
Set destRng = ws2.Range("D5") '<<<< CHANGE

sourceRng.Copy
With destRng
..PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False, _
Transpose:=False
..PasteSpecial Paste:=8 ' Column widths

For i = 1 To sourceRng.Rows.Count
..Rows(i).RowHeight = sourceRng.Rows(i).RowHeight
Next i
End With

End Sub