Multiple Columns Copied - VBA

[mattbarb]

Hi,

Im trying to copy over a number of columns to other columns using a VBA. I tried adapting a script i found but it's not working, any help would be really appreciated:

Sub PasteSpecial_ValuesOnly()
'PURPOSE: How To Paste Values Only With PasteSpecial
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault
  
   
'Copy A Range of Data
  Worksheets("Plan").Range("J16:J37").Copy
  Worksheets("Plan").Range("P16:P37").Copy
  
'PasteSpecial Values Only
  Worksheets("Plan").Range("M16:M37").PasteSpecial Paste:=xlPasteValues
  Worksheets("Plan").Range("S16:S37").PasteSpecial Paste:=xlPasteValues
  
'Clear Clipboard (removes "marching ants" around your original data set)
  Application.CutCopyMode = False
  
End Sub

Many thanks!

 

[Fluff]

How about

Sub mattbarb()
   With Worksheets("Plan")
      .Range("M16:M37").Value = .Range("J16:J37").Value
      .Range("S16:S37").Value = .Range("P16:P37").Value
   End With
End Sub

 

[Joe4]

Note that what you did wrong originally is that you need to complete the first copy before you start the second!
It cannot hold two different values in the clipboard at the same time, and remember what order that you want them in.

But Fluff's code is more efficient than using that method.

 

[mattbarb]

Hi,

Many thanks for helping. When i use this it just seems to remove the values and doesn't copy and paste, am I doing something wrong?

How about

Sub mattbarb()
   With Worksheets("Plan")
      .Range("M16:M37").Value = .Range("J16:J37").Value
      .Range("S16:S37").Value = .Range("P16:P37").Value
   End With
End Sub

 

[Fluff]

That should copy the values from J16:J37 to M16:M37 and from P16:P37 to S16:S37
Is that what you wanted?

 

[mattbarb]

|Hi, yes thats right, but doesn't seem to work. it just seems to remove values.

That should copy the values from J16:J37 to M16:M37 and from P16:P37 to S16:S37
Is that what you wanted?

 

[Fluff]

Which values are being removed?

 

[mattbarb]

J16:J37 and M16:M37 are removed. And nothing is pasted. Thanks

 

[Fluff]

The code from post#2 does not remove the values from J16:J37
It will remove the values from M16:M37 if J16:J37 is empty
Have you made any changes to the code I posted?

 

[mattbarb]

The code from post#2 does not remove the values from J16:J37
It will remove the values from M16:M37 if J16:J37 is empty
Have you made any changes to the code I posted?

Thanks, all working now!