Copy range cells to another sheet and keep format

[bgarcia94550]

I have the following code to copy a row that is not hidden , then pasted to another sheet in the same workbook. The problem I have is that the formatting is all gone in the destination sheet. Here's my code:


Sub CopyLine2()
Dim Boxsize As Integer
Dim Row As Integer
Dim Rownum As Integer
Rownum = 0
Worksheets("Price Card").Activate
Boxsize = Worksheets("Price Card").UsedRange.Rows.Count
For Row = 1 To Boxsize
If Rows(Row).Hidden = False Then
Rownum = Rownum + 1
Rows(Row).Select
Selection.Copy

'Destination sheet
Worksheets("Price2").Select
Rows(Rownum).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Worksheets("Price Card").Activate
End If
Next Row
End Sub

 

[PHV]

Have you tried this ?
Worksheets("Price2").Rows(Rownum).PasteSpecial

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[bgarcia94550]

The column widths and row heights do not come over. Any other suggestions?

 

[PHV]

Try something like this:
Sub CopyLine2()
Worksheets("Price Card").Cells.SpecialCells(xlCellTypeVisible).Copy _
Destination:=Worksheets("Price2").Cells
End Sub

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[bgarcia94550]

The same results......

 

[macropod]

Hi bgarcia94550,

Unless you copy & paste a whole row or column, neither the row height nor the column width will be copied. Row heights and column widths are properties of 'entire' rows & columns, respectively, not of individual cells or cell groups - even if you select all the cells in a row or column.

So, instead of:

Rows(Row).Select
Selection.Copy

use:

Rows(Row).EntireRow.Copy

Note too that you don't need to select a row to copy it, which makes for more efficient (faster) code.

Cheers

 

[KenWright]

And if for any reason you don't want to copy the entire row (eg you may have other data that isn't to come across), then you will need to query the row height or column width on the source sheet, and then programmatically set it to the same on the destination sheet (or copy the whole row and then delete the bits you don't want).

Regards
Ken.....................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[bgarcia94550]

I tried the new code, but still the same result. And yes I want to copy the entire row. Here's the new code in place:


For Row = 1 To Boxsize
If Rows(Row).Hidden = False Then
Rownum = Rownum + 1
'Rows(Row).Select
'Selection.Copy
Rows(Row).EntireRow.Copy

'Destination sheet
Worksheets("Price2").Select
Rows(Rownum).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Worksheets("Price Card").Activate
End If
Next Row

 

[Loomah]

Hi
Using any of the above suggestions should copy all the formats including RowHeight, when copying EntireRow, but excluding Column widths.

Try this amendment to your code. I haven't tested it by creating relevant sheet names etc but based the code on hardcoded sheetnames and row numbers.

Sub lime()
Dim iCol As Integer
Dim Row As Long
Dim Boxsize As Long
For Row = 1 To Boxsize
    With Worksheets("Price Card")
        'copy data - this deals with all formatting apart from col width
        .Rows(Row).EntireRow.Copy Worksheets("Price2").Rows(Row + 1)
        'change COL widths in destination sheet
        For iCol = 1 To .UsedRange.Columns.Count
            Worksheets("Price2").Columns(iCol).ColumnWidth = _
                .Columns(iCol).ColumnWidth
        Next
    End With
Next Row
End Sub

Happy Friday
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[Loomah]

Ignore the above code as it's complete crubbish.

I missed bits and this one should work. I've also moved the column width bit so that it's only done once - there's no need to set them for every row is there! D'oh!

Sub lime()
Dim iCol As Integer
Dim Row As Long
Dim Boxsize As Long
With Worksheets("Price Card")
    Boxsize = .UsedRange.Rows.Count
    For Row = 1 To Boxsize
        If Rows(Row).Hidden = False Then
            'copy data - this deals with all formatting apart from col width
            .Rows(Row).EntireRow.Copy Worksheets("Price2").Rows(Row + 1)
        End If
    Next Row
    'change COL widths in destination sheet
    For iCol = 1 To .UsedRange.Columns.Count
        Worksheets("Price2").Columns(iCol).ColumnWidth = _
            .Columns(iCol).ColumnWidth
    Next iCol
End With
End Sub

Happier Friday?
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[bgarcia]

Can't thank you enough, the code works. And yes, it's a much happier Friday now.