VBA code to loop through and move to next column until condition is met

[samjones833]

Hi all i currently have some code that copies data from one worksheet and pastes it into another, one column at a time. I'd like the code to loop and every time the loop occurs move to the next column to the right, however the loop would need to stop if the month name in row 4 does not match =TEXT(EOMONTH(TODAY(),0),"MMM"). My code looks up an item in a seperate spreadsheet which relates to the program number in cells B6:10, it then filters the spreadsheet and copies the data into column AH. It then repeats this process for all the other programs. Finally, for everytime the code passes through the loop the code would aslo need to change the cell if is copying the data from. Hope you can help and please ask any questions.

Sub Update()

Application.DisplayAlerts = False
Application.ScreenUpdating = False


Sheets("Gross X").Activate

'Remove autofilter
    Columns("A:AN").Select
    Selection.AutoFilter


'Add new Sheet
    Sheets.Add.Name = "New Sheet"


'Return to Gross X, select 100% fit item and paste into New Sheet for L461
    Sheets("Gross X").Activate
       ActiveSheet.Range("A:AN").AutoFilter Field:=1, Criteria1:= _
        "XYZ"
        Cells.Select
    Selection.Copy
    Sheets("New Sheet").Select
    Range("A1").Select
    ActiveSheet.Paste


'Copy Dmd and paste into Data sheet

    Sheets("New Sheet").Activate

    Range("J2").Select
    Selection.Copy
    Sheets("Data").Activate
     Range("B6").Activate
        ActiveCell.End(xlToRight).Offset(0, 1).Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        

'Delete sheets and provide msg box
 Sheets("New Sheet").Delete
 Sheets("Gross X").Activate
 ActiveSheet.ShowAllData
 Sheets("Data").Activate
 Range("AG2").Select

 Application.ScreenUpdating = True

 MsgBox ("Update Complete")
        
        
        
End Sub

 

[mumps]

Your code is a little confusing. You create a new sheet and then copy data from "Gross X" and paste it into the new sheet. Then you copy J2 from the new sheet and paste it to the "Data" sheet. Then you delete the new sheet. Why not just copy the data from "Gross X" and paste it directly into the "Data" sheet without creating a new sheet? Please clarify in detail.
It is hard to work with a picture. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).