Copy and paste non-adjacent cells

[robertdseals]

Hello,
I want to copy cells A3-A8 and A10-A14 and A16-A19. Then I want to paste them in cells F3-F8 and F10-F14 and F16-F19. I don't want to copy A3-A8 and paste it into F3-F8, then copy A10-A14 and past it into F10-F14...I want to copy all at once and paste all at once. And, my paste will be on a different tab (or maybe even a different worksheet) so I don't care if the copy is one macro and the paste is a second.

Right now, I can copy A3-A8 and A10-A14 and A16-A19. But when I go to paste it, it pastes into F3-F17.


Thoughts?

 

[alansidman]

Option Explicit


Sub cpyPst()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    s1.Range("A3:A8").Copy s2.Range("F3")
    s1.Range("A10:A14").Copy s2.Range("F10")
    s1.Range("A16:A19").Copy s2.Range("F16")
End Sub

 

[robertdseals]

Almost, but this still copies then pastes then copies then pastes....I need to do all the copy first, then all the paste.

 

[alansidman]

Apologies. I don't have an alternate solution for you. When I attempted to use the Union function it did not get expected results.

Question: What difference does it make if this does not paste all at once? It might help in solving if there was a rationale explanation.

 

[robertdseals]

Right now, I have to copy from one specific column in one spreadsheet, and copy it over to another specific column in another spreadsheet. I want to use a "relative reference" so it will copy down from whichever column my cursor is in. In addition, the rows between the numbers I have contain formulas so I don't want to copy the entire row and paste it. If I can copy all the numbers I need from one sheet (non-adjacent cells) and paste them into the corresponding locations on the new sheet, I wouldn't have to keep going back and forth to copy and paste. I hope that makes some sense. Thanks for your assistance.

 

[alansidman]

The only way I see this would be to copy the entire range and paste it to the new location. Then run a loop on the new range using this type of code to determine if there is a formula and then clearing the contents of that cell if true.

if range("F" & i).HasFormula = True then
range("F"& i).clearcontents

 

[robertdseals]

hummmm....that might work! Thanks.

 

[alansidman]

You are welcome. Post back if an issue arises. Good Luck.

 

[robertdseals]

That worked (with some minor changes).
Sub Macro5()
MyValue = InputBox("what column", "what col")
For dd = 1 To 1000
If Range(MyValue & dd).Offset(0, -1).HasFormula = True Then
Range(MyValue & dd).Offset(0, -1).Copy Range(MyValue & dd)
End If
Next dd


End Sub