Paste data from source sheet to data sheet using 1st available row within a named range.

Hi Excel Forum

This is my first post and also my first time delving in to Excel VBA... please be patient.

I have a multiple data sources from multiple sheets that all reside in 3 Named ranges per sheet. With the use of VBA, I would like to copy these separate named ranges (3 of them per source sheet if they have a value) and paste them into the Master sheets 3 Named Ranges. With there being multiple source data sheets, I would like to avoid over write any existing data that may have already been copied from the source sheets to the master named ranges but at the same time use any blank cells. In essence filling up the master sheet with data from all the source sheets(about 11 of them).

I have searched and tried to implement this from VBA code found on the Web but always seem to get stuck with the copying to the Named ranges in the master sheet... I have not been able to resolve copying data to the 1st available row(has no data) within the 3 named ranges on the Master. At this point If I can get this to work for a single source sheet to the master with no over written data I should be able to get this correct for the rest as I will assign a macro button per source sheet.

Thanks for any hints or advice.

Could you provide example workbook(s) that mimic what you would like to achieve?

Thanks!

Thank you for the quick response... i have uploaded a sample file.

When you say different sources do you mean the information is in different workbooks? Or is all the information on one workbook but within different worksheets?

Hi rvasquez

All the sources are in a single workbook but spread across multiple sheets. In the sample sheet all the data will come from the source sheets (1-3) and copied to the master sheet. Hope that helps.

Thanks

Hello there attached is your example worksheet update to include a macro that I believe accomplishes what you are looking for. To run the macro. Press Alt+F8 on your keyboard and select the test macro and then select the run option.

Please note that this code assumes that your Master worksheet is the first worksheet in your workbook.
It also assumes that the rest of the worksheet in your workbook are worksheets you wanted copied into the Master worksheet.


To insert this code

1. Press Alt+F8 on your keyboard
2. Clear the macro name field
3. Type the following in the macro name field AddToMaster
4. Select the Create option
5. In between the Sub AddToMaster() and End Sub copy and paste the following code, anything that appears in green is a comment meant to help you understand

Please Login or Register  to view this content.

7. Close out of VBA and then press Alt+F8 again
8. Select the AddToMaster macro and select Run.

Let me know if this works for you!

Thanks!

Thanks rvasquez, I will revert shortly with feedback... thank you once again.

Hello rvasquez

My first question would be how do I specify which sheets will be used in the macro? The reason for this is have other sheets that contains info that does not need to be copied into the master.

Secondly, I see that you have specified as an example to " 'loop through cells in column A from row 2 to the last row ", collecting data but how do I limit this to a specified range after the tables in sheets 2 - 3 as I will have data further on that does not require copying to the Master sheet.

Thanks

Hello there,

What worksheets do you want included and what row do you want the code to stop at?

Thanks!

Hello rvasquez

I would like to exclude and/ or include sheets by name if possible. Can you please stop the code at row 15 of the source sheets and is it possible to start at a certain cell. I tried to run your code on Excel 2011 but unfortunately it through out a runtime error 5. After clicking debug the following was highlighted:

Formula:

Please Login or Register  to view this content.

The error message stated "Invalid procedure call or argument"

Thanks for your help, very much appreciated.

Hello there,

Try updating your code to the following:
I've highlighted in red the changes, you'll need to change the red "Worksheet you wish to exclude" lines with the worksheet names you don't wish to include in the process.

Please Login or Register  to view this content.

Hopefully by excluding the worksheets you don't want included this will block the error you are getting. Let me know if it doesn't

Hi rvasquez

I'm not what's happened but I'm now receiving an error in office 2010 (32 bit & 64bit) with the following error message:

Run-time Error 13: Type mismatch.

The following line of code was highlighted:

Formula:

Please Login or Register  to view this content.

At first I thought I should add sheet names to the exclude list, still the same result.

Thanks

Was it working prior to you saving it? Did the attached workbook work or did it throw the same error?

Hello rvasquez

After many many hours of working through your code I'm unfortunately no better off. I've attached a revised spreadsheet to mimic what I would like to achieve using your code. At this point nothing seems to be happening when I run the macro that I have inserted and I have exhausted all avenues to resolve it.

Thanks for your help once again.

Alrighty,

Let's try this one, if you notice I've made a copied of the Quote worksheet and inserted it so that it is the first worksheet in the workbook. Enable macro and then click the Run Report and the information from the Angola, Botswana and Ghana worksheet will populate on the worksheet "Quote (2)". If you select clear report it will clear the worksheet and return it to it's original state.

To view the code behind the scenes press Alt+F11 to bring up Visual basic and the code is located in Module1. Anything that appears in green is a comment meant to help you understand.

Thanks!

Hello rvasquez

Thank you so much for your efforts, this is exactly what I need.

Cheers for now...

Great! Glad I could help!