MrExcel.com - Your One Stop for Excel Tips & Solutions

Copy a Range to another worksheet

Posted by Bruce on January 29, 2002 5:57 AM


Does anyone know how, in VB, to copy a named range of cells from one worksheet to another, while retinaing all cell formatting, including Row Heights and Column Widths?

Posted by JohnG on January 29, 2002 7:54 AM

This is what I came up with so far but for some reason it ignores the row height part.

Sub CopyMacro()
' Recorded 1/29/2002 by J. Gittins
'
Workbooks("Book2").Worksheets(1).Range("A1:A3").Copy
Workbooks("Book3").Worksheets(1).Range("A1:A3").PasteSpecial (xlPasteAll)
Workbooks("Book3").Worksheets(1).Range("A1:A3").ColumnWidth = _
Workbooks("Book2").Worksheets(1).Range("A1:A3").ColumnWidth
Workbooks("Book3").Worksheets(1).Range("A1:A3").RowHeight = _
Workbooks("Book2").Worksheets(1).Range("A1:A3").RowHeight
End Sub

Posted by Bruce on January 29, 2002 8:39 AM

Thanks! I'll try that This is what I came up with so far but for some reason it ignores the row height part. Sub CopyMacro()

Posted by Bruce on January 29, 2002 8:44 AM

Seems to be ignoring both row height and column width AND is messing up my formatting on the source copy This is what I came up with so far but for some reason it ignores the row height part. Sub CopyMacro()

Posted by Priam on January 29, 2002 3:25 PM

Sub CopyMacro()


For the rows :-

For x = 1 To 3
Workbooks("Book3").Worksheets(1).Cells(x, 1).RowHeight = _
Workbooks("Book2").Worksheets(1).Cells(x, 1).RowHeight
Next


Posted by Bruce on January 30, 2002 5:21 AM

How would I apply that same loop concept to the columns? And since I am doing this by ranges, how can I find the first and last cell in the ranges? For the rows :- For x = 1 To 3


Posted by Priam on January 30, 2002 6:08 AM

Dim rng2 As Range, rng3 As Range, r%, c%, rw%, col%
Workbooks("Book2").Worksheets(1).Activate
Set rng2 = Selection
Set rng3 = Workbooks("Book3").Worksheets(1).Range(rng2.Address)
r = rng2.Rows.Count
c = rng2.Columns.Count
For rw = 1 To r
rng3(rw, 1).RowHeight = rng2(rw, 1).RowHeight
Next
For col = 1 To c
rng3(1, col).ColumnWidth = rng2(1, col).ColumnWidth
Next


Posted by Bruce on January 30, 2002 8:28 AM

That works perfectly! Thank you. I just had to make a small change so that each range copy starts at the next column and row from the last copy. Thanks everyone for all your help! Dim rng2 As Range, rng3 As Range, r%, c%, rw%, col%

: How would I apply that same loop concept to the columns? And since I am doing this by ranges, how can I find the first and last cell in the ranges?