Results 1 to 9 of 9

Thread: For loop macro between workbooks
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Feb 2019
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post For loop macro between workbooks

    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* ASIN Item# Title
    *Barcoded ASIN* B006XXXXXX Product name
    VVVVVVVVVVVVV B007XXXXXX VVVVVVV

    ASIN to LD.xlsx

    ASIN Item#
    B006XXXXXX 4500
    B007XXXXXX 4600


    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 by DrParmeJohnson; Jul 12th, 2019 at 05:03 PM.

  2. #2
    Board Regular
    Join Date
    Oct 2007
    Posts
    5,757
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    2 Thread(s)

    Default 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

  3. #3
    New Member
    Join Date
    Feb 2019
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

  4. #4
    Board Regular
    Join Date
    Oct 2007
    Posts
    5,757
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Help with for loop macro between workbooks

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

  5. #5
    New Member
    Join Date
    Feb 2019
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with for loop macro between workbooks

    Alright, sweet. Thank you again.

  6. #6
    New Member
    Join Date
    Feb 2019
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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!

  7. #7
    New Member
    Join Date
    Feb 2019
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

  8. #8
    Board Regular
    Join Date
    Oct 2007
    Posts
    5,757
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Help with for loop macro between workbooks

    Quote Originally Posted by DrParmeJohnson View Post
    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
    Quote Originally Posted by DrParmeJohnson View Post
    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

  9. #9
    New Member
    Join Date
    Feb 2019
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •