+ Reply to Thread
Results 1 to 16 of 16

complicated macro to copy original data and represent in different format in NEW DATA

  1. #1
    Registered User
    Join Date
    04-19-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    33

    complicated macro to copy original data and represent in different format in NEW DATA

    Hi all,

    I think this might be a brain buster. Its very manual for me to do this and I hope of the experts on here can help.

    See sample workbook attached and what I am trying to achieve is in the "NEW DATA" tab.

    You can see the main changes are in the presentation.

    From column O onwards of original data the macro has to look down the column and at the first non-blank cell, copy row the new data page under the heading which corresponds with that column. So every column heading will be presented vertically now on NEW DATA page. Then each row where there is a number in that column will then be copied to NEWDATA under the vertical heading.

    Not sure I am explaining it well. You will see from the data whats changing in the new data page.

    Thanks for your help in advance.
    Attached Files Attached Files

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

    Re: complicated macro to copy original data and represent in different format in NEW DATA

    Try
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    04-19-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: complicated macro to copy original data and represent in different format in NEW DATA

    Quote Originally Posted by jindon View Post
    Try
    Please Login or Register  to view this content.
    Very fast! Thank you

    Is there a way we can specify the header row and the column range for the headings?

    My headings are on row 11 and the range is column O to column BX

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

    Re: complicated macro to copy original data and represent in different format in NEW DATA

    Then I need to see your actual sheet layouts and then result in a workbook.

  5. #5
    Registered User
    Join Date
    04-19-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: complicated macro to copy original data and represent in different format in NEW DATA

    Quote Originally Posted by jindon View Post
    Then I need to see your actual sheet layouts and then result in a workbook.
    Its exactly like the sample workbook except the data starts on row 12 - headings in row 11 and then we only want to look down the columns for non 0 values from columns O - BX. Columns O - BX are the names of people as you see in the sample workbook.

    The macro has to filter based on Column O first for all the <> 0 values and then copy to new data - then go to M and do the same and then N etc but from row 12 till row 174.

    It would be good to put these variables in the macro.

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

    Re: complicated macro to copy original data and represent in different format in NEW DATA

    1)
    Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

    For normal conversational replies, try using the QUICK REPLY box below.
    2)
    Quote Originally Posted by swilson2006 View Post
    Its exactly like the sample workbook except the data starts on row 12 - headings in row 11.
    Then change to
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    04-19-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: complicated macro to copy original data and represent in different format in NEW DATA

    Jindon

    Thanks for looking into this.

    Sorry that didn't work as planned as it seems to keep the newdata with 64 columns of amounts instead of just 1 column. It seemed to replicate the column headers. But I want every column header to have a section of its on so if we have 62 columns we will only have 1 column in the new data layout.

    Please see the attached excel which is almost an exact replicate but since there are 64 columns I would not want to confuse everyone so I put a sample so you can get the idea.

    Hope you can help, thanks for your time.
    Attached Files Attached Files

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

    Re: complicated macro to copy original data and represent in different format in NEW DATA

    It not the same at all.
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    04-19-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: complicated macro to copy original data and represent in different format in NEW DATA

    That was pretty awesome! THANK YOU!

    One small issue is that the VAT Column should also get a section of its own.

    Would you be able to add a total at the end of every "ACCOUNT" section?

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

    Re: complicated macro to copy original data and represent in different format in NEW DATA

    Then I need to see your EXACT desired result in a workbook.

  11. #11
    Registered User
    Join Date
    04-19-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: complicated macro to copy original data and represent in different format in NEW DATA

    Hello

    I have attached the new format here. I was hoping you could also do the total and the table on the side in the new data page.

    Also, see the VAT payable. That column should also appear in a grid.

    Thanks alot
    Attached Files Attached Files

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

    Re: complicated macro to copy original data and represent in different format in NEW DATA

    Change to
    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    04-19-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: complicated macro to copy original data and represent in different format in NEW DATA

    Hi, sorry now a different thing went wrong. All works except this time it did not add the "ACCOUNT" to the rows. I couldnt work out what to cut and add from the previous code which did add the account code from the column headings to each row of data.

    I think thats all with it, works brilliantly, thank you so much for your help.

    Thanks very much

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

    Re: complicated macro to copy original data and represent in different format in NEW DATA

    Ahhh,. Insert one line
    Please Login or Register  to view this content.

  15. #15
    Registered User
    Join Date
    04-19-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: complicated macro to copy original data and represent in different format in NEW DATA

    Jindon - thanks so much, this all worked out well. I really appreciate you sharing your skills.

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

    Re: complicated macro to copy original data and represent in different format in NEW DATA

    You are welcome and thanks for the rep.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

+ 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] Vlookup and copy data back into original data
    By Sazza in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 08-23-2017, 11:24 AM
  2. Macro multiply rows and copy exact same data from original row
    By AlexAXL in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-02-2016, 06:46 AM
  3. Replies: 1
    Last Post: 11-26-2014, 04:28 AM
  4. [SOLVED] Macro to copy cell width to new sheets (ie copy original sheet format)
    By teenyjem in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-20-2013, 07:28 AM
  5. Macro to Copy Values, Insert Row, Paste in Another, Delete Original Data
    By JimPDX in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-23-2013, 05:35 AM
  6. Macro to copy Dyanmic lists then copy sheet and clear the original data
    By twiggywales in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-30-2012, 01:06 PM
  7. Replies: 2
    Last Post: 07-18-2012, 09:11 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