Copying range D2:End of Row from each sheet into master sheet's next open cell in row

[vpan]

Hi,

I am trying to merge any x amount of sheets into a master sheet.

Right now I am trying to copy each 2nd row from the sheets into the master sheet.

Code:

Please Login or Register  to view this content.

I believe this line is the one that is not working:
Code:

Please Login or Register  to view this content.

So that line is supposed to copy Range D2:EndCellInRow and paste it to the next open cell in Row 5 in mastersheet

Any ideas what is wrong?

[Bernie Deitrick]

There are two things wrong, either of which would keep your code from working.

The first is that you are not fully qualifying the cells object that you are using to define the range to copy. The second is that you are copying all but three columns from each sheet, and trying to fit them into the remaining cells of row 5, which will work for one sheet but not the others. So, replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

[vpan]

Hi Bernie,

Thank you for your response, I tried that solution and I am getting the same error. It's an application defined, object defined error

To add to this, as soon as remove that line of code, there is no error pop up

[Kenneth Hobson]

First off, here is how to set the Master sheet object:

Please Login or Register  to view this content.

The way you have it, it looks like you are copying rows to columns? If so, you will likely run out of columns soon.

Can you show how 1 or 2 copies might go? e.g.
1. Sheet(3).Range("D2:D20").Copy Master.Range("A2")
2. Sheet(4).Range("D2:F21").Copy Master.Range("A21")
3. Sheet(5).Range("D2:E5").Copy Master.Range("A41")

When you say D2:EndCellInRow is that "D2:D" & EndCellInRow or "D2:F" & EndCellInRow? (Last case is last row with value in column but columns D-F (last column in row of slave sheet))

[vpan]

Yeah,
I think when I was copying the code onto this message board, I must have deleted a space and hte set went up a line. Its not like that in my code

As for what Im trying to do, I am actually trying to move all the row number 2s in each sheet into row number 5 in master sheet (master has a few more headings up top so it has moved down 3 rows)

So Im trying to merge row -> row

So if sheet 3 goes to column 6 I want to copy D2:F2 from sheet 3 to D5:F5 in Master
Then in sheet 4 if it goes to column 10 I want to copy D2:J2 from sheet 4 to G5:P5 in Master
And so on for all the sheets

So it is the second option, "D2:F" & EndCellInRow, moving all data from row 2 (starting col D) in any sheet after the 3rd, to be pasted into the next empty cell in the Master Sheet's row5 , across the row, not going down in the columns

Thank you!

[Bernie Deitrick]

There is nothing wrong with the code - so there must be something in your workbook that is affecting the operation.

Here is a workbook with your code, and my adjustment - click the button on "Master"

[vpan]

Hi Bernie,

Thank you for showing me that, indeed I can see the code is not the problem
Just googled my error and this is what a microsoft support page says:
"This problem can occur when you give the workbook a defined name and then copy the worksheet several times without first saving and closing the workbook, as in the following sample code:"

any ideas what this means? Im a bit confused, I have to save the workbook each time the code is run?

Also this is the page where I found the explanation: https://support.microsoft.com/en-us/kb/210684

[Bernie Deitrick]

No - what it means is that you might have too many copies of an original worksheet. Earlier versions had an issue then, when sheets were copied, the codenames (what the VBA project refers to the sheet as) of the sheet got too long.

So, insert a reference to MS VBA extensibility:

Capture.JPG

Then run this code to try and fix the codename issue:

Please Login or Register  to view this content.

Finally, save the workbook, close it, re-open it, and try to run the original macro again.