VBA / Macro / Formula Advice

[modstop]

Hi All,

So, I'm currently putting together a CSV file which is around 50k lines

I am having to copy and paste product codes manually into a certain number of cells and then move down to the next product code.

Does anyone have any advise on how to write a formula or is able to show me some vba or macro that i could use to enable it to copy and past down automatically and then know when to swap to the next product code?

So the new product code renews every time the column on the left says black.

Any help would be very much appreciated as this is taking a huge amount of time up

 

[Joe4]

That is telling you that you do not have a worksheet in that workbook with the name "HELINFO".
Is it in the same workbook?
Are you sure that is the exact name?
Double-click on the name in the tab so it goes into edit, and see if there are any extra characters anywhere (i.e. special blank spaces, etc).

 

[Joe4]

If you cannot figure it out, run this little macro, and paste the results of the Message Box here:

Sub ListTabName()

    Dim ws As Worksheet
    Dim str As String
    
    For Each ws In Worksheets
        str = str & ws.Name & "," & vbCrLf
    Next ws
    
    MsgBox str
    
End Sub

 

[modstop]

So i got it to run correctly however it just copied "product code" into each box where i needed the actual Product code to go

 

[Joe4]

The code was written for column A, not column I. So I think we just need to make this change:

Sub MyMacro()

    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim lr As Long
    Dim r1 As Long
    Dim r2 As Long
    
    Application.ScreenUpdating = False
    
'   Set worksheet variables
    Set ws1 = Sheets("Sheet1")
    Set ws2 = Sheets("Sheet2")
    
'   Find last row in column I with data on main sheet (ws1)
    lr = ws1.Cells(Rows.Count, "I").End(xlUp).Row
    
'   Initalize r2
    r2 = 1

'   Loop through all rows on sheet1
    For r1 = 1 To lr
'       Check to see in entry in column I on sheet 1 is "black"
        If ws1.Cells(r1, "I") = "black" Then
'           Increment row counter on sheet 2 by 1
            r2 = r2 + 1
        End If
'       Populate column M on ws1 with code from ws2
        ws1.Cells(r1, "M") = ws2.Cells(r2, "A")
    Next r1
    
    Application.ScreenUpdating = True
    
    MsgBox "Macro complete!"
    
End Sub

Don't forget to change the ws1 and ws2 references in the code to match your sheet names.

 

[modstop]

this is the current reset from it

 

[Joe4]

this is the current reset from it

OK, it looks like you changed another important detail on me!

In your original example, you were looking for "black". But in your example posted above, you have "Black".
It matters!

Update the VBA code to look for "Black" instead of "black".

If you may have a mix of "black" and "Black", and want both cases to change the value being brought black, then change that line of code to:

If UCase(ws1.Cells(r1, "I")) = "BLACK" Then

 

[modstop]

My apologies Joe,

Will this stop the code from bring back the result that's in A1 and work its way down the list now?

 

[Joe4]

Will this stop the code from bring back the result that's in A1 and work its way down the list now?

Yes. Try it and see!

If it does not work properly, then please post the following:
1. Image of the first ten rows of your first sheet, being sure to expand column M far enough so we can see what is in it.
2. Image of the first ten rows of your second sheet.

 

[modstop]

It worked! you have no idea how much time you have saved me, i assume i can reuse this bit of VBA code for other spreadsheets if it fits the same parameters?

Appreciate the help!

you are a lifesaver

 

[Joe4]

It worked! you have no idea how much time you have saved me, i assume i can reuse this bit of VBA code for other spreadsheets if it fits the same parameters?

Appreciate the help!

you are a lifesaver

You are welcome.

Yes, you should be able to re-use this code for similar problems, with a few minor edits.