Copying ranges of cells from different worksheets in a workbook based on an if then

**stinkybottom** · 06-12-2012, 04:09 PM

I hope I am in the correct spot. If I am not could you point me in the right direction? Thank you.

This is my first post.

I have a project where I need to scan printed doucments in to a pdf then convert those in to excel speedsheets.

I found a program tha does a great job over all. It is caputring the data I need except for a few errors.

I would like to copy ranges of data based on a date being in a particular cell. For example, on one worksheet the date is listed in B5, on another B9.
If that cell contains the date of B5 I want to copy B6 to M29, if the date is in B10 I want to copy B10 to M33. I have attached a sample file. Sheets 1 and 4 contain example of what I just described along with color coding so you can see i easier.

Once they are copied I want to paste the into either one sheet or multiple sheet. I would perfer one sheet.

If you have questions for me please ask. I am a told **** at macros and such.

Example Workbook.xlsx

**JBeaucaire** · 06-12-2012, 04:26 PM

The only thing missing from your example is how you'd like the consolidated one-sheet report to look like? Using just sheets1 and 4, how would the merged report look? Would you want the "date" from each page on the copied rows, too?

**JBeaucaire** · 06-12-2012, 04:45 PM

Give this a try:

Please Login or Register  to view this content.

How/Where to install the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook

The macro is installed and ready to use. Press Alt-F8 and select it from the macro list.

**stinkybottom** · 06-12-2012, 05:17 PM

Thank you both for the replies.

Basically I want it to automatically look at each sheet, pull the data out and place it in one big sheet. Does that make sense? For example:

Date, column 1, column 2, and so on.

I gave two sheets as examples to show how the data is the same on each page but the data does not start in the same cells. I would like it to it for every sheet in the workbook with the number of sheet around 30.

**JBeaucaire** · 06-12-2012, 05:41 PM

Both? Just you and me here....

How did the macro work for you?

**stinkybottom** · 06-12-2012, 05:44 PM

Originally Posted by JBeaucaire

Both? Just you and me here....

How did the macro work for you?

Oops. I mean both of your replies. Long day

I am at home now but I will try it at work any reply. I do appreciate your reply.

**JBeaucaire** · 06-12-2012, 06:37 PM

you posted a sample workbook, so d/l it and try out the macro.

**stinkybottom** · 06-13-2012, 06:55 AM

You are a rocket scientist! It appears to work very well. You do not know how happy you have made me. Thank you. I will be running it today more and I will report back. Thank you again.

**JBeaucaire** · 06-13-2012, 11:13 AM

If that takes care of your need, please select Thread Tools from menu above and set this topic to SOLVED.

**stinkybottom** · 06-13-2012, 12:36 PM

Originally Posted by JBeaucaire

If that takes care of your need, please select Thread Tools from menu above and set this topic to SOLVED.

I have been playing around with it at work and I have run in to a few issues.

I wanted to expand the macro's ability to read sheets so I added three more "elseif". One pointing to a date cell found in A10, A30, and A31. When I run it the output sheet columns D,G,I,K, and M are incorrect for the new blocks of data I am consolidating. The original two sheets that point to B5 and B9 are 100% correct.

Below is my macro based on yours. I am also attaching another sample workbook. If you run the macro I have below it is easy to see what I am talking about.

Thanks

Copy of 001_20120613093426452.xls

Please Login or Register  to view this content.

**JBeaucaire** · 06-13-2012, 02:50 PM

As per forum rules, EDIT the post above and either add code tags around the code (see my post #3 above as an example) or simply remove it.

**stinkybottom** · 06-13-2012, 04:18 PM

Ok done. I am sorry about that

**JBeaucaire** · 06-13-2012, 04:46 PM

Each section has edits in the RESIZE commands, too. Be sure to read up on what that does.

Please Login or Register  to view this content.

**stinkybottom** · 06-13-2012, 09:19 PM

Thank you again. I did as you suggested and I read a little about the resize topic. Not many complete sources but I admit I have just gotten home fom work so I will look more later. Your help was amazing. I wish you the best, and I hope my project goes well. I have around 1500 sheets to take care of but I am off on the right track.

Be safe and well.

Copying ranges of cells from different worksheets in a workbook based on an if then

LinkBack

Thread Tools

Rate This Thread

Display

Copying ranges of cells from different worksheets in a workbook based on an if then

Re: Copying ranges of cells from different worksheets in a workbook based on an if then

Re: Copying ranges of cells from different worksheets in a workbook based on an if then

Re: Copying ranges of cells from different worksheets in a workbook based on an if then

Re: Copying ranges of cells from different worksheets in a workbook based on an if then

Re: Copying ranges of cells from different worksheets in a workbook based on an if then

Re: Copying ranges of cells from different worksheets in a workbook based on an if then

Re: Copying ranges of cells from different worksheets in a workbook based on an if then

Re: Copying ranges of cells from different worksheets in a workbook based on an if then

Re: Copying ranges of cells from different worksheets in a workbook based on an if then

Re: Copying ranges of cells from different worksheets in a workbook based on an if then

Re: Copying ranges of cells from different worksheets in a workbook based on an if then

Re: Copying ranges of cells from different worksheets in a workbook based on an if then

Re: Copying ranges of cells from different worksheets in a workbook based on an if then

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions