Consulting

Results 1 to 10 of 10

Thread: VBA to Copy / Paste into New Workbook With Different Layout by Column & Row Headings

  1. #1

    VBA to Copy / Paste into New Workbook With Different Layout by Column & Row Headings

    Thanking you in advance.

    I'm trying to copy and paste data from a workbook I receive regularly into a new workbook. The goal is to get the updated data in the right place in the new workbook.

    The source workbook has much filler space that I don't need in the target and sometimes columns and/or rows are changed in the source compared to the last delivery. I've tried copying the cell contents from the intersection of each row/column header in the source into the same row/column intersection in the target. I've tried using Index/Match and intersect commands with no success. My attempts have been useless and too newbie to show. Because I've locked into row/column header thought, I'm just not seeing other ways to do this that are probably obvious to others.

    Here's a screenshot of the source:

    Source.jpg
    And the Target:

    Attachment 22162

    I've attached simplified versions of the source and target files.

    Thanks, would really appreciate any ideas. Not asking for entire code, just a direction at what methods I should be using.

    I'm on win7 and Excel 2010.
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    I have done something similar in the past and i did by using a extra sheet tthat defined the mapping between the source file and the target file. This was because it makes it very easy to change if the source data changes.
    I have modified this code for your appplications you can see it is very brief. I have also attached the workbook with the mapping table in it
    Sub remap() With Worksheets("MapData")
    mapping = Range(.Cells(1, 1), .Cells(120, 4))
     End With
    Workbooks("Source File.xlsx").Activate
    SourceData = Worksheets("Sheet1").Range(Cells(1, 1), Cells(55, 9))
    Workbooks("Target File.xlsm").Activate
    With Worksheets("Sheet1")
      Range(.Cells(1, 1), .Cells(20, 7)) = ""
      outarr = Range(.Cells(1, 1), .Cells(20, 7))
    'loop through each line of the mapping and copy from one array to the other
     For i = 2 To 120
       outarr(mapping(i, 4), colno(mapping(i, 3))) = SourceData(mapping(i, 2), colno(mapping(i, 1)))
     Next i
      Range(.Cells(1, 1), .Cells(20, 7)) = outarr
      
     End With
    End Sub
    
    
    Function colno(letr)
    ' this fuction converts a column letter to a column number index it only works up column Z
    
    
    colno = Asc(letr) - 64
    End Function
    Note I had to Unmerge the A2 cells and the G2 cells to get it to work . (if you are using VBA i suggest avoiding merging cells it is difficult to handle in VBA)
    Attached Files Attached Files

  3. #3
    Wow, that was fast, thanks! Yes, merged cells make me crazy, but some people love 'em.

    Have a great day. I'll let you know how it works.

  4. #4
    Thanks again offthelip! It works perfectly and I have two questions. I believe I know how you're building outarr, but I'm not understanding how the Mapdata tab data gets generated. You didn't enter all that by hand did you?

    Also, curious why you used a With statement to define mapping instead of dim and set?

    Appreciate the help.

  5. #5
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    I did generate the Mapdata roughly by hand, I genrated the row numbers by putting the excel function Row() into every row and then filtering for for "Totals", that gave me the row numbers , then you can see in column H, I did a mapping of which source column was going to which target column , I thought about writing some VBA to do it automatically from that but because there were only 7 columns I just copied the rows down and edited the columns by hand.
    The with statement is just an easy way of referencing a worksheet without needing to select it. This allows me to load the variant array. This is completely different for doing a dim a variable as a range and then set variable to a range.
    I avoid setting a variable to a range, this is because it is very very slow, jsut as slow as accessing the worksheet ( which is actuallly what is happening). I do everything in variant arrays if at all possible. I would recommmend using it all the time so that you get familar with doing this using variant arrays. If you only use when you have ended up with slow code you won't be familar with them when you need them.
    so I avoid using ranges in vba as far as possible
    I avoid using any worksheet function that needs a range as a parameter, e.g Vlookup, etc
    I avoid accessing the worksheet as much as possible in particular I avoid accessing the worksheet in a loop
    Just doing this means that my macros usually run in milliseconds, I don't need to worry about turning screen updates or calculations on or off, because I usually only make one or two updates .

  6. #6
    offthelip - your comment about "writing some VBA to do it automatically" inspired me to try but i'm not getting anywhere. This could be very helpful to me in countless ways. Would you please point me in the right direction to automate the mapping? Again, not asking you to write code for me, just a pointer or two.

    Thanks and have a great day.

  7. #7
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    The easiest way is to write some code to do it, I am not sure this is exactly correct but it shows you the wasy i would do it ( all with variant arrays again!!). I am assuming that the mapping is in H2 to M3 and the numbers from the filter are in B2 to B16

    Sub test()
    Worksheets("Mapdata").Select
    
    
    inarr = Range(Cells(2, 8), Cells(3, 13))
    numbs = Range(Cells(2, 2), Cells(16, 2))
    Range(Cells(1, 1), Cells(120, 4)) = ""
    outarr = Range(Cells(1, 1), Cells(120, 4))
    
    
    indi = 2
    For i = 1 To 6
     For j = 1 To 15
       outarr(indi, 1) = inarr(1, i)
       outarr(indi, 2) = numbs(j, 1)
       outarr(indi, 3) = inarr(2, i)
       outarr(indi, 4) = j
       indi = indi + 1
      Next j
    Next i
    Range(Cells(1, 1), Cells(120, 4)) = outarr
    End Sub

  8. #8
    offthelip - I've been struggling trying to use what you provided above, maybe because you're light years ahead of me - I'm still married to option explicit!

    Would you mind taking a look at the attached? Was trying to use pieces of what you provided to
    copy data from a range and paste it to another range where the row and column headers have changed. I can make it work fabulously but only once,

    If I try to use a dynamic arrays to step through different the different titles, VBA yells at me that I can't use i as a counter. But if I use a static array, VBA screams that I can't fill the array with a range... What to do?
    I seem to have managed to make this absurdly complicated, would very much appreciate your thoughts.

    Even though small, I have countless uses for this if I can automate it because clients and suppliers are always giving me data in completely different forms.

    Thanks again offthelip!
    Attached Files Attached Files

  9. #9
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    Hi ssu,
    You are still thinking like an Excel user and not a vba programmer!! You don't need to set variables to ranges, ( I avoid it as much as possible because it makes for slow VBa)
    You never need to use find , you can do a direct comparison in VBa or if you need to look with the cell you can use the Instr () function . This will usually be faster than using hte excel worksheet fucntiosn.
    The code I have written below will remap the cells according toe the cells headers and first columns titles.
    ( I have even declared the variable for you), I think it is waste of time, because loading arrays from cells will always be variant( which is unhelpful anyway) and writing to cells you always need use variants and for indexing you always need a number of some sort.
    So to my way of thinking declaring variables doesn't help anybody who knows what they are doing at all.
    I have annotated the code to help you

    Sub Fast()
        Dim i As Long
        Dim j As Long
        Dim k As Long
        Dim m As Long
        Dim Source As Variant
        Source = Range(Cells(2, 1), Cells(4, 3))
        Dim Target As Variant
        Target = Range(Cells(2, 5), Cells(4, 7))
    
    
    'loop through rows on source
     For i = 1 To 3
            ' loop through rows on target
          For k = 1 To 3
          ' find matching rows
            If Source(i, 1) = Target(k, 1) Then
             ' now find the mathcing columns
                ' loop throuhg columns on source
                For j = 1 To 3
                ' loop through columns on target
                 For m = 1 To 3
                       ' find matchinf
                    If Source(1, j) = Target(1, m) Then
                     ' we have found the matching cells so copy it across
                      Target(k, m) = Source(i, j)
                    End If
                  Next m
                Next j
           End If
         Next k
       
       Next i
       
         Range(Cells(2, 5), Cells(4, 7)) = Target
          
    End Sub

  10. #10
    Ha - I confess, guilty as charged! Looking forward to the day i do think like a vba programmer but i suspect it'll take more than three lessons. The elegance and simplicity of what you wrote is a thing of beauty. Works perfectly. Thanks for your patience!

Posting Permissions

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