Filter, Copy and Paste Loop with Non Constant Column Location in VBA

Question

I'm looking for some help with putting together some code in VBA that will filter a column with a certain header name, copy and paste that information into a second sheet, and then perform the same filter, copy, paste action for each value that is in the column. Unfortunately the column will not always be in the same location.

Any help would be much appreciated.

Below is what I've got so far:

Dim lastrow As Long
Dim lastcol As Long
Dim SSheet As Worksheet
Dim DSheet As Worksheet
Dim PRange As Range

'Define Data Range
Set SSheet = Worksheets("All Data")
Set DSheet = Worksheets("Data")
lastrow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
lastcol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(lastrow, lastcol)

SSheet.Select
Selection.AutoFilter.Sort.SortFields.Clear
ActiveSheet.ShowAllData
Rows("1:1").Select
Selection.Find(What:="Job Group", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveSheet.Range("$A$1:" & lastrow, lastcol).AutoFilter Field:=14, Criteria1:= _"1A"
Cell ("A1").Select
Range("$A$1:" & lastrow, lastcol).Select
Selection.Copy
DSheet.Select
Range("A1").Select
ActiveCell.Paste
Application.CutCopyMode = False

You should start by recording a macro first, then see what that produces. — dwirony, Dec 12 '18 at 19:07
Can you instead please edit the code in to your original post? Also, please read [how to avoid using `.Select`\`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — BruceWayne, Dec 12 '18 at 19:37

score 0 · Answer 1 · answered Dec 13 '18 at 09:11

0

Sub Button1_Click()
    Dim lastrow As Long
    Dim lastcol As Long
    Dim SSheet As Worksheet, Lst As Long
    Dim DSheet As Worksheet
    Dim PRange As Range, fRng As Range, f As String, c As Range

    f = "Job Group"

    Set SSheet = Worksheets("All Data")
    Set DSheet = Worksheets("Data")

    With SSheet
        Lst = .Cells(.Rows.Count, "A").End(xlUp).Row + 1

        With DSheet
            lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
            lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Column
            Set fRng = .Range(.Cells(1, 1), .Cells(1, lastcol))
            Set c = fRng.Find(what:=f, lookat:=xlWhole)
            Set PRange = .Cells(1, 1).Resize(lastrow, lastcol)
            If .AutoFilterMode Then
                .AutoFilter.Sort.SortFields.Clear
                .AutoFilterMode = False
            End If
            .Range("A1").AutoFilter Field:=c.Column, Criteria1:="1A"
        End With

        PRange.Offset(1).Copy .Cells(Lst, "A")
    End With
End Sub

answered Dec 13 '18 at 09:11

Davesexcel

6,896
2
27
42

I'm getting an error that the variable is not set when the code gets to the end of the If. Also, I don't think this is currently set up to loop and perform the copy paste for all values in the filtered column, which is really what I'm hoping to do with the code. – Mike Childers Dec 13 '18 at 18:05
I assure you I would not supply a code without testing it myself. You would have to be lacking some information. – Davesexcel Dec 13 '18 at 18:47
@MikeChilders If you are getting "Run-time error '91': Object variable or With block variable not set" on the line `.Range("A1").AutoFilter Field:=c.Column, Criteria1:="1A"`, then it probably means that `c Is Nothing` - this means that the value `f` (`"Job Group"`) was not found in `fRange` (Row 1 of the "Data" worksheet) - however, the code in the *question* suggests that we should be searching the "**All** Data" sheet instead: `Set fRng = sSheet.Range(sSheet.Cells(1, 1), sSheet.Cells(1, lastcol))` - this is why providing sample data in the question can be very useful! – Chronocidal Feb 04 '20 at 09:36

Filter, Copy and Paste Loop with Non Constant Column Location in VBA

1 Answers1