Copying from multiple wkshts into one wrksht using a loop for endof row for each wkst

[katkins0429]

Hi I am wondering if someone could help me with a soloution. I have a workbook that has specific data feeding from various other workbook (using linked formulas). Within this workbook (my master workbook) I need to create a copy of all the worksheets (not the menue sheet or the cmd sheet) into one master sheet. However since each worksheet has a formula I cannot use a vba to find the end of the sheet, however I could use application count if row does not equal "", to count to the end of each row.

I have attached a test workbook for your review. I am not sure if I am explaining my issue correctly.

Thank you

[efernandes67]

Hi,

If I were you I woul rather work with tables instead of lists!

Paste the following in a module and try it:

Please Login or Register  to view this content.

Elio Fernandes

[katkins0429]

Hi I tried pasting this and running it however it gave me an error of Run time error 104 Application-defined or object-defined.

[efernandes67]

Open VB editor and run cpyDataFrom_Test_to_Master step-by-step pressing F8 to see where the error is.


See attached file.

[katkins0429]

Hi i got the macro to run but because I am using links to other worksheet it is only picking up the first tab and everything else is blank. After stepping throug the the code it seems as if first copy the formulas then convert it to paste values.

[katkins0429]

I have tried modifying my sheets and I am still getting some blank rows an not all rows are pulling in some will some will not. I do not know if is becasue there is a formula in each row and the code is only looking at A:A for blank instead of looking at all the columns in the array as blank. I am stumped.

[efernandes67]

I attached the file with the code and it is running!!! If your file is as the one you posted, I think it should work. If you want, post your actually file and I will take a look.

[AB33]

If you want a simple consolidation, try the attached.