-2

I would like to make a macro which will copy certain cells values marked by user in one sheet to another,but into different cells.

In one worksheet we have data in cells from A1 to D1,my goal is to paste them into second worksheet but to another cells (A2,A4,A6,A8 in my case)

And also when somebody copies for example more than four cells it will also paste them right next (B2,B4,B6,B8 and so on..)

I've managed something like this but it does not work

Sub sbCopyRangeToAnotherSheet()
'Copy the data
Sheets("Arkusz2").Range("A2:D2").Copy
Sheets("Arkusz1").Activate
'Select the target range
Range("A2", "A5", "A8", "A11").Select
'Paste in the target destination
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub
Matt
  • 19
  • 2
  • It won't let you paste a contigous range into a non-continguos range, so you need to create a loop which goes through copying/pasting individually – CFO Apr 27 '18 at 09:26
  • @CFO no you don't need a loop for that. See my answer. – Pᴇʜ Apr 27 '18 at 09:29
  • 2
    @Matt just a note on your question: *"it does not work"* is the worst description of an error. If you ask questions here please always tell which error you get and in which line. Also describe the behavior what happens instead of what you expect to happen. Then you have a good question that doesn't get down voted and it is much easier to give a good answer. – Pᴇʜ Apr 27 '18 at 09:32

1 Answers1

0

The correct syntax is

Sub sbCopyRangeToAnotherSheet()
    Sheets("Arkusz2").Range("A2:D2").Copy
    Sheets("Arkusz1").Range("A2,A5,A8,A11").PasteSpecial
    Application.CutCopyMode = False
End Sub

or you can even do it in one line:

Sub sbCopyRangeToAnotherSheet()
    Sheets("Arkusz2").Range("A2:D2").Copy Sheets("Arkusz1").Range("A2,A5,A8,A11")
End Sub

And I recommend to read: How to avoid using Select in Excel VBA.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • This code does nothing,no error no any sign,i'm using it with a button if it's helpful. – Matt Apr 27 '18 at 09:35
  • 1
    @Matt Sorry, see my edit. Changed it to `PasteSpecial`. Note that this copies in the background. It doesn't switch the active sheet. – Pᴇʜ Apr 27 '18 at 09:36
  • Hmm,ok but it does not paste the values,even if i switched to target worksheet – Matt Apr 27 '18 at 10:07
  • Are you sure you ran the code at all? I tested it this time and this must work as expected. Put the cursor into the sub and press F5 to run it or F8 to go through it step by step. – Pᴇʜ Apr 27 '18 at 10:53
  • When i used it with F8 it works but not as expected.My goal is to copy value from A2 to A2,from B2 to A4 from C2 to A6 and from D2 to A8.Now it copies values from A2 to A2,B2,C2,D2,from B2 to A4,B4,C4,D4..You know what i mean ? – Matt Apr 27 '18 at 11:15