Sheets().Range().Select

[b9k9uk]

Hi

Using the above notation:

Sheets(x).Range(xx).select produce a run time error 1004 select method of range class failed.

I have a VBA taking data from sheets(x).range(x) and placing it in sheets.range

What should be the correct notation for this simple task?

 

[MARK858]

Are you trying to select a cell on a sheet that isn't the ActiveSheet? If yes tell us what you are trying to do after the select as you rarely need to select a cell to work with it or preferably post your full code as you have it.

 

[b9k9uk]

Are you trying to select a cell on a sheet that isn't the ActiveSheet? If yes tell us what you are trying to do after the select as you rarely need to select a cell to work with it or preferably post your full code as you have it.

This is the code:

Sheets("G2G").Select

WeekNumber = Sheets("G2G").Range("B3").Value
'
' Start If - Then until WeekNumber becomes greater than 9
'
If WeekNumber = 1 Then
'
' Click_Week1 Macro
'
Sheets("G2GDATA").Range("Z3:AF14").Select
Selection.Copy
Sheets("G2G").Range("C4").Select
ActiveSheet.Paste

 

[MARK858]

Try the code below...

Sub testit1()
WeekNumber = Sheets("G2G").Range("B3").Value
If WeekNumber = 1 Then Sheets("G2GDATA").Range("Z3:AF14").Copy Sheets("G2G").Range("C4")
End Sub

 

[b9k9uk]

Sorry, it doesn't work. Should there be a .Paste at the end of the line?

In the mean time what I have got to work is..

Sheets("G2GDATA").Select
Range("AG3:AM14").Select
Selection.Copy
Sheets("G2G").Select
Range("C4").Select
ActiveSheet.Paste

which seems like a lot of code but it works.

Many thanks for your help.

 

[MARK858]

Sorry, it doesn't work. Should there be a .Paste at the end of the line?

The code I posted works fine for me, click the button on sheet G2G in the link below.
Btw where is the "If WeekNumber = 1 Then" test in the code you have just posted and why has the range changed?
And no there should not be a .Paste at the end.

https://app.box.com/s/8x2vqk3t9zfk69lri7tnmxtmv10396ma

 

[Mike Blackman]

Hi Mark,

If the OP has Option Explicit set at the top of the module then the code will definitely fail - based on what has been provided that's the only obvious potential point of failure.

Hi B9k9UK,

What error message do you get when you try Marks code?

 

[MARK858]

He hasn't mentioned having Option Explicit at the top of his code and it is not in the code he/she posted and no variables are declared in the code he/she posted.
If it is then he needs

Sub testit1()
Dim WeekNumber As Long
WeekNumber = Sheets("G2G").Range("B3").Value
If WeekNumber = 1 Then Sheets("G2GDATA").Range("Z3:AF14").Copy Sheets("G2G").Range("C4")
End Sub

Depending as stated on whether he/she adjusted the range and if he/she is still doing the test.

 

[b9k9uk]

Hi Mark,

If the OP has Option Explicit set at the top of the module then the code will definitely fail - based on what has been provided that's the only obvious potential point of failure.

Hi B9k9UK,

What error message do you get when you try Marks code?

Oops my bad!

Yes Marks code does work. I had the data in C4. When I deleted the data and then ran Marks code it did work.

My bad, it does work, many many thanks.

PS I do not have Option Explicit. That is a bit beyond me at the moment! I am using a series of Sub routines (Sub XXXX () - End Sub). Small steps maekth progress until we can run

 

[MARK858]

Oops my bad!

Yes Marks code does work. I had the data in C4. When I deleted the data and then ran Marks code it did work.

My bad, it does work, many many thanks.

Thanks for the feedback and happy you got it working