Issue with multiple paste

[cairo9]

Hi,

I am new and have self learnt VBA from the internet, hence getting stuck at a basic issue. I tried my best to google the solution- but failed.

My code:
Selection.Copy


Sheets("A").Select
Cells(b, c).Select
ActiveSheet.Paste



Sheets("B").Select
Cells(b, c).Select
ActiveSheet.Paste

So it is copying perfectly, pasting it fine in Sheet A, after that for some reason the clipboard is getting cleared and I am getting Run-time Error 1004 once it reaches Sheet B..
Basically I am trying to do Ctrl C followed by Ctrl V, for multiple pasting, however it is doing Ctrl C followed by Enter.
Please advise.

I am using Office Professional 2016.

 

[Peter_SSs]

Welcome to the MrExcel board!

Provided b and c have been given some valid values, that code works fine for me.

Perhaps you haven't given us the full code or perhaps you have some other code (eg Worksheet_Change code) that could be impacting what you are trying to do?

 

[mohadin]

HI
May be

Sub test()
    Selection.Copy
    Sheets(Array("A", "B")).Select
    Columns("b:c").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub

 

[cairo9]

Welcome to the MrExcel board!

Provided b and c have been given some valid values, that code works fine for me.

Perhaps you haven't given us the full code or perhaps you have some other code (eg Worksheet_Change code) that could be impacting what you are trying to do?

Sub copy_1()


b = ActiveCell.Row
c = ActiveCell.Column

Selection.Copy

    Sheets("A").Select
    Cells(b, c).Select
    ActiveSheet.Paste


    Sheets("B").Select
    Cells(b, c).Select
    ActiveSheet.Paste

    ActiveWorkbook.Save
End Sub

------------
What I am trying to do? I am at one of the worksheet, so whatever is there in say Cell (5,6) I want to copy it and paste it in Sheet A and Sheet B again on cell (5,6)

Observation: So I tried going by pressing F8- just before activesheet.paste in Sheet A - on the lower bottom left it says" select destination and press ENTER or choose Paste". --> similar to what we get when we press Ctrl + C

However after pasting, in Sheet A --> it says "Ready" --> that mean the clipboard may have been cleared. Hence the pasting in sheet B is not happening.

I don't know, I want something like: Application.CutCopyMode = True ( if at all it exist).
Please advise.

 

[Peter_SSs]

As I said last time, that code works exactly as it is for me - copies the cell I have active and pastes it into the same position on Sheet A and sheet B.

Is it just the value from the active cell you are trying to get into the other two sheets or are you also trying to copy a formula or formatting etc?

If just the value, try this instead

Sub copy_2()
  b = ActiveCell.Row
  c = ActiveCell.Column

  Sheets("A").Cells(b, c).Value = ActiveCell.Value
  Sheets("A").Cells(b, c).Value = ActiveCell.Value
  
  ActiveWorkbook.Save
End Sub

BTW, when post code in the forum, please use code tags to preserve the indentation of the code. I added them in your last post for you. My signature block below explains how to use them or you can use the # button above the Reply window.

 

[cairo9]

Can you help me with a code or guide me to a forum from where I can get it.
I am trying to achieve --> The active cells from the current worksheet need to be copied ( maybe to the clipboard) and then I move to different sheets (say sheet A,B,C,D etc) and paste it there at the same column and row, pulling the information from the clipboard.

 

[Peter_SSs]

Can you help me with a code or guide me to a forum from where I can get it.
I am trying to achieve --> The active cells from the current worksheet need to be copied ( maybe to the clipboard) and then I move to different sheets (say sheet A,B,C,D etc) and paste it there at the same column and row, pulling the information from the clipboard.

As I said before, your code from post 4 does that for me already (for sheets A & B and more could be added).

However, here are a couple of other ways to achieve it.

Sub Copy_Range_v1()
  Dim rSel As Range
  Dim mySheets() As String, addr As String
  Dim sh As Variant
  
  mySheets = Split("A,B,C,D", ",")
  Set rSel = Selection
  addr = rSel.Cells(1).Address
  For Each sh In mySheets
    rSel.Copy Destination:=Sheets(sh).Range(addr)
  Next sh
End Sub

Sub Copy_Range_v2()
  Dim wsAct As Worksheet
  Dim mySheets() As String, addr As String
  Dim sh As Variant
  
  mySheets = Split("A,B,C,D", ",")
  Set wsAct = ActiveSheet
  Selection.Copy
  addr = Selection.Cells(1).Address
  Application.ScreenUpdating = False
  For Each sh In mySheets
    Application.Goto Sheets(sh).Range(addr)
    ActiveSheet.Paste
  Next sh
  wsAct.Activate
  Application.CutCopyMode = False
  Application.ScreenUpdating = True
End Sub

 

[cairo9]

Peter. Thanks a ton for theses, code 1 worked for me, code 2 did not- again due to the activesheet.paste function, which is clearing my clipboard ( don't know why). Either way it was a great learning.

Just wondering can this be extended to multiple excelfiles.
That is currently I am picking the data from a random sheet (say X) and pasting them in sheet A, B, C etc of file 1. In addition, now I would want the same data to also be pasted in sheet D,E,F of another file 2, of course at the same row and column.

 

[Peter_SSs]

I think it could but I'm not clear enough to provide specific code. I sus pect you might want to set up 2 array with the sheet names, one for the sheets in one destination file and the other destination file so, something like
mySheets1 = Split("A,F,Q,D", ",")
mySheets2 = Split("A,B,F,G,J,C", ",")

Then for the copy line you would need some with Destination:=Workbooks("workbook name 1").Sheets(sh).Range(Addr)
and some with Destination:=Workbooks("workbook name 2").Sheets(sh).Range(Addr)

 

[cairo9]

Thanks Peter- cannot thank you enough for all your help. I made mySheets2 and repeated the loop and it works perfectly fine.