VBA Loop: start pasting only after certain rows

[jefffMarch]

I have two sheets in a workbook, where the first sheet has a column which has a couple of teams. I created this script that loops through the columns on sheet 1, and copy the team name, and some data around it. Once copied, it activate sheet2, and paste the 3 columns copied from sheet1. Now, what I want is that it is about to paste on sheet2 in columnA for team A, instead of pasting starting at A1, I want it to start at A4 because there are some formula in the first few rows. The same should happen for team B, and C....Since this is happening in a loop, it was easy for me to make some mistakes which causes the pasting to have "offset" after each paste...Of course I don't want that. The skipping should happens only during the first paste, everything after should be fine (which my code can do by finding the last empty row and paste there). So how can I spike a few rows in a loop, and not repeat it again?

Public Sub CopyRows()
    Sheets("Raw Data - Weekday").Select
    ' Find the last row of data
    FinalRow = Cells(Rows.Count, 25).End(xlUp).Row
    ' Loop through each row
    For x = 2 To FinalRow
        ' Decide if to copy based on column D
        ThisValue = Cells(x, 25).Value
        If ThisValue = "A Channel Support" Then
            'Cells(x, 16).Copy
            Range(Cells(x, 24), Cells(x, 26)).Copy
            Sheets("Data Table").Select
            'NextRow = Cells(Rows.Count, 3).End(xlUp).Row + 1
            NextRow = Cells(Rows.Count, 3).End(xlUp).Row + 1
            'NextRow = Cells(Rows.Count, 5).End(xlUp).Offset(21)
            'Cells(NextRow, 5).Offset(rowOffset:=21).Select
            Cells(NextRow, 3).Select
            ActiveSheet.Paste
            Sheets("Raw Data - Weekday").Select
        ElseIf ThisValue = "B Channel Enablment" Then
            Range(Cells(x, 24), Cells(x, 26)).Copy
            Sheets("Data Table").Select
            NextRow = Cells(Rows.Count, 9).End(xlUp).Row + 1
            Cells(NextRow, 9).Select
            ActiveSheet.Paste
            Sheets("Raw Data - Weekday").Select
         ElseIf ThisValue = "C Enterprise" Then
            Range(Cells(x, 24), Cells(x, 26)).Copy
            Sheets("Data Table").Select
            NextRow = Cells(Rows.Count, 15).End(xlUp).Row + 1
            Cells(NextRow, 15).Select
            ActiveSheet.Paste
            Sheets("Raw Data - Weekday").Select
        ElseIf ThisValue = "D Core Horizon" Then
            Range(Cells(x, 24), Cells(x, 26)).Copy
            Sheets("Data Table").Select
            NextRow = Cells(Rows.Count, 21).End(xlUp).Row + 1
            Cells(NextRow, 21).Select
            ActiveSheet.Paste
            Sheets("Raw Data - Weekday").Select
        ElseIf ThisValue = "E Salesforce" Then
            Range(Cells(x, 24), Cells(x, 26)).Copy
            Sheets("Data Table").Select
            NextRow = Cells(Rows.Count, 27).End(xlUp).Row + 1
            Cells(NextRow, 27).Select
            ActiveSheet.Paste
            Sheets("Raw Data - Weekday").Select
        End If
    Next x
End Sub

 

[Fluff]

Hi & welcome to MrExcel.
How about

Public Sub jefffMarch()
   Dim Ary As Variant
   Dim i As Long, UsdRws As Long
   
   Ary = Array("A Channel Support", 3, "B Channel Enablment", 9, "C Enterprise", 15, "D Core Horizon", 21, "E Salesforce", 27)
   With Sheets("Raw Data - Weekday")
      UsdRws = .Cells(Rows.Count, 25).End(xlUp).Row
      For i = 0 To UBound(Ary) Step 2
         .Range("A1:Z" & UsdRws).AutoFilter 25, Ary(i)
         .AutoFilter.Range.Offset(1).Columns("X:Z").Copy Sheets("Data Table").Cells(4, Ary(i + 1))
      Next i
      .AutoFilterMode = False
   End With
End Sub

 

[jefffMarch]

Hi & welcome to MrExcel.
How about

Public Sub jefffMarch()
   Dim Ary As Variant
   Dim i As Long, UsdRws As Long
  
   Ary = Array("A Channel Support", 3, "B Channel Enablment", 9, "C Enterprise", 15, "D Core Horizon", 21, "E Salesforce", 27)
   With Sheets("Raw Data - Weekday")
      UsdRws = .Cells(Rows.Count, 25).End(xlUp).Row
      For i = 0 To UBound(Ary) Step 2
     [B]    .Range("A1:Z" & UsdRws).AutoFilter 25, Ary(i)[/B]
         .AutoFilter.Range.Offset(1).Columns("X:Z").Copy Sheets("Data Table").Cells(4, Ary(i + 1))
      Next i
      .AutoFilterMode = False
   End With
End Sub

Wow, I need sometimes to wrap my mind around this short and concise code; Thank you...
I tried it, but only got one out of bound error on the first line after the for loop (.Range(A1:Z".....). I will resolve that first.

 

[jefffMarch]

Wow, I need sometimes to wrap my mind around this short and concise code; Thank you...
I tried it, but only got one out of bound error on the first line after the for loop (.Range(A1:Z".....). I will resolve that first.

Actually, it is: "AutoFilter method of Range class failed"

 

[Fluff]

Do you have a header row in row 1 with values in every cell from A1 to Z1?
Also is you data in a structured table, or just a normal range?

 

[jefffMarch]

Do you have a header row in row 1 with values in every cell from A1 to Z1?
Also is you data in a structured table, or just a normal range?

In the source sheet (Raw Data), here it has header. In the picture below, the Y column is where filtering happens; They are in normal range

 

[Fluff]

Ok, how about

Public Sub jefffMarch()
   Dim Ary As Variant
   Dim i As Long, UsdRws As Long
  
   Ary = Array("A Channel Support", 3, "B Channel Enablment", 9, "C Enterprise", 15, "D Core Horizon", 21, "E Salesforce", 27)
   With Sheets("Raw Data - Weekday")
      UsdRws = .Cells(Rows.Count, 25).End(xlUp).Row
      For i = 0 To UBound(Ary) Step 2
         .Range("X1:Z" & UsdRws).AutoFilter 2, Ary(i)
         .AutoFilter.Range.Offset(1).Copy Sheets("Data Table").Cells(4, Ary(i + 1))
      Next i
      .ShowAllData
   End With
End Sub

Although those look like tables to me.

 

[Fluff]

If the code still fails, which I think it might, select X2 & then look at the ribbon & there should be a tab labelled "Table Design", click on that & at the left of the ribbon it will say Table Name & below that will show the name of the table.
What is the name?

 

[jefffMarch]

Yeah, you've guessed it right! There is a small issue but overall we are moving in the right direction. So now, I am getting the "Object variable or With block variable not set" error. However, when I put the cursor in the range (X2..) and run the script, it works; the data is being pasted (4 columns, instead of 3 that I need).

Anyhow, here is the name: TicketAgingDetails (Its the name that I gave given to the output of the SQL script)

 

[Fluff]

Ok, try

Public Sub jefffMarch()
   Dim Ary As Variant
   Dim i As Long, UsdRws As Long
   
   Ary = Array("A Channel Support", 3, "B Channel Enablment", 9, "C Enterprise", 15, "D Core Horizon", 21, "E Salesforce", 27)
   With Sheets("Raw Data - Weekday").ListObjects("TicketAgingDetails")
      For i = 0 To UBound(Ary) Step 2
         .Range.AutoFilter 2, Ary(i)
         .AutoFilter.Range.Offset(1).Columns("A:C").Copy Sheets("Data Table").Cells(4, Ary(i + 1))
      Next i
      .AutoFilter.ShowAllData
   End With
End Sub