Error message but i dont see why Run Time Error 9

[ipbr21054]

Hi,

The code on my command button1 is shown.
I see the run time error 9 subscript out of range but i dont see why,

This is the info to help you.
EXPENSES1 copy cell value D30 then paste it to EXPENSES2 cell D4

Also
EXPENSES1 copy cell values F30:K30 then paste to EXPENSES2 F4:K4

Private Sub CommandButton1_Click()Sheets("EXPENSES1").Range("D30").Copy Sheets("EXPENSES2").Range("D4")
Sheets("EXPENSES1").Range("F30:K30").Copy Sheets("EXPENSES2").Range("F7")
End Sub

 

[Rick Rothstein]

This works now.

I had to also activate the sheet & select a cell as the pasted cells were also selected stil.
Not ideal but it works.

Private Sub CommandButton1_Click()
  Sheets("EXPENSES1").Range("D30").Copy
  Sheets("EXPENSES2").Range("D4").PasteSpecial Paste:=xlPasteValues
  Sheets("EXPENSES1").Range("F30:K30").Copy
  Sheets("EXPENSES2").Range("F4:K4").PasteSpecial Paste:=xlPasteValues
  Sheets("EXPENSES2").Activate
  ActiveSheet.Range("A5").Select
  Sheets("EXPENSES1").Activate
  ActiveSheet.Range("L32").Select
  ActiveWorkbook.Save
End Sub

If the above code works, then this code should also...

[table="width: 500"]
[tr]
	[td]Private Sub CommandButton1_Click()
  Sheets("EXPENSES2").Range("D4").Value = Sheets("EXPENSES1").Range("D30").Value
  Sheets("EXPENSES2").Range("F4:K4").Value = Sheets("EXPENSES1").Range("F30:K30").Value
End Sub[/td]
[/tr]
[/table]

By the way, you can eliminate the "marching ants" around a selection you just copied by using this line of code...

Application.CutCopyMode = False

 

[ipbr21054]

That code does not paste anything into a sheet but it does delete everything it was supposed to copy.
Reason being is copy and paste sheets are the same ?

 

[Rick Rothstein]

That code does not paste anything into a sheet but it does delete everything it was supposed to copy.
Reason being is copy and paste sheets are the same ?

If the code you posted in Message #10 really does work for you, then the code I posted in Message #11 should produce the identical end result. My code does not copy/paste, rather, since you were copying values only, my code takes the shortcut and assigns the values directly. I see no reason for the problem you are describing. For example, your code copied the value in cell D30 on EXPENSES1 to cell D4 on EXPENSES2... my code assigns the value D30 on EXPENSES1 directly to cell D4 on EXPENSES2... this would produce the same result as your copy operation. I then do the same thing for the multi-cell copy that you do afterwards. The only way I can see my code "deleting" anything is if you mixed the references for your copy/paste in the code you posted in Message #10 .

 

[ipbr21054]

Hi,
Well i started again in respect of copying your code and this time works great.

Thanks