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.

Sub Copy()


Dim sh As Worksheet
Set sh = ThisWorkbook.Worksheets("Data")


sh.Range("D14:AZ85").Copy Destination:=sh.Range("C14")
Application.CutCopyMode = False


End Sub

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.

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.