For loop macro between workbooks

DrParmeJohnson

New Member
Joined
Feb 28, 2019
Messages
44
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

*ASIN*ASINItem#Title
*Barcoded ASIN*B006XXXXXXProduct name
VVVVVVVVVVVVVB007XXXXXXVVVVVVV

<tbody>
</tbody>

ASIN to LD.xlsx

ASINItem#
B006XXXXXX4500
B007XXXXXX4600

<tbody>
</tbody>


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.
 
Last edited:

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,621
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.

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.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

New Member
Joined
Feb 28, 2019
Messages
44
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,621
Re: Help with for loop macro between workbooks

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

DrParmeJohnson

New Member
Joined
Feb 28, 2019
Messages
44

ADVERTISEMENT

Re: Help with for loop macro between workbooks

Alright, sweet. Thank you again.
 

DrParmeJohnson

New Member
Joined
Feb 28, 2019
Messages
44
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

New Member
Joined
Feb 28, 2019
Messages
44

ADVERTISEMENT

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

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,621
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:
Code:
    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:
Code:
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:
Code:
        .Columns(Itemcolumn).HorizontalAlignment = xlLeft
Here is the complete code:
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

New Member
Joined
Feb 28, 2019
Messages
44
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,135
Messages
5,599,917
Members
414,348
Latest member
KloppyM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top