For loop macro between workbooks

[DrParmeJohnson]

Hello,

I have tried to get this question answered before but to no avail, so I am trying again.

I have been struggling to make a macro that will simplify my workload.

Simply put, the macro works between two workbooks, one that is opened in XLSTART and the actual book that is being worked on. The always open book is called "ASIN to LD.xlsx" and the other book is not always the same so it is dynamically named through the macro to make matters easy. The macro also only has to work on one sheet on each book (both called Sheet1.

ActiveWorkbook

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]*ASIN*[/TD]
[TD]ASIN[/TD]
[TD]Item#[/TD]
[TD]Title[/TD]
[/TR]
[TR]
[TD]*Barcoded ASIN*[/TD]
[TD]B006XXXXXX[/TD]
[TD][/TD]
[TD]Product name[/TD]
[/TR]
[TR]
[TD]VVVVVVVVVVVVV[/TD]
[TD]B007XXXXXX[/TD]
[TD][/TD]
[TD]VVVVVVV[/TD]
[/TR]
</tbody>[/TABLE]

ASIN to LD.xlsx

[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD]ASIN[/TD]
[TD]Item#[/TD]
[/TR]
[TR]
[TD]B006XXXXXX[/TD]
[TD]4500[/TD]
[/TR]
[TR]
[TD]B007XXXXXX[/TD]
[TD]4600[/TD]
[/TR]
</tbody>[/TABLE]


This is how each file looks respectively. The tricky part is that while the ASIN to LD.xlsx file always has its columns in the same ranges "A:A" & "B:B", the other workbooks are not always the same and the ASIN column can be in different columns.

So, what I need help with is figuring out how to take a value from ActiveWorkbook (say B006XXXXXX), use that value to find its matching self on ASIN to LD.xlsx, then copy the Item# beside it, in this case 4500, paste that back onto the ActiveWorkbook in the same column (Item#) and then loop through each cell (of which the length of the column will not always be the same) until it runs out of cells in the ASIN column of ActiveWorkbook.

I know this is a long post and an extensive request (at least it seems to be to me) but, I have been at this for more hours than I wanted to be and would really appreciate some assistance, as I'm losing my mind over this.

If anymore information is necessary, please ask.

Thank you greatly.

 

[John_w]

Re: Help with for loop macro between workbooks

Put this code in a standard module in a new workbook and save it as macro-enabled workbook (.xlsm or .xlsb file).

With your 'ActiveWorkbook' and 'ASIN to LD.xlsx' workbooks open and the macro workbook open, run the macro from the 'ActiveWorkbook' workbook.

Public Sub Add_ASIN_Items_To_Active_Workbook()

    Dim col As Variant
    Dim ASINcolumn As Long, Itemcolumn As Long
    Dim ASINlastRow As Long
    Dim ASINfindRow As Variant
    Dim ASINtoLDsheet As Worksheet
    Dim r As Long
    
    Set ASINtoLDsheet = Workbooks("ASIN to LD.xlsx").Worksheets("Sheet1")

    With ActiveWorkbook.Worksheets("Sheet1")
        
        'Find ASIN and Item# columns in active workbook
        
        col = Application.Match("ASIN", .Rows(1), 0)
        ASINcolumn = col
        col = Application.Match("Item#", .Rows(1), 0)
        Itemcolumn = col
        ASINlastRow = .Cells(.Rows.Count, ASINcolumn).End(xlUp).Row
        
        'Loop through ASIN cells in active workbook, find ASIN in "ASIN to LD" workbook and copy corresponding Item# to Item# column in active workbook
        
        For r = 2 To ASINlastRow
            ASINfindRow = Application.Match(.Cells(r, ASINcolumn).Value, ASINtoLDsheet.Columns(1), 0)
            If Not IsError(ASINfindRow) Then
                .Cells(r, Itemcolumn).Value = ASINtoLDsheet.Cells(ASINfindRow, 2).Value
            End If
        Next
        
    End With
    
    MsgBox "Done"
    
End Sub

 

[DrParmeJohnson]

Re: Help with for loop macro between workbooks

I’ll let you know how this works on Monday when I’m back to my work computer but should this work the same if I have the ASIN to LD workbook open but hidden?

Also, thank you in advance for taking the time to help.

 

[John_w]

Re: Help with for loop macro between workbooks

I just tested the code with "ASIN to LD.xlsx" hidden and it still works

 

[DrParmeJohnson]

Re: Help with for loop macro between workbooks

Alright, sweet. Thank you again.

 

[DrParmeJohnson]

Re: Help with for loop macro between workbooks

Hey, just got to trying the macro and it works beautifully, only change I had to make was adding some code to make the macro rename whatever the activesheet was to "Sheet1" for consistency but, other than that, it worked perfectly.

I really appreciate the help as this has been killing me for weeks. If I do have any issues in the future, I will let you know but, it seems to be perfect.

Again, thank you!

 

[DrParmeJohnson]

Re: Help with for loop macro between workbooks

Quick question, if I wanted to Left align the text in the Itemcolumn after the loop is completed, how would I go about adding that?

I just guessed and tried this:

ActiveSheet.Cells(r, Itemcolumn).HorizontalAlignment = xlHAlignLeft

but, I really don't know what I'm doing so it doesn't work.

Thanks.

 

[John_w]

Re: Help with for loop macro between workbooks

Hey, just got to trying the macro and it works beautifully, only change I had to make was adding some code to make the macro rename whatever the activesheet was to "Sheet1" for consistency but, other than that, it worked perfectly.

I really appreciate the help as this has been killing me for weeks. If I do have any issues in the future, I will let you know but, it seems to be perfect.

Again, thank you!

You should really change the code to make it suit your data rather than the other way around. I used Sheet1 in the code because you said that was the name of both sheets. It is easy to change the code to use the active sheet instead of a sheet with a specific name:

    With ActiveWorkbook.ActiveSheet

Quick question, if I wanted to Left align the text in the Itemcolumn after the loop is completed, how would I go about adding that?

I just guessed and tried this:

ActiveSheet.Cells(r, Itemcolumn).HorizontalAlignment = xlHAlignLeft

but, I really don't know what I'm doing so it doesn't work.

Thanks.

For things like this I usually record a macro to get the syntax and properties etc. and incorporate it into the code. The macro recorder produced this:

Sub Macro1()
    Windows("THE ACTIVE WORKBOOK.xlsx").Activate
    Columns("C:C").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
End Sub

which can be reduced to the following line in the existing code:

        .Columns(Itemcolumn).HorizontalAlignment = xlLeft

Here is the complete code:

Public Sub Add_ASIN_Items_To_Active_Workbook()

    Dim col As Variant
    Dim ASINcolumn As Long, Itemcolumn As Long
    Dim ASINlastRow As Long
    Dim ASINfindRow As Variant
    Dim ASINtoLDsheet As Worksheet
    Dim r As Long
    
    Set ASINtoLDsheet = Workbooks("ASIN to LD.xlsx").Worksheets("Sheet1")

    With ActiveWorkbook.ActiveSheet
        
        'Find ASIN and Item# columns in active workbook
        
        col = Application.Match("ASIN", .Rows(1), 0)
        ASINcolumn = col
        col = Application.Match("Item#", .Rows(1), 0)
        Itemcolumn = col
        ASINlastRow = .Cells(.Rows.Count, ASINcolumn).End(xlUp).Row
        
        'Loop through ASIN cells in active workbook, find ASIN in "ASIN to LD" workbook and copy corresponding Item# to Item# column in active workbook
        
        For r = 2 To ASINlastRow
            ASINfindRow = Application.Match(.Cells(r, ASINcolumn).Value, ASINtoLDsheet.Columns(1), 0)
            If Not IsError(ASINfindRow) Then
                .Cells(r, Itemcolumn).Value = ASINtoLDsheet.Cells(ASINfindRow, 2).Value
            End If
        Next
        
        'Left align the Item# column
        
        .Columns(Itemcolumn).HorizontalAlignment = xlLeft
        
    End With
    
    MsgBox "Done"
    
End Sub

 

[DrParmeJohnson]

Re: Help with for loop macro between workbooks

Just put the new code in and it works as expected. And I appreciate the tips, I was using "Sheet1" and renaming it just because not all the sheets are usually called "Sheet1" but, I see how it would be simpler to just use Activesheet.

Again, I appreciate the help.