Macro that consolidates data

[bowhunt24]

Hello all! Wondering if I could get a quick hand modifying a bit of code. I'm hoping it isn't too complicated and ends up being nice and straight forward.

I have three workbooks that need to have their data consolidated into one master workbook.

The master file name is: MASTER ver 2.0.xlsm
The three books that feed into it are: RTC, BELL and WOOD (all are .xlsm)
All of the files are located in a folder on the desktop labeled: SB TRACKER

The three workbooks being copied from may contain different amounts of data so I need help with two things.

1) making the macro identify only data (not blank rows) for the selection
2) when copying into the MASTER ver 2.0 it copies to the first available row.

Here is the macro i'm starting with, still very basic:

Please Login or Register  to view this content.

If anyone can help me out i would greatly appreciate it! Thank you!

Evan

[bowhunt24]

Yay, checking out some other posts I figured out how to get it to copy to the first available row. Here is the updated code I'm working with now.

Please Login or Register  to view this content.

Now the only thing I need to figure out is how to make the range it is copying from dynamic. Meaning there are 29 possible rows that could contain data but I only want rows with data in them copied. The entire possible range is B5:O33. I want to use Row C to determine if the row is copied or not. If there is any data in the row C (with an offset of 5) then that will determine if the row is copied. Right now the copy ranges are just where I had data. Hope this makes sense!

Evan

[royUK]

Here's some code that you can adapt

Please Login or Register  to view this content.

Note in your code you should avoid activating and/or selecting sheets, workbooks and ranges

[bowhunt24]

This looks great, thanks for the help Roy. Couple quick questions...Is this code meant to replace my entire Macro or just the selecting part? Also, I'm not good enough to actually write the macro so I just hit record and run through the steps, hence all the activating and selecting. Trying to get better and cleaner with my code though....

The last thing is I'm not entirely sure what to replace in this code to make it work with my files
The master sheet that is gathering all the data and where the macro lives is Master ver 2.0.xlsm and the three workbooks info is being gathered from are RTC, BELL and WOOD all .xlsm. The sheet name for all the workbooks is the same "OPEN LEADS".

Just a gentle nudge in the right direction would be great! Thanks again,

Evan

[royUK]

It's to replace all of your code. I'll have a look at the amendments you need tomorrow

[bowhunt24]

THANK YOU ROY!!! Any help is very much appreciated.

[royUK]

It would be better to move the three workbooks to copy from into a sub folder of SB TRACKER, then place the code I posted into a Standard module of the master workbook. Then run the code, make back up copies first.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste
To run the Excel VBA code:

Choose Tools | Macro | Macros
Select a macro in the list, and click the Run button

[bowhunt24]

Hey Roy, thanks again for all the help! One last question:

What do I need to change in the code you provided me so that it will work with my file names and folders? I can see where it looks like you would put in your path but I'm not sure on the format, tried a few times and couldn't get it to work. If you could just high-light anywhere I need to change the code to work with my particular files that would be awesome!

Thanks again,
Evan

[royUK]

This line defines where the data workbooks are, so change that

Please Login or Register  to view this content.

ThisWorkbook refers to the workbook containing the code, the data sheets are in a subfolder called Data

[bowhunt24]

Getting a run time error 1004 on this line...

I made it BOLD so you can see it in context with the code....

Please Login or Register  to view this content.

[royUK]

I can't tell without seein what you are working with. The code is fully tested so I can only think that you have a problem with the actual data

[bowhunt24]

I have attached the workbooks I am working with. The master and then one of the workbooks that is supposed to consolidate in. I also named the subfolder "data" so it matches up to your existing code.

Thanks again for all the help Roy, You are an Excel Jedi

[bowhunt24]

Hmmm....well apparently I claimed to attach the workbooks I'm working with but failed to actually do so. I'm awesome. Let's try this again, here are the workbooks in question. I attached the master that contains the code and one of the source books for reference.

Thanks,
Evan