+ Reply to Thread
Results 1 to 22 of 22

VBA to copy relevant data and populate a template sheet

  1. #1
    Forum Contributor
    Join Date
    05-01-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    822

    VBA to copy relevant data and populate a template sheet

    Morning,

    I need to use the following file and take data from both sheet1 & sheet2 to populate the position template tab;

    The data i require is;

    FUND (column A)
    USER IDENTIFER (Column N)
    TRADED MARKET VALUE (BASE) x % SPLIT (column G)

    These need to be copied from both sheet1 & sheet2 and added the following columns on the position template tab;

    FUND (column A) into COLUMN D
    USER IDENTIFER (Column N) into COLUMN B
    TRADED MARKET VALUE (BASE) x % SPLIT (column G) into COLUMN F & G


    Any help would be greatly appreciated
    Attached Files Attached Files
    Last edited by kenadams378; 02-06-2013 at 09:58 AM.

  2. #2
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA to copy relevant data and populate a template sheet

    Please Login or Register  to view this content.
    Last edited by AB33; 01-22-2013 at 07:02 AM.

  3. #3
    Forum Contributor
    Join Date
    05-01-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    822

    Re: VBA to copy relevant data and populate a template sheet

    Thanks, the code works but as the data contains formula's i just need it to copy over values?

  4. #4
    Forum Contributor
    Join Date
    05-01-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    822

    Re: VBA to copy relevant data and populate a template sheet

    ' Bump - no repsonse '

  5. #5
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA to copy relevant data and populate a template sheet

    I just got back to my PC

    Please Login or Register  to view this content.
    Last edited by AB33; 01-22-2013 at 10:55 AM.

  6. #6
    Forum Contributor
    Join Date
    05-01-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    822

    Re: VBA to copy relevant data and populate a template sheet

    You are a star. Works great! many Thanks!!!!!

    slightly off topic, when i run the code for some reason on my front sheet it makes column A & B wider than normal??? any ideas?

  7. #7
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA to copy relevant data and populate a template sheet

    Ken,
    You are right!
    This line is the culrpit
    Please Login or Register  to view this content.
    Comment it, or delete it, or if you could choose which columns you wish to adjust. I was too lazy and did all

  8. #8
    Forum Contributor
    Join Date
    05-01-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    822

    Re: VBA to copy relevant data and populate a template sheet

    Thanks, i don't need any autofit on the data so will just remove it.

    Many thanks again,

    Following this code i have identified a number of new lines of data that as part of the process don't have an identifier (column C) on postion template, is there a way to highlight any 0 or #N/A as part of this data and copy these to a new sheet called 'New Portfolios)??

  9. #9
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA to copy relevant data and populate a template sheet

    Ken,
    It is possible, but you need to show me your data. Currently, Column C on position template is blank, so where are the data coming from, or you mean column B which has identifier header on position template .

  10. #10
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA to copy relevant data and populate a template sheet

    Try this one and if you are happy with it, I can incorporate in the origianal code as one

    Please Login or Register  to view this content.

  11. #11
    Forum Contributor
    Join Date
    05-01-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    822

    Re: VBA to copy relevant data and populate a template sheet

    The concept of that code works great.

    I think i need to alter it though, i need to identify the data as part of the final outputs of the data on sheet1 & sheet2 from column N.

    The data that is copied over to the new portfolios tab should be the line of data column A to G.

    Hope this makes sense

  12. #12
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA to copy relevant data and populate a template sheet

    Ken,
    Before this code, column N of sheets 1 and 2(User Identifier column) were copied in to position template of column B. So, you want to copy column A-G of both sheets in to new portfolios. If so, what do you want to do with the 0 and N/A? Or how do you want to change the first code to accommodate your latest request? I am not sure. Please explain

  13. #13
    Forum Contributor
    Join Date
    05-01-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    822

    Re: VBA to copy relevant data and populate a template sheet

    Searching for N/A & 0 is the identification of new portfolio's for me to be able to map these somewhere i need to core data of these new portfolio's.

    As part of the code, search for #N/A & 0 within sheet1 & sheet2 then copy columns A:G into the new portfolio sheet.

  14. #14
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA to copy relevant data and populate a template sheet

    Okay,
    Do you want to search the whole sheet, or just column N only?

  15. #15
    Forum Contributor
    Join Date
    05-01-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    822

    Re: VBA to copy relevant data and populate a template sheet

    Just column N of both sheet1 & sheet2.

  16. #16
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA to copy relevant data and populate a template sheet

    I do not think you have N/A in other columns, so assuming only column N to search and copy range A-G.

    Please Login or Register  to view this content.

  17. #17
    Forum Contributor
    Join Date
    05-01-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    822

    Re: VBA to copy relevant data and populate a template sheet

    Thanks, i get the following message when i run the code
    Attached Files Attached Files

  18. #18
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA to copy relevant data and populate a template sheet

    Well, I do not know, but it may be a linked formula you have, so I have amended the code to copy values only

    Please Login or Register  to view this content.

  19. #19
    Forum Contributor
    Join Date
    05-01-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    822

    Re: VBA to copy relevant data and populate a template sheet

    You are a star!!

    Thank you so much for your help on this...

    Hope to work with you again in future

  20. #20
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA to copy relevant data and populate a template sheet

    You are welcome!

  21. #21
    Forum Contributor
    Join Date
    05-01-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    822

    Re: VBA to copy relevant data and populate a template sheet

    AB33,

    just wanted to pose a question, is it possible to retrieve different columns of data in no particular order of can i only return columns next to each other?

  22. #22
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA to copy relevant data and populate a template sheet

    Ken,
    This thread has been closed not only once but twice. Please re-start a new thread.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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