Copy rows until a empty cell and paste them into a new sheet or workbook

[nkf531]

So I need help with this problem, I attached a file for an example. That I need VB formula to make my life easier than going through thousands of sheets. What I was hoping for help with is a formula that will go from Row 38 downards until empty Cell D is found (i think this would be easier to do than saying if A=Total Time report distribution) If doing until A = total time report distribution that would be even great as well. Then I would want it to copy and paste that into a new sheet or workbook.

All in all these are fake numbers i put in but What I really want is just the rows being copied until it meets the set criteria on to a new page or sheet which i can't figure out. Any other organizing i figure i can do a record macro to make it easier. Thanks!!!

[arlu1201]

What about the headers? Should they be copied to the new sheet/workbook?
If you do not want headers - use this code

Please Login or Register  to view this content.

If you need headers, just change the line - "For i = 38 to lrow" to "For i = 34 to lrow" or whatever row u want.

Hope this helps.

[nkf531]

Thanks! I was wondering if it's possible to put it on a completely new sheet. but the thing is i have multiple sheets per workbook that i want this to do to. is it possible to make it run through them all then compile them all onto one new worksheet? Thank you so much!

[arlu1201]

Yeah, its very much possible. Just show me a sample on how you want the data to be shown? Should the data from 1 sheet be separated from the other sheet by 1 row or some other differentiator, etc.? It will be good if you attach a sample file.

[nkf531]

Thanks so Much!! here's an example on sheet 3, The data is pretty much stored as per example on sheet one with each sheet varying in # of rows needed to be copied. Thank you so much! no divider would be fine but the thing is there are numerous sheets on each workbook. Is it also possible to add a code for the macro to move on to the next open workbook and do the same? or is that not possible?

[arlu1201]

Ok. I understood your requirement. So ideally, the line Total Time Report Distribution shouldnt appear in your copy paste, right?

Its possible to move on to the next open workbook - you can either specify the path to the macro directly or the macro can prompt you to open the file and then repeat the steps. The steps can be repeated provided the format is the same. If the format is different or there are different number of columns, we will need to code it below this code.

[nkf531]

Oh ok, the formula you showed me before just puts it on the next sheet instead of an entirely new one? and how would i go about programming this? it's a little difficult and i've been stuck on this for like a couple days already

[arlu1201]

Oh you mean you would like the contents to move to a new workbook and not the next sheet?

[nkf531]

yea that's what I was hoping for, all the information from the workbook which has multiple sheets, move into a new work book, because if i can have them in order it would let me put it into a pivot table or organize it better

[arlu1201]

This is the code to copy the data from all the worksheets in this workbook to a new workbook titled "Output File". You can change the name as required.

Please Login or Register  to view this content.

You also said you would like to open another workbook and then do the similar output. If the format is the same as this file, let me know and i can edit the code accordingly to add the required lines.

[nkf531]

Yea all the sheets would be in the same format

[arlu1201]

Should the data from the new workbook be consolidated into the current workbook or should it be consolidated into another blank workbook?

[nkf531]

Consolidated into a new worksheet in the same workbook. though if the data of all open workbooks can go into a new work book that'd be great but i know that'd be more difficult.

[nkf531]

Actually I have a question, the macro does work but only on one sheet. I was hoping to use this macro on multiple sheets or have them all add their data to another sheet. But since the macro is opening a new file, it can't keep creating a new file? Please let me know if there is a way to fix this??

[arlu1201]

You mean the above code (in post 10) does not consolidate the data from all your sheets into 1 sheet?

Consolidated into a new worksheet in the same workbook. though if the data of all open workbooks can go into a new work book that'd be great but i know that'd be more difficult.

In total, how many times should this code be repeated? In the sense, how many such workbooks you need the code to be repeated for?

[nkf531]

Well By workbooks i meant multiple excel files that may be open, but this is fine as well. I was hoping to make this a reusable macro that can be used on different files. lets say i have 50 files, I was hoping to use the macro to go to each one and add it to the File which contains all the data. I tried tweaking it but it only continues to draw data from the workbook with the macro in it.

So lets say I have file abc, abd, abe. and I have a sheet with the macro on it. I want to be able to use this macro on abc, abd, and abe. It doesn't even have to be at the same time, i just want the data to be consolidated to one location.

Though this was written really well! thanks!

[arlu1201]

Ok, i will tweak it for you so that you can just open the required workbook and it will do the task for u. Incase you need to run it more than once, you will need to run the macro from the beginning. Or just have a button and click it each time you require the output.

[nkf531]

Thanks that sounds great

[arlu1201]

One question - (possibly the last), so if i have the macro open workbook abc, it copies the data to new workbook 1. Then the macro opens workbook bce, should the data get copied to the same workbook 1? Or to a different workbook?

[nkf531]

To the same workbook but after. if it can be pasted with a blank row inbetween to differentiate that'd be a life saver

[arlu1201]

Should i work with the same file that you have uploaded in this thread?