+ Reply to Thread
Results 1 to 16 of 16

Pasting Specific Columns from Other Sheets into One

  1. #1
    Forum Contributor
    Join Date
    05-01-2018
    Location
    Maine
    MS-Off Ver
    2010
    Posts
    114

    Pasting Specific Columns from Other Sheets into One

    Hello,

    I am trying to create what I thought would be a simple VBA script to move data but I cannot seem to get it to work correctly. I have attached a mock up of what I am referencing. I included what columns need copied from (sheet 2 or 3) to (sheet 1) in the mock up.

    I am trying to grab data from specific columns in 2 sheets and paste that, in the same order, in the first blank row on the main sheet (sheet 1). Essentially this consists of 3 sheets. When a button is clicked on the main page (sheet 1), it copies the specific column data from its corresponding sheet (sheet 2) and pastes those columns in order on the main page (sheet 1) in the first blank row. A different button will do the same but copy from (sheet 3) to (sheet 1) instead.

    The problem im running into is having a code that runs 2 buttons corresponding to the other tabs and not pasting over itself on the main sheet. It is important that the data it pastes stays in order and it does not save over itself if you hit the button again. I do not have a table on this spreadsheet for other reasons.

    I tried to use macro recorder but it saves over the data already there and splits the columns randomly down my main sheet out of order. I also cannot find any similar posts because this needs to gather specific column data from other sheets and paste them into one 'main' consolidated sheet. So apologies if I missed this being answered elsewhere.

    Any help would be greatly appreciated and if I can find something that works it will make my life so much easier. I appreciate anyone who can help me.
    Attached Files Attached Files

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Pasting Specific Columns from Other Sheets into One

    Assign these 2 macros to the appropriate buttons:
    Please Login or Register  to view this content.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: Pasting Specific Columns from Other Sheets into One

    I'm not an expert, but I do like to know about your problem.

    Quote Originally Posted by Darkcloud617 View Post
    Hello,
    I am trying to grab data from specific columns in 2 sheets and paste that, in the same order
    I saw your "Secondary 1" sheet which contains a text "Column A to H" (from A2 to A66). Is there any meaning of that text ?

    in the first blank row on the main sheet (sheet 1)
    of what column ?

    When a button is clicked on the main page (sheet 1), it copies the specific column data
    from its corresponding sheet (sheet 2) and pastes those columns in order
    Do you mean that the column in "Secondary 1"
    which you want to copy is defined by you after you click the button ?

    1. You click the button
    2. a message show asking what column in "Secondary 1" you want to be copied
    3. you define the column (input)
    4. the macro runs, copy it then paste it into the A5 of your MAIN sheet.

    on the main page (sheet 1) in the first blank row.
    again, what column ?
    Column A ? and always paste it into column A but at the first blank row after the last row which has a value ?

    The problem im running into is having a code that runs 2 buttons corresponding
    to the other tabs and not pasting over itself on the main sheet.
    It is important that the data it pastes stays in order and it does not save over itself if you hit the button again.
    If it's always in Column A of the paste area in MAIN sheet, I think you can use [.End(xlup).offset(1,0)]
    With that command, the macro will put the copied column to the first blank row of the last row which has a value.
    So it won't paste to A5 (MAIN sheet) again.

    I tried to use macro recorder but it saves over the data already there
    Because after you record the macro,
    and when you run that recorded macro, it will repeat exactly the same what of you did before.
    So, you need to modify the macro you've just recorded it.


    I also cannot find any similar posts because this needs to gather
    specific column data from other sheets and paste them into one 'main' consolidated sheet.
    I think you need to program the macro to ask about what specific column that you want to be copied.
    So, after you hit the button, it will ask about it, you let it know, then it runs.

    Because I don't get exactly what you mean ... just guessing if it is something like this :

    1. You have "Secondary 1" sheet where there are a list of data in each column. Say column A B C D E F G.
    2. One day you want to copy a specific data which is a list in column G of "Secondary 1" sheet.
    3. The paste area is always column A in MAIN sheet but the next row which is empty.

    You run the macro (hit the button).
    It ask you what column that you want to be copied
    You tell the InputBox that it's column G of the "Secondary 1" sheet.
    The macro runs, copy all the list in column G of "Secondary 1" sheet
    and then paste it to a blank row in column A of "MAIN" sheet.
    (If this is your first time, in this case then cell A5 is the starting point of the paste cell).

    Assumed that your column G of "Secondary" sheet has data list for 10 rows,
    now your last blank row of column A of your "MAIN" sheet is A15.

    Next you hit the same button again, and this time you tell
    the InputBox that it's column C of the "Secondary 1" sheet
    The macro runs, and now you see the list in your MAIN sheet starting from cell A15.
    (where A5 to A14 is your data from column G of "Secondary 1" sheet)

    and so on.

    Is it something like that what you mean ?
    Last edited by karmapala; 11-13-2018 at 01:54 PM.

  4. #4
    Forum Contributor
    Join Date
    05-01-2018
    Location
    Maine
    MS-Off Ver
    2010
    Posts
    114

    Re: Pasting Specific Columns from Other Sheets into One

    Hello Mumps,

    Thank you so much for the code. This is almost exactly what I was needing but in a better code than I put together. The problems still seem to stem back to my original problems though. This does move the data but pastes one column under another instead of matching what it had in sheet 2 and sheet 3. Those columns should match up when its pasted in sheet 1 (the row stays the same). For some reason it also pastes the data in seemingly random blank rows further down my spreadsheet. Any idea why that would happen?

  5. #5
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Pasting Specific Columns from Other Sheets into One

    I'm not sure what you mean by:
    matching what it had in sheet 2 and sheet 3.
    Could you post a revised copy of your workbook which includes fourth sheet with a manual mock up that shows what you want the end result to look like after the macro is run?

  6. #6
    Forum Contributor
    Join Date
    05-01-2018
    Location
    Maine
    MS-Off Ver
    2010
    Posts
    114

    Re: Pasting Specific Columns from Other Sheets into One

    Hello Karmapala,

    Thank you for looking into my issue. I very much appreciate it. I included the questions below so hopefully this helps clarify it further. Thank you again for any assistance you provide.

    -I saw your "Secondary 1" sheet which contains a text "Column A to H" (from A2 to A66). Is there any meaning of that text?

    No this is just to show that multiple rows are being moved.

    -of what column ?

    These are noted in 'secondary 2' and 'secondary 3'. It is taking data from a column in 'secondary 2' or 'secondary 3' and moving it to a different column in 'Main'

    -Do you mean that the column in "Secondary 1"
    which you want to copy is defined by you after you click the button ?

    Not necessarily this should just run when you click the button. This will have pre-defined columns to paste in from 'secondary 2 & 3' to defined columns in 'Main'. These will always stay the same.

    -again, what column ? These are defined in 'Secondary 2 & 3". It will copy the data from those sheets and paste it into 'main'
    Column A ? and always paste it into column A but at the first blank row after the last row which has a value ?

    No, but there are set columns that they will always paste into

    -If it's always in Column A of the paste area in MAIN sheet, I think you can use [.End(xlup).offset(1,0)]
    With that command, the macro will put the copied column to the first blank row of the last row which has a value.
    So it won't paste to A5 (MAIN sheet) again.

    Honestly, I am not sure how to define a code like that into the script that I was using to copy this.

    -Because after you record the macro,
    and when you run that recorded macro, it will repeat exactly the same what of you did before.
    So, you need to modify the macro you've just recorded it. The code you mentioned above would work but I just used macro recorder to copy from 'Secondary 2 & 3' to 'Main'.

    I feel like adding something else to find the blank row and paste would be more suited for a code overhaul but I am not sure.

    Here is what it should do in a general sense.

    1. Someone clicks a button in 'Main' that is linked to 'Secondary 2'.
    2. It copies defined columns from 'Secondary 2' (these columns will not change)
    3. It pastes those specific columns in 'Main' in defined columns (these columns will not change)
    3a. It pastes the columns next to each other in 'Main' to keep the row integrity from 'secondary 2'
    3b. It ensures that it is pasting in the next blank row

    1. Someone clicks a button in 'Main' that is linked to 'Secondary 3'.
    2. It copies defined columns from 'Secondary 3' (these columns will not change)
    3. It pastes those specific columns in 'Main' in defined columns (these columns will not change)
    3a. It pastes the columns next to each other in 'Main' to keep the row integrity from 'secondary 3'
    3b. It ensures that it is pasting in the next blank row
    Last edited by jeffreybrown; 11-13-2018 at 02:57 PM. Reason: Removed full quotes!

  7. #7
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Pasting Specific Columns from Other Sheets into One

    Hello Darkcloud617,

    Please do not quote whole posts -- it's just clutter.
    HTH
    Regards, Jeff

  8. #8
    Forum Contributor
    Join Date
    05-01-2018
    Location
    Maine
    MS-Off Ver
    2010
    Posts
    114

    Re: Pasting Specific Columns from Other Sheets into One

    Quote Originally Posted by Mumps1 View Post
    Could you post a revised copy of your workbook which includes fourth sheet with a manual mock up that shows what you want the end result to look like after the macro is run?
    Hey Mumps,

    Yes I have attached a new mock up showing the end result. This just means that I am trying to preserve the row integrity.
    Attached Files Attached Files
    Last edited by Darkcloud617; 11-13-2018 at 03:12 PM. Reason: Removed clutter in quote

  9. #9
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: Pasting Specific Columns from Other Sheets into One

    Quote Originally Posted by Darkcloud617 View Post
    Here is what it should do in a general sense.

    1. Someone clicks a button in 'Main' that is linked to 'Secondary 2'.
    2. It copies defined columns from 'Secondary 2' (these columns will not change)
    3. It pastes those specific columns in 'Main' in defined columns (these columns will not change)
    3a. It pastes the columns next to each other in 'Main' to keep the row integrity from 'secondary 2'
    3b. It ensures that it is pasting in the next blank row
    I'm sorry that I didn't get what you mean before.
    Now I understand better, still now clear though.

    From your explanation above, I thought the code from Mumps1 will do for you, but it turned out it's not.

    This does move the data but pastes one column under another instead of matching what it had in sheet2 and sheet3
    The code from Mumps1 (I thought) is already matching what it had in sheet 2 and sheet 3.

    For example,
    in sheet2 column A ("Column A to H") ---> you want this column A to be in column H of the MAIN sheet.
    in sheet3 column D ("Column D to H") ---> you want this column D to be in column H of the MAIN sheet.

    So from Mumps1's code, if you first hit the button1 (intended for Sheet2)
    it will put the list in column A of Sheet 2 to column H of the Main sheet, starting at cell H5.

    Assuming that your column A of Sheet 2 has 10 rows of data....
    then the next blank row of column H of MAIN sheet is H15.

    Later, if you hit the button2 (intended for Sheet3)
    it will put the list of column D of Sheet 3 into column H of the MAIN sheet, starting at cell H15.

    But it seems that kind of result is not what you want ?
    Last edited by karmapala; 11-13-2018 at 03:20 PM.

  10. #10
    Forum Contributor
    Join Date
    05-01-2018
    Location
    Maine
    MS-Off Ver
    2010
    Posts
    114

    Re: Pasting Specific Columns from Other Sheets into One

    Right, its just a bit different. The rows in 'Secondary 1' and 'Secondary 2' are raw data that I am trying to move over into 'Main'. In my actual spreadsheet, I will update the data once its consolidated into 'Main'. Secondary 1 & 2 will not be worked from. These will just contain raw data that I need specific columns from so I can work on it from 'Main'. The rows still need to be complete in 'Main' for that reason.

    Hopefully that helps clarify my issue.
    Last edited by Darkcloud617; 11-13-2018 at 03:25 PM.

  11. #11
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: Pasting Specific Columns from Other Sheets into One

    After looking at your second file, I think that's what Mumps1 code gave you.

    To start from row5, I put "row helper".
    So, it something like this the way I try :

    Please Login or Register  to view this content.
    Last edited by karmapala; 11-13-2018 at 03:26 PM.

  12. #12
    Forum Contributor
    Join Date
    05-01-2018
    Location
    Maine
    MS-Off Ver
    2010
    Posts
    114

    Re: Pasting Specific Columns from Other Sheets into One

    This Worked!!! Thank you very much. Ive never used a row helper before... Just to clarify, this ensures that excel starts at the 5th cell?

  13. #13
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: Pasting Specific Columns from Other Sheets into One

    Quote Originally Posted by Darkcloud617 View Post
    Right, its just a bit different.
    The rows in 'Secondary 1' and 'Secondary 2' are raw data that I am trying to move over into 'Main'.
    In my actual spreadsheet, I will update the data once its consolidated into 'Main'.
    Secondary 1 & 2 will not be worked from.
    I see.
    So, it seems the two buttons at your MAIN will be used for one time only ?
    Please correct me if I'm wrong.

  14. #14
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Pasting Specific Columns from Other Sheets into One

    Try:
    Please Login or Register  to view this content.

  15. #15
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: Pasting Specific Columns from Other Sheets into One

    Quote Originally Posted by Darkcloud617 View Post
    This Worked!!! Thank you very much. Ive never used a row helper before...
    Just to clarify, this ensures that excel starts at the 5th cell?
    Yes it is, Darkcloud617. In a condition that all the rows after row4 is empty.

    The [.End(xlup)] code is to find the last row which has value.
    The row helper has "." value (row4 of the defined column).

    the [.offset(1,0)] code is telling that after it finds the last row which has value,
    it should move one row down, so it's row5.

    FYI, this is not telling the program to start at row5.
    But since (at the first you hit the button) there is no value/header in MAIN sheet,
    that's why it need the row helper so it knows of what you want, the cell as the starting point to be pasted.

    But the next time you hit the button again, of course it won't start to paste at row5 anymore,
    but to the last blank row of the defined column.

    The result from Mumps1 code with mine is the same. And I think, Mumps1 code is better than mine.

    Please remember, (my code) if there is a cell of the defined column after row4 which is not empty - it won't start at row5.
    Last edited by karmapala; 11-13-2018 at 04:06 PM.

  16. #16
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Pasting Specific Columns from Other Sheets into One

    @karmapala: Thank you for your input. The code I suggested doesn't need a helper row. The macro checks to see what the last used row is and if it is less than 5, it sets the last row to 5.

+ 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] Prevent a VBA wiping over specific columns when pasting into a new sheet
    By elleb in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-18-2017, 01:52 PM
  2. Copying a pasting specific columns
    By Throughstream in forum Excel General
    Replies: 1
    Last Post: 01-21-2017, 07:16 PM
  3. [SOLVED] Copying specific information from multiple sheets and pasting them into another
    By aeronaught in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-08-2016, 05:11 AM
  4. [SOLVED] Copying and Pasting Rows from specific sheets
    By Nuccio92 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-07-2016, 02:01 PM
  5. Copying Specific Data sets and pasting them in Columns of 10
    By novious1 in forum Excel Programming / VBA / Macros
    Replies: 26
    Last Post: 04-15-2015, 11:15 PM
  6. Pasting values in specific columns
    By greengirl in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-05-2014, 12:11 AM
  7. Pasting formulas in specific columns
    By jomili in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 02-08-2011, 12:12 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