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

 

[shg]

It means you don't have a worksheet named EXPENSES1 or EXPENSES2. Case doesn't matter, but spurious characters (e.g., spaces) do.

 

[ipbr21054]

Hi,
The thing is that i do.

There is a formula in the cell so i tried the below code but still the same message.

I will name them Sheet 1 and 2 and see what happens

Private Sub CommandButton1_Click()Sheets("EXPENSES1").Range("D30").Copy
Sheets("EXPENSES2").Range("D4").PasteSpecial Paste:=xlPasteValue


End Sub

 

[Hjemmet]

Try this one it made with Macro record

Sub Makro2()'
' Makro2 Makro
'


Sheets("EXPENSES1").Select
Range("D30").Select
Selection.Copy
Sheets("EXPENSES2").Select
Range("D4").Select
ActiveSheet.Paste
Sheets("EXPENSES1").Select
Range("F30:K30").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("EXPENSES2").Select
Range("F4").Select
ActiveSheet.Paste
End Sub

 

[RoryA]

One of the sheet names is wrong. Check them again very carefully.

 

[ipbr21054]

Ok
I have found something.
After you rename a tab Excel adds () around the number,like so EXPENSES(2)

i HAVE THIS WORKING TO A FASHION BUT THE COPY CELL D30 is stil in copy mode,i mean flashing lines around the cell.

Private Sub CommandButton1_Click()Sheets("EXPENSES1").Range("D30").Copy
Sheets("EXPENSES2").Range("D4").PasteSpecial Paste:=xlPasteValues


End Sub

 

[Rick Rothstein]

One of the sheet names is wrong. Check them again very carefully.

Further to Rory's comment... make sure the 1 (one) is not actually a lower case L. Also, as Rory said earlier... make sure you don't have any leading or trailing spaces in the name in the tab itself.

 

[ipbr21054]

Rick,
I am past that point now,reason was () around sheet name.

Now i can copy ok & paste ok but the copy cell is still selected and flashing.

 

[ipbr21054]

I thought if i activated a sheet and selected a cell would work.

This works & cell D20 is selected but still the copy cell is flashing

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("EXPENSES1").Activate
Sheets("EXPENSES1").Range("D20").Select


End Sub

 

[ipbr21054]

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.

Thanks all

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