CODEWITHSUNDEEP
excelvba
wazami
wazami

Reputation: 23

How to copy data without also copying the row/column width

I am using VBA to copy and paste a variable number of rows from one sheet to another when they meet a criteria.

This is working. However, when the data is pasted into the target sheet the column and row width change to be the same as the source sheet.

How can I stop this from happening? So that just the data is pasted, without the cell formatting.

If anyone knows it would be much appreciated.

Heres the code I'm using.

Sub copyOverdue()

Dim cell As Range
Dim j As Integer
Dim Source As Worksheet
Dim Target As Worksheet

Set Source = ActiveWorkbook.Worksheets("Action Register")
Set Target = ActiveWorkbook.Worksheets("Sheet1")

j = 36     ' Paste to this number row
For Each cell In Source.Range("A7:A243")
    If c = "Overdue" Then
       Source.Range("A" & c.row & "," & "G" & c.row).Copy Target.Range("AD" & j)
       Source.Range("C" & c.row & "," & "F" & c.row & "," & "H" & c.row & "," & "K" & c.row).Copy Target.Range("AF" & j)
       j = j + 1
    End If
Next cell
End Sub

Upvotes: 0

Views: 165

Answers (1)

SJR
SJR

Reputation: 23081

You can pastespecial values only

For Each cell In Source.Range("A7:A243")
    If cell.value = "Overdue" Then
       cell.Resize(, 7).Copy
       Target.Range("AD" & j).PasteSpecial xlValues
       Source.Range("C" & cell.Row & "," & "F" & cell.Row & "," & "H" & cell.Row & "," & "K" & cell.Row).Copy
       Target.Range("AF" & j).PasteSpecial xlValues
       j = j + 1
    End If
Next cell

You can also avoid the clipboard altogether and transfer the values directly, which is more efficient but it can get a bit mess you are dealing with large ranges.

Target.Range("AD" & j).Resize(,7).Value=cell.Resize(, 7).value

Upvotes: 2

Related Questions