+ Reply to Thread
Results 1 to 14 of 14

Trying to Pull data from Master Sheet onto New and Existing Sheets

  1. #1
    Registered User
    Join Date
    03-07-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    6

    Trying to Pull data from Master Sheet onto New and Existing Sheets

    Hi,

    I'm new here and need some help. I've not had very much experience with coding other than some basic tutorials and am looking for some pointers.

    Basically, I have a Master sheet that needs separating into individual sheets, most of which exist but every now and then when new data gets added a new sheet needs to be created. I have tried setting up queries several different ways and I get errors when trying to edit an existing one, and it's also much better if it's VBA because then it is just one file. Can anyone help with a macro or just some advice that would be helpful?

    I have attached an example spreadsheet that is a similar yet simplified layout.

    Thanks
    Fearghas
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

    Re: Trying to Pull data from Master Sheet onto New and Existing Sheets

    Hi fjlaff

    Welcome to the forum.

    Here's one solution that might work for you. To run the macro press Ctrl+Shift+Q. To See the macro press Alt+F8 / Step Into

    The macro sorts the data by name, copies the data then tries to find a sheet with the name. If it cannot find a sheet it opens one, and copies the data.

    Let me know how you get on.

    Regards
    Alastair
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    03-07-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Trying to Pull data from Master Sheet onto New and Existing Sheets

    Hi Alastair,

    Thanks very much for your help. Unfortunately when I tried using the macro on the actual data (which I cannot post as it is confidential), I got several errors. I have also tried this macro that I found yesterday on another site, which seems to be more what I need.

    Please Login or Register  to view this content.
    However, again coming up with some errors and I'm not sure why or how to fix all of them. I'm a complete novice at all of this and can't work out the mechanisms, which would give me a grasp good enough to solve my problem.

    Regards,
    Fearghas

  4. #4
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

    Re: Trying to Pull data from Master Sheet onto New and Existing Sheets

    Hi Fearghas

    I understand the need for confidentiality. Could you not send a cut down portion of your data with names replaced by Michael Mouse, Winnie Pooh etc? The macro works perfectly on the sample that you gave, so there must be an essential difference between sample and reality.

    Regards
    Alastair

  5. #5
    Registered User
    Join Date
    03-07-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Trying to Pull data from Master Sheet onto New and Existing Sheets

    Hi Alastair,

    The only differences are the Name is in column C and the Date in column D, and in total I have 69 rows of data but all of it is part of a table.

    Regards
    Fearghas

  6. #6
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

    Re: Trying to Pull data from Master Sheet onto New and Existing Sheets

    Hi Fearghas

    So I have changed the order and it works OK for me. If it fails for you, please let me know the specific details.

    One thing I did think of overnight - at the moment it overwrites anything that is in the individual sheets. Is this what you want, or would you prefer the macro to add to anything already on the sheet?

    Regards
    Alastair
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    03-07-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Trying to Pull data from Master Sheet onto New and Existing Sheets

    Hi Alastair,

    I just ran it and it says subscript out of range then highlights this
    Please Login or Register  to view this content.
    I'd prefer it to add new data to the existing ones, and is there any way of automating the copied data into tables?

    Regards
    Fearghas

  8. #8
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

    Re: Trying to Pull data from Master Sheet onto New and Existing Sheets

    Hi Ferghas

    Not sure what is happening - but the row is not that important. Try putting an apostrophe (') before the line and see if it runs.

    I can work on the adding tinight, and I will check out the tables (never done it before! )

    Regards
    Alastair

  9. #9
    Registered User
    Join Date
    03-07-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Trying to Pull data from Master Sheet onto New and Existing Sheets

    Hi Alastair,

    I tried the apostrophe and just got an error further down. The code I mentioned in the third post works now, except that the sheets are all in reverse alphabetical order, and I'm not sure where to add in other lines of basic functions that I have recorded macros for. Adding a table works fine for Ctrl+A then insert Table, but then if you try and run that macro on another sheet it only remembers the range of the original Ctrl+A, not the data on the new sheet.
    I've had a think and ultimately all I need is the addition of new data to existing sheets, which will in turn refresh pivot tables and charts. The adding of new sheets and tables is helpful but doesn't happen as often.

    Regards,
    Fearghas

  10. #10
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

    Re: Trying to Pull data from Master Sheet onto New and Existing Sheets

    Hi Ferghas

    Now I'm really confused! There must be something essentially different between the sample and reality.

    I had a look at the other macro, but it uses some concepts that I do not fully understand (= haven't a clue about ) so cannot amend it.

    Anyway to move on - here's a solution that will just stop if the sheet has not been set up. It will add to any data already on the sheet and will not post anything that has a "P" in column S of Master (and so posts a P once the named sheet has been populated)

    Let me know how you get on.

    Regards
    Alastair
    Attached Files Attached Files

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Trying to Pull data from Master Sheet onto New and Existing Sheets

    This should do
    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    03-07-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Trying to Pull data from Master Sheet onto New and Existing Sheets

    Hi jindon,

    Your macro worked but it was quite slow, is there a reason for that?

    It would be very helpful if either of you could add comments to your macro so I could then learn for myself the cogs and connections and work from there. Once I understand the moving parts I can take apart and rebuild the engine, so to speak.

    Thanks
    Fearghas

  13. #13
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Trying to Pull data from Master Sheet onto New and Existing Sheets

    Try this one then.
    Please Login or Register  to view this content.

  14. #14
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

    Re: Trying to Pull data from Master Sheet onto New and Existing Sheets

    Hi Ferghas

    Here's the file with comments. Hope you find them useful.

    Please note that I am not setting myself up as an example of good practice or anything like that - merely sharing routines that do work (well - most of the time )

    Regards
    Alastair
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Pull data from same cell from multiple sheets into master column
    By incubi in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 01-09-2013, 04:42 PM
  2. Replies: 2
    Last Post: 12-04-2012, 10:21 AM
  3. Copying cells from existing sheets to new row of master sheet in one workbook
    By volcaremos in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 11-06-2012, 03:53 PM
  4. Replies: 3
    Last Post: 09-27-2012, 09:49 AM
  5. Pull data from identical sheets onto master sheet
    By veritas1 in forum Excel General
    Replies: 2
    Last Post: 08-18-2011, 05:45 AM

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