vba: copying three columns of filtered data to one row of another sheet

Question

I am trying to move data from three columns of one sheet to one row of another (I need to filter it to get this data). I need to pull every incident that can be filtered from the source sheet to plug into the destination sheet.

I am having issues with my filtered data to my destination sheet. I am trying to create a variable (sorter), filter by that variable, and then copy the filtered data to the destination sheet in the row where I got the sorter variable, then move to the next sorter variable. So far it looks like this:

    'Set up my loop based on id in cell from destination page
    For i = lrow To 1 Step -1                                                                   'need to double check the i and j - one should be from s and the other should be from d
        For j = 2 To lrow2 Step 1
        sorter = s.Cells(j, 1).Value
                'Sorting the source sheet
                With srange
                    .AutoFilter Field:=1, Criteria1:=sorter
                    'find the first and last row of the visible range
                    firstrow = .AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Row
                    lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
                        For Row = firstrow To lastrow
                            'select header row of source info
                            s.Range("c1").Select
                                'select copy the cells of the first filtered section
                                ActiveCell.Offset(1, 0).Select
                                        If Not cell.EntireRow.Hidden Then
                                            'ActiveCell.Copy d.Range("b2")
                                            'Copying and pasting first row
                                            ActiveCell.Offset(0, 1).Copy d.Cells(j, 2)
                                            ActiveCell.Offset(0, 2).Copy d.Cells(j, 3)
                                            ActiveCell.Offset(0, 3).Copy d.Cells(j, 4)
                                            'Copying and pasting second row
                                            ActiveCell.Offset(1, 1).Copy d.Cells(j, 5)
                                            ActiveCell.Offset(1, 2).Copy d.Cells(j, 6)
                                            ActiveCell.Offset(1, 3).Copy d.Cells(j, 7)
                                            'Copying and pasting third row
                                            ActiveCell.Offset(2, 1).Copy d.Cells(j, 8)
                                            ActiveCell.Offset(2, 2).Copy d.Cells(j, 9)
                                            ActiveCell.Offset(2, 3).Copy d.Cells(j, 10)
                                            'etc - I have 8 rows, but that should get me started
                                        End If
                        Next Row
                    End With
            Next j
    Next i

the line

  firstrow = .AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Row

and the line

   if not cell.entirerow.hidden then

both give the error 424 Object required when I try to get around them. I tried to implement the solution from this issue (VBA: Select the first filtered cell then move onto the next one down), but I'm pretty lost at this point. Thanks for any and all help.

Below is Issue was fixed by Scott - Thanks! - kept below for posterity

Issue 1 is that my ranges are dynamic, and I keep getting error 1004 on these lines of my code:

    dim lrow as string
    dim lrow2 as string

    lrow = s.Range("a" & Rows.Count).End(xlUp).Row
    lrow2 = d.Range("a" & Rows.Count).End(xlUp).Row

    set name1 = s.Range(Cells(2, 1), Cells(lrow, 1))
    set name2 = d.Range(Cells(2, 1), Cells(lrow2, 1))
    set srange = s.Range(Cells(1, 1), Cells(lrow, 5))                            
    set drange = d.Range(Cells(1, 1), Cells(lrow2, 10))

I copied this from another macro of mine that works, and it seems to be the same as other answers on the site(specifically this VBA: Selecting range by variables). When I hover over the variable, it shows the number I want, and when I replace with number it still doesn't work, so the variable isn't the issue. When I change to a1 notation it works fine (set drange = d.range("a1:j10")) it works fine as well, so I'm missing something simple that I can't see.

Your first problem: The range object `Cells()` inside the Range() need to be qualified with the same parent as the Range itself. For Example: `set name1 = s.Range(s.Cells(2, 1), s.Cells(lrow, 1))` — Scott Craner, Dec 12 '16 at 17:08

score 0 · Answer 1 · answered Dec 13 '16 at 14:51

I ended up just creating a loop through both pages and pasting the information. It takes much longer than I'd like, but I was doing it manually, so it is much faster than that. I know that it could be sped up a couple of different ways, but I thought I'd post my bad solution and if I create a good one I'll repost. I won't mark this solved, as it doesn't answer the question, and I'd still like it answered. Here is the code for my loop:

'Loop through source
'Loop starts with setting up i for source sheet
For i = 2 To lrow2 Step 1                            'i stays on destination sheet
'Setting up the loop for the destination sheet
For j = 2 To lrow Step 1                       'j stays on source sheet

    'sorting by range in the destination sheet
    sorter = d.Cells(i, 2).Value
        'Look in cell on source page
        lcol = d.Cells(i, d.Columns.Count).End(xlToLeft).Column
        If s.Cells(j, 2).Value = sorter Then _
            s.Cells(j, 15).Copy d.Cells(i, lcol + 1)
        If s.Cells(j, 2).Value = sorter Then _
            s.Cells(j, 16).Copy d.Cells(i, lcol + 2)
        If s.Cells(j, 2).Value = sorter Then _
            s.Cells(j, 17).Copy d.Cells(i, lcol + 3)

Next j
Next i

vba: copying three columns of filtered data to one row of another sheet

1 Answers1