+ Reply to Thread
Results 1 to 14 of 14

Copy column and paste to end of first column

  1. #1
    Registered User
    Join Date
    07-13-2013
    Location
    syracuse, ny
    MS-Off Ver
    Excel 2003
    Posts
    9

    Exclamation Copy column and paste to end of first column

    Hi everyone,

    I'm VERY new to vba and needed some help with a task.
    I want to take four columns (not always adjacent) and paste them below the first set of columns while also adding a column that will consist of the header of one of the columns I pasted.

    I don't think I can explain it very well, but but my Excel attachment shows what I have now (sheet: old) and what I want to have it look like at the end (Sheet: new).
    The number of people ("names") and the number of months across the top will always vary. But there will ALWAYS be 3 columns ("name", "team", "dept") before the month headers begin.

    The reason I want to do this is so I can make a pivot table that can filter by month and by name and by team. Any suggestions?

    Thanks in advance for the help!
    Attached Files Attached Files
    Last edited by ag123; 07-13-2013 at 05:15 PM.

  2. #2
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Copy column and paste to end of first column

    hi ag123, welcome to Excelforum, option, please check attachment, press "Run" button or run code "test"
    Attached Files Attached Files

  3. #3
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Copy column and paste to end of first column

    ag123,

    Welcome to the Excel Forum.

    With your raw data in worksheet old.

    Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

    1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
    2. Open your workbook
    3. Press the keys ALT + F11 to open the Visual Basic Editor
    4. Press the keys ALT + I to activate the Insert menu
    5. Press M to insert a Standard Module
    6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
    7. Press the keys ALT + Q to exit the Editor, and return to Excel
    8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

    Please Login or Register  to view this content.
    Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

    Then run the ReorgData macro.
    Last edited by stanleydgromjr; 07-13-2013 at 07:56 PM.
    Have a great day,
    Stan

    Windows 10, Excel 2007, on a PC.

    If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

  4. #4
    Registered User
    Join Date
    07-13-2013
    Location
    syracuse, ny
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Copy column and paste to end of first column

    Wow! Thank you both so much, it's almost perfect!

    I adapted stanleydgromjr's code to fill an existing sheet instead of creating a new one.

    Two things, if possible...
    1. I would prefer if the values under "feb" were placed directly under "jan" without multiple blank rows beneath them (even though the number of names will vary each time the code is run).

    2. I would prefer is the loop stopped after the last filled column (in the test case: "feb"). right now there are multiple rows beneath the data that have the the first 3 columns filled (i.e. name/ dept/ team) but no month or value because those are blank. Can we make it end after the last month that is present?

    Thank you SOOO So much already, it's a big helpa nd I can't wait to get this perfect


    My code so far is:

    Please Login or Register  to view this content.

  5. #5
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Copy column and paste to end of first column

    ag123,

    My original macro ReorgData worked correctly with your original attached workbook.

    If the macro does not work correctly with worksheet M) Avg Hrs- Month, then I will have to see the workbook?

    Please attach the workbook.

  6. #6
    Registered User
    Join Date
    07-13-2013
    Location
    syracuse, ny
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Copy column and paste to end of first column

    stanleydgromjr,

    Thanks for your quick reply. This is happening because the worksheet is filled with formulas to row 500 and column BZ that evaluate to "" if data from another worksheet is empty. So what I need to do is select all the cells that contain a value that is NOT "" (blank) and then do the function that you had posted above. How can I select only those cells to apply the above code to?

    Thanks again

  7. #7
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Copy column and paste to end of first column

    ag123,

    As I have already stated:

    If the macro does not work correctly with worksheet M) Avg Hrs- Month, then I will have to see the workbook?

    Please attach the workbook, with all supporting worksheets.


    If you are not able to attach the actual workbook with all supporting worksheets, then:

    In the Quick Reply box, just put the word BUMP. Then, click on the Post Quick Reply button, and someone else will assist you.

  8. #8
    Registered User
    Join Date
    07-13-2013
    Location
    syracuse, ny
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Copy column and paste to end of first column

    Unfortunately, I cannot post the real workbook due to proprietary info, but I recreated a small data set of what my problem looks like.
    Basically on Sheet: "M) Avg Hrs- Month it takes data from sheet: "M) Total Hrs- Month" and divides it by a specified number if the cell contains anything. the extra "" cells on M) Avg Hrs- Month Run through row 20 and column z i believe.


    THANKS!!!

    what i want to do.xlsm

  9. #9
    Registered User
    Join Date
    07-13-2013
    Location
    syracuse, ny
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Copy column and paste to end of first column

    I know this is ugly, but bear with me as I am MORE than a novice at vba...

    This is how I am selecting the range and then applying the loop. My only problem right now is that I need to do this for three different sets of worksheets and I cannot with the current code because the variable names are duplicated for each of the three (the three sections can be found with 'for mat/ 'for arg/ 'for nap and all the code below that section). How can I make the code run through the 3 sets of worksheets without having to go through and change all the variable names?


    THANKS AGAIN!!


    Please Login or Register  to view this content.

  10. #10
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Copy column and paste to end of first column

    ag123,

    Thanks for the workbook - that makes a big difference.

    Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

    Please Login or Register  to view this content.
    Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

    Then run the ReorgDataV2 mcro.

  11. #11
    Registered User
    Join Date
    07-13-2013
    Location
    syracuse, ny
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Copy column and paste to end of first column

    This question might be better solved in another thread. Please refer to:

    http://www.excelforum.com/excel-prog...80#post3322280


    Thank for your help

  12. #12
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Copy column and paste to end of first column

    ag123,

    You did mark this thread as SOLVED.

    Did you at least try my posted macro in reply #10?

  13. #13
    Registered User
    Join Date
    07-13-2013
    Location
    syracuse, ny
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Copy column and paste to end of first column

    Standgromjr,

    I posted a solution that I came up with in #9, your solution from #10 works as well, but it doesn't help me to repeat the process through ALL 3 sets of data (This part of my question was posted in #9 just minutes before you posted your solution in #10. I stated:: My only problem right now is that I need to do this for three different sets of worksheets and I cannot with the current code because the variable names are duplicated for each of the three (the three sections can be found with 'for mat/ 'for arg/ 'for nap and all the code below that section). How can I make the code run through the 3 sets of worksheets without having to go through and change all the variable names?

    I started a new thread because it is somewhat of a different question, but I guess I was wrong. Can you help with this as well??

    THANKS

  14. #14
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Copy column and paste to end of first column

    ag123,

    See your other thread for a solution:
    http://www.excelforum.com/excel-prog...html?p=3322861

+ 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. Replies: 3
    Last Post: 05-22-2013, 01:08 AM
  2. Vba - find next empty column, insert column, copy paste values & copy paste formulas
    By DoodlesMama in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-20-2012, 12:43 PM
  3. Replies: 3
    Last Post: 08-28-2012, 10:09 AM
  4. [SOLVED] Copy and Paste a specific Column to a Column that's heading matches a particular cell
    By Rexmond in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-17-2012, 12:29 AM
  5. Replies: 4
    Last Post: 08-31-2010, 10:48 PM

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