Copy/Paste 30 Times or Loop?

[GeorgY]

Hi guys, I've attached below a small part of my code. I am wondering if there is a way to do this, without copying and pasting something 30 times, but using a loop instead.

As you can see there are a series of productsNumbers (30 in total, but this example is for 3). Each productNumber is a string that is actually a 5 digit number, so it can be an integer as well, if it has to.

I would like to code below to loop for as many products as there may be. The way that I have it right now does not work - I feel that I am missing something small or that there is no way to do this. Suggestions are apperciated. Thanks!

Sub Summarize()

    Dim productNumber1 As String
    Dim productNumber2 As String
    Dim productNumber3 As String

    Dim fCell As Range
    Dim lCell As Range
    Dim rng As Range
    
    Dim i As Integer
    
    productNumber1 = Sheets("SKUs Input").Range("A3")
    productNumber2 = Sheets("SKUs Input").Range("A4")
    productNumber3 = Sheets("SKUs Input").Range("A5")
    
    For i = 1 To 3
    
    'Atlantic
    
    Sheets("Atl").Select
    Set fCell = Range("E2")
    Set lCell = Range("E65536").End(xlUp)
    Set rng = Range(fCell, lCell)
        
    Sheets("Calculations").Select
    Range("A2", "G98").Select
    Selection.Clear
    
    For Each Cell In rng
        If Cell = "productNumber" & i Then
            Cell.Resize(1, 7).Copy
            Sheets("Calculations").Select
            Range("A65536").End(xlUp).Offset(1, 0).Select
            Selection.PasteSpecial (xlPasteValues)
            ActiveCell.Offset(1, 0).Select
        End If
    Next
    
    Sheets("Calculations").Select
    Range("H100").Copy
    Sheets("STAX Summary").Select
    Range("C22").End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial (xlPasteValues)
    
    Next i
    
End Sub

[Cheeky Charlie]

Oh my, yes there are a few improvements...

Why not just use autofilter?

Also, post an example.
CC

[Richard Buttrey]

Hi,

It seems you've already got the guts of your loop. If I've understood correctly, delete the productnumber1, productnumber2 etc. variables.

Make the loop counter(assuming the product numbers are in a contiguous range),

For i = 1 to Sheets("SKUs Input").Range("A3").CurrentRegion.Rows.Count

Then in the For Each Cell..Next cell loop

If Cell = Range("A3").Cells(i,1) Then

What's the object of the code? There may be a simpler way of solving your requirement, perhaps a pivot table???

HTH

[GeorgY]

Thanks Richard, that was exactly what I was looking for.

Yes, it's currently being done by pivot table. I like code more.