StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POVBA Excel Merging dynamic ranges from two sheets into one, 1004 paste error
text
Body
copied!<p>I'm trying to merge data from two different spread sheets into one which becomes the data source for a couple pivot tables. Both sheets have different layouts so I'm looping through the first sheet to find the column, copy the data range below it and then paste into the wDATA sheet. Then go to the next sheet, find the same headers and then paste below the first block. I'm getting my favorite error, 1004. I've tried different proprieties and methods but it won't paste so here's what I started with. <a href="http://www.mediafire.com/?6o0zxpaxzou9n85" rel="nofollow">Link</a> is a file with the larger bit and data. I promise its clean. Any Help?</p> <pre><code> For x = 1 To iEndcol 'TOP SECTION OF DATA -FBL5N If InStr(Cells(1, x), "Sold") Then Range(Cells(2, x), Cells(lEndRowA, x)).Copy _ Destination:=wDATA.Range(Cells(1, 1), Cells(lEndRowA, 1)) ElseIf Cells(1, x) = "Invoice#" Then Range(Cells(2, x), Cells(lEndRowA, x)).Copy _ Destination:=wDATA.Range(Cells(1, 2), Cells(lEndRowA, 2)) ElseIf Cells(1, x) = "Billing Doc" Then Range(Cells(2, x), Cells(lEndRowA, x)).Copy _ Destination:=wDATA.Range(Cells(1, 3), Cells(lEndRowA, 3)) ElseIf InStr(Cells(1, x), "Cust Deduction") Then Range(Cells(2, x), Cells(lEndRowA, x)).Copy _ Destination:=wDATA.Range(Cells(1, 4), Cells(lEndRowA, 4)) ElseIf Cells(1, x) = "A/R Adjustment" Then Range(Cells(2, x), Cells(lEndRowA, x)).Copy _ Destination:=wDATA.Range(Cells(1, 5), Cells(lEndRowA, 5)) ElseIf InStr(Cells(1, x), "Possible Repay") Then Range(Cells(2, x), Cells(lEndRowA, x)).Copy _ Destination:=wDATA.Range(Cells(1, 6), Cells(lEndRowA, 6)) ElseIf InStr(Cells(1, x), "Profit") Then Range(Cells(2, x), Cells(lEndRowA, x)).Copy _ Destination:=wDATA.Range(Cells(1, 7), Cells(lEndRowA, 7)) End If Next End If ' DO NOT REDEFINE lEndrowA until all data is moved ' Fills in data from the second source, wLID If Not wLID Is Nothing Then wLID.Activate lEndRowB = Cells(4650, 1).End(xlUp).Row iEndcol = Cells(1, 1).End(xlToRight).Column For x = 1 To iEndcol 'BOTTOM If InStr(Cells(1, x), "Sold-To") Then Range(Cells(2, x), Cells(lEndRowB, x)).Copy _ Destination:=wDATA.Range(Cells(1, 1), Cells(lEndRowA + lEndRowB, 1)) ElseIf Cells(1, x) = "Invoice#" Then Range(Cells(2, x), Cells(lEndRowB, x)).Copy _ Destination:=wDATA.Range(Cells(1, 2), Cells(lEndRowA + lEndRowB, 2)) ElseIf Cells(1, x) = "Billing Doc" Then Range(Cells(2, x), Cells(lEndRowB, x)).Copy _ Destination:=wDATA.Range(Cells(1, 3), Cells(lEndRowA + lEndRowB, 3)) ElseIf InStr(Cells(1, x), "Cust Deduction") Then Range(Cells(2, x), Cells(lEndRowB, x)).Copy _ Destination:=wDATA.Range(Cells(1, 4), Cells(lEndRowA + lEndRowB, 4)) ElseIf Cells(1, x) = "A/R Adjustment" Then Range(Cells(2, x), Cells(lEndRowB, x)).Copy _ Destination:=wDATA.Range(Cells(1, 5), Cells(lEndRowA + lEndRowB, 5)) ElseIf InStr(Cells(1, x), "Possible Repay") Then Range(Cells(2, x), Cells(lEndRowB, x)).Copy _ Destination:=wDATA.Range(Cells(1, 6), Cells(lEndRowA + lEndRowB, 6)) ElseIf InStr(Cells(1, x), "Profit") Then Range(Cells(2, x), Cells(lEndRowB, x)).Copy _ Destination:=wDATA.Range(Cells(1, 7), Cells(lEndRowA + lEndRowB, 7)) End If Next End If </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload