1

I am currently trying to copy 4 columns from my "Old" sheet to my "Current" sheet. This is the current code that I have:

Sub PasteData()

Sheet3.Range("J1:M252").Copy

Sheet1.Range("J1:M252").Insert Shift:=xlShiftToRight

Application.CutCopyMode = False

Sheet1.Range("J253:J462").Offset(ColumnOffset:=4).Insert Shift:=xlShiftToRight

'Inserts the comments from the "Old" sheet

End Sub

This copies and pastes the columns fine, but it is replacing the data that I currently have in the 4 columns for the "Current" file. I want to make it so that everything that is being replaced in those columns moves to the right. Thank you.

EDIT I've run into a problem. I pasted the columns in just fine, but there is still data that has not moved to the right. I plan to leave the columns J-M empty after row 252. I want to shift J253:Y282 (the highlighted cells) to the right from column N-AC. I inserted a picture to give a better description.

enter image description here

Community
  • 1
  • 1
user3688713
  • 41
  • 2
  • 3
  • 11
  • Try inserting the columns instead of copy and pasting them....try recording a macro of exactly what you want to happen. – user3271518 Jun 02 '14 at 15:08
  • `there is still data that has not moved to the right.` This is because the new data is not pasted in `1:252`.. We can easily shift the new block (`J253:Y282`) to start from `N` but the real matter is why and when we need to go to `253:282` instead of keep inserting stuff at `1:2521`. I can see that you are not copying pasting from 1:252 anymore, but instead you go below that. This has caused the messing up of `J253:Y282`. Do you have many rounds like these? I mean, are you going to copy paste data starting in `J283` eventually? In this case, could you describe more what the process is? – Ioannis Jun 02 '14 at 18:42
  • I am basically reformatting a report that I pull in to look like the formatted version. This involves moving a column to the left, removing zeros, copying columns J-M and putting in on the version that I have to format, etc. This process is to basically click one button to do everything instead of manually doing everything. I want columns J-M to end at row 252, but columns O-AC need to go all the way down to 282. This is going to be done weekly, so it will be used often. I don't know if this is clear enough so please ask questions, so I can clarify. – user3688713 Jun 02 '14 at 19:10
  • I see. Could you then try to replicate the below code for the `O:AC` case? Is it more complicated than changing the origin and destination ranges? I added an edit below that does this, should be pretty straightforward? – Ioannis Jun 02 '14 at 19:16
  • No it is not more complicated than changing the origin and destination. All I am trying to do is just to move those columns over to match up. I tried the code you wrote down: Sheet1.Range("J253").Offset(columnOffset:=4).Insert Shift:=xlShiftToRight and I got an error. – user3688713 Jun 02 '14 at 19:35
  • And I tried Sheet1.Range("J253").Insert Shift:=xlShiftToRight but that only moves it one column over. I need to get J253 to N253 – user3688713 Jun 02 '14 at 19:38
  • The `columnOffset` part works for me (dumps the block in `N253`). Are you sure that your destination sheet is `Sheet1`? Yes, the other one should not work, it sohuld be `N253` instead of `J253` – Ioannis Jun 02 '14 at 19:59
  • Yes it works for Sheet1. the columnoffset part works now but it moves 4 cells to the right from J253 and only moves it one cell over. I want it so that the entire column of J-M from cells 253 to 282 move four columns to the right. – user3688713 Jun 02 '14 at 20:22
  • I am afraid I do not understand.. Could you add some snapshots (one or two iterations) so that it is easier to explain? Perhaps you can replace the current snapshots with new ones, more interactive? Similarly, I added what the Offset part does in my answer below. – Ioannis Jun 02 '14 at 20:51
  • Hey I just edited the question and added my current code. I was wondering if you could check it out to see what's wrong. By the way, I really do appreciate the fact that you keep on commenting and helping me. I am very grateful for you help. Thanks. – user3688713 Jun 03 '14 at 13:25
  • 1
    Hey I actually just solved it! I ended up increasing the range of Sheet3.Range("J1:M252").Copy to Sheet3.Range("J1:M500").Copy – user3688713 Jun 03 '14 at 14:37

1 Answers1

2

This worked for me (tested):

Sub copyColumns()
    Dim rOrigin As Range, rDestination As Range
    Set rOrigin = Sheets("Old").Range("J1:M252")
    Set rDestination = Sheets("Current").Range("J1")
    rOrigin.Copy
    rDestination.Insert Shift:=xlShiftToRight
    Application.CutCopyMode = False
End Sub

or with fewer lines

Sub copyColumns()
    Sheets("Old").Range("J1:M252").Copy 
    Sheets("Current").Range("J1").Insert Shift:=xlShiftToRight
    Application.CutCopyMode = False
End Sub

I suggest using the sheet code name instead of its (actual) name, because if someone changes the sheet name the code will stop working.

Sheet code names are viewed in the project explorer and are Sheet1, Sheet2, etc. So Sheet1.Range is more robust than Sheets("Old").Range.

Also, Selections are considered bad practice and it is better that they are avoided.

I hope this helps!

EDIT

Following up your edit, if you now want to start from J253 and shift the inserted columns to N you can do Sheets("Current").Range("N253").Insert Shift:=xlShiftToRight or maybe

Sheets("Current").Range("J253").Offset(columnOffset:=4).Insert Shift:=xlShiftToRight

but the real question is when exactly you stop inserting at the same range of rows and go below, and how many row you want to insert each time. If you try automate a tedious copy paste, you might as well automate it all the way?!

Initially:

enter image description here

After one iteration:

enter image description here

After two iterations:

enter image description here

Community
  • 1
  • 1
Ioannis
  • 5,238
  • 2
  • 19
  • 31