CODEWITHSUNDEEP
excelvba
user1741915
user1741915

Reputation: 11

Excel/VBA code to automatically copy rows to another sheet

I've been trying to do this myself andhave trawled google but having no knowlege of VBA have come up short. I'm sure it's very simple if you know what you are doing:

Sheet 1 is the main spreadsheet Column B is the query column

When a cell in Column B equals "X" I want the whole row to be copied into Sheet 2, including any conditional formatting.

I want this to happen automatically and to update or overwrite the data in Sheet 2 (apart from row 1 which has the titles in).

So basically Sheet 2 should always contain an exact copy of all the rows in Sheet 1 where column B = "X".

Any help would be much appreciated!

Upvotes: 1

Views: 4424

Answers (1)

nutsch
nutsch

Reputation: 5962

Assuming your data starts in A1 of sheet1, this macro will transfer it to sheet2, overwriting everything in sheet2 except the first row. 

Sub FilterAndCopy()
Dim sht1 As Worksheet, sht2 As Worksheet

Set sht1 = Sheets("Sheet1")
Set sht2 = Sheets("Sheet2")

intersect(sht2.UsedRange,sht2.rows("2:" & rows.count)).ClearContents

sht1.Cells(1, 1).CurrentRegion.AutoFilter
sht1.Cells(1, 1).CurrentRegion.AutoFilter 1, "X"
sht1.Cells(1, 1).CurrentRegion.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Copy sht2.Cells(2, 1)
sht1.Cells(1, 1).CurrentRegion.AutoFilter

End Sub

Upvotes: 2

Related Questions