Copy/Paste Loop for every other column

  • Greetings Ozgridians,

    I have a VBA problem that i have been trying to figure out for a few days. In Sheet1 i have data range from C5:AZ90. I want to copy a column and paste it into the column before it and clearcontents on the column that was originally copied. I have created a vba which does one column at a time and that is not dynamic at all.

    copy D5:D90 and paste it in C5:C90 and clear D5:D90

    copy f5:f90 and paste it in e5:e90 and clear f5:f90

    copy h5:h90 and paste it in g5:g90 and clear h5:h90

    And I want to do that for all the columns up to AZ

    Any help is much appreciated.

  • Sounds straight forward but how do I clearcontents of the copied cells in that range?

    Here's the code per your suggestion. It copies the columns into the prior columns but still has value in the copied column.

    Edited once, last by royUK: Add Code Tags ().

  • Just realized that your initial suggestion copies and pastes the values one column prior, however, that is not what I'm trying to do. I want to rollover the columns for D14:D85 (Ending) into C14:C85) (Beginning) and clearcontents in column D. And replicate (rollover ending to beginning) for the rest of the columns which have different items in row 12. See desired result in red below. My code does not accomplish that as of now.

  • Change the "lr" to the actual last used row if you don't want the code to find the last used row.

  • jolivanes,

    Thank you. This has been promising, how would this code change if you were to have a button for it in another sheet?

    Say there are 30 sheets and this macro is for a sheet name "data" but you have a button for user to click on a different sheet, say last sheet of the workbook.

  • Unfortunately it did not.

    Your code earlier had worked except it needs to define the sheet where the changes will be made in this the sheet is called "data" such that the user does not have to be on the 'data' sheet to run the macro, it can be run from a different sheet. If I was the sole user of this file, it wouldn't matter, but other users of the file want to just hit a button on one sheet that has all the different macros to do whatever they want it done on other sheets. Hope this makes sense. Thanks.

  • If you prefer to have 30 Buttons rather than 1, this should do that.

    Link all Buttons to this macro and you should be good to go.

    Put this code in a regular module (i.e. Module1)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!