how to auto-copy rows to new sheet VBA Excel

Question

I'm trying to auto-copy a row from a master spreadsheet to another spreadsheet. This should occur when the input value in the master is equal to X.

So if X is entered into Column A in the master, then auto-copy to separate spreadsheet (let's call it X). Basically Sheet X should always contain an exact copy of all the rows in master where Column A = X.

I'm not sure if this would affect the auto-copy but the master sheet contains a script that hides/unhides Columns. So if X is entered into Column A in the master sheet then Column B,C will be hidden and D,E,F will be shown.

An example of what I'm trying to achieve is shown below:

Master Sheet contains this info. But if X is entered into Column A only D,E,F will be visible

A B C D E F
X     4 5 6
Y 1 2 3 4 5
X     1 2 3

X Sheet:

A D E F
X 4 5 6
X 1 2 3

This is what I've attempted

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

Set sht1 = Sheets("Master")
Set sht2 = Sheets("X")

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

But it returns an Error:

Microsoft Visual Basic
Object variable with block variable not set

If Intersect returns Nothing, what object reference is .ClearContents invoked against? — Mathieu Guindon
– Mathieu Guindon, Commented Oct 10, 2018 at 4:57

DisplayName · Accepted Answer · 2018-10-15 07:01:53Z

1

+50

I guess you start clearing "X" sheet from row 2 to preserve headers

should this be the case, you can clear all "X" sheet rows and paste headers form "Master" sheet back

Option Explicit

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

    Set sht1 = Sheets("Master")
    Set sht2 = Sheets("X")

    sht2.UsedRange.ClearContents
    Dim rng As Range

    With sht1.Cells(1, 1).CurrentRegion
        .AutoFilter
        .AutoFilter 1, "X"
        For Each rng In .SpecialCells(xlCellTypeVisible).Areas ' loop through visible cells "groups"
            rng.Copy sht2.Range(rng.Address) ' copy current group and paste it to 'sht2' (i.e. sheet "X") corresponding address
        Next
        .AutoFilter
    End With

    With sht2.UsedRange ' reference 'sht2' used range
        .Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete ' delete referenced sheet blank rows
        .Rows(1).SpecialCells(xlCellTypeBlanks).EntireColumn.Delete ' delete referenced sheet blank columns
    End With
End Sub

Edited to account for possible "Master" sheet hidden columns

edited Oct 15, 2018 at 7:01

answered Oct 10, 2018 at 6:23

DisplayName

13.4k2 gold badges13 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Chopin Over a year ago

Will it matter if the master sheet contain a script that hides/unhides columns based off a specific value being entered in column A? Your code returns an error: That command cannot be used on multiple selections.

Chopin Over a year ago

The first line. Sub FilterAndCopy()

DisplayName Over a year ago

This cannot be. When you hit the error, click “debug” button and you’ll be brought to the offending line highlighted in yellow or blue

DisplayName Over a year ago

so from then on, step through your code by hitting F8 until you reach the actual offending line

Chopin Over a year ago

Sorry. It's on the .SpecialCells line

|

Collectives™ on Stack Overflow

how to auto-copy rows to new sheet VBA Excel

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related