PDA

View Full Version : [SOLVED] VBA to Copy / Paste into New Workbook With Different Layout by Column & Row Headings



ssu02193
05-03-2018, 04:33 PM
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:

22168
And the Target:

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.

offthelip
05-04-2018, 03:38 AM
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)

ssu02193
05-04-2018, 06:39 AM
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.

ssu02193
05-09-2018, 04:02 PM
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.

offthelip
05-09-2018, 04:31 PM
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 .

ssu02193
05-15-2018, 04:33 PM
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.

offthelip
05-16-2018, 01:54 AM
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

ssu02193
05-20-2018, 03:15 PM
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!

offthelip
05-20-2018, 04:09 PM
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

ssu02193
05-21-2018, 05:47 AM
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!