How to Copy entire row - keeping formulas

How to copy entire row - keeping formulas

Requirement:

The user is trying to copy from Sheet1 based on qualifying data (Line1) in column B. The user can copy the data to A10 in sheet2 no worries but it pastes the values which the user does not want. The user wants to keep the formulas.

Sub test()

Application.ScreenUpdating = False
Sheet1.Select

With ActiveSheet
.AutoFilterMode = False
With Range("B6", Range("B" & Rows.Count).End(xlUp))
.AutoFilter 1, "Line1"
On Error Resume Next
.EntireRow.Copy Sheet2.Range("A10")
End With
.AutoFilterMode = False
End With

End Sub

Solution:

Code:

Sub test()

Application.ScreenUpdating = False
Sheet1.Select

With ActiveSheet
.AutoFilterMode = False
With Range("B6", Range("B" & Rows.Count).End(xlUp))
.AutoFilter 1, "Line1"
On Error Resume Next
.EntireRow.Copy
Sheet2.Range("A10").PasteSpecial xlPasteAll
End With
.AutoFilterMode = False
End With
End Sub

The part in bold will paste values, formulae, formatting etc.

If you want, you could tidy up the code a little as follows:-

Code:

Sub Test()

Application.ScreenUpdating = False

With Sheet1.Range("B6", Sheet1.Range("B" & Sheet1.Rows.Count).End(xlUp))
       .AutoFilter 1, "Line1"
       .EntireRow.Copy
       Sheet2.Range("A10").PasteSpecial xlPasteAll
       .AutoFilter
End With

Application.ScreenUpdating = True

End Sub

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1215742-copying-entire-row-keeping-formulas

Obtained from the OzGrid Help Forum.

Solution provided by vcoolio.