OzGrid

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:

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:-

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.

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

See also:

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

Gallery