+ Reply to Thread
Results 1 to 19 of 19

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

  1. #1
    Registered User
    Join Date
    02-22-2010
    Location
    SA
    MS-Off Ver
    Excel 2007
    Posts
    15

    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.

  2. #2
    Valued Forum Contributor
    Join Date
    03-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    1,093

    Re: Paste data from source sheet to data sheet using 1st available row within a named rang

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

    Thanks!

  3. #3
    Registered User
    Join Date
    02-22-2010
    Location
    SA
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Paste data from source sheet to data sheet using 1st available row within a named rang

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

  4. #4
    Valued Forum Contributor
    Join Date
    03-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    1,093

    Re: Paste data from source sheet to data sheet using 1st available row within a named rang

    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?

  5. #5
    Registered User
    Join Date
    02-22-2010
    Location
    SA
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Paste data from source sheet to data sheet using 1st available row within a named rang

    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

  6. #6
    Valued Forum Contributor
    Join Date
    03-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    1,093

    Re: Paste data from source sheet to data sheet using 1st available row within a named rang

    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!
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    02-22-2010
    Location
    SA
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Paste data from source sheet to data sheet using 1st available row within a named rang

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

  8. #8
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Paste data from source sheet to data sheet using 1st available row within a named rang

    TIP OF THE DAY - when using a specific column to "spot the last row of data", remember there a different numbers of possible rows in each version of Excel. To make the search work in any version of Excel, I'd recommend this correction:

    Please Login or Register  to view this content.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  9. #9
    Registered User
    Join Date
    02-22-2010
    Location
    SA
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Paste data from source sheet to data sheet using 1st available row within a named rang

    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

  10. #10
    Valued Forum Contributor
    Join Date
    03-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    1,093

    Re: Paste data from source sheet to data sheet using 1st available row within a named rang

    Hello there,

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

    Thanks!

  11. #11
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Paste data from source sheet to data sheet using 1st available row within a named rang

    Expand on this to exclude specifically named sheets:

    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    02-22-2010
    Location
    SA
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Paste data from source sheet to data sheet using 1st available row within a named rang

    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: copy to clipboard
    Please Login or Register  to view this content.


    The error message stated "Invalid procedure call or argument"

    Thanks for your help, very much appreciated.

  13. #13
    Valued Forum Contributor
    Join Date
    03-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    1,093

    Re: Paste data from source sheet to data sheet using 1st available row within a named rang

    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

  14. #14
    Registered User
    Join Date
    02-22-2010
    Location
    SA
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Paste data from source sheet to data sheet using 1st available row within a named rang

    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: copy to clipboard
    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

  15. #15
    Valued Forum Contributor
    Join Date
    03-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    1,093

    Re: Paste data from source sheet to data sheet using 1st available row within a named rang

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

  16. #16
    Registered User
    Join Date
    02-22-2010
    Location
    SA
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Paste data from source sheet to data sheet using 1st available row within a named rang

    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.
    Attached Files Attached Files

  17. #17
    Valued Forum Contributor
    Join Date
    03-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    1,093

    Re: Paste data from source sheet to data sheet using 1st available row within a named rang

    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!
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    02-22-2010
    Location
    SA
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Paste data from source sheet to data sheet using 1st available row within a named rang

    Hello rvasquez

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

    Cheers for now...

  19. #19
    Valued Forum Contributor
    Join Date
    03-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    1,093

    Re: Paste data from source sheet to data sheet using 1st available row within a named rang

    Great! Glad I could help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1