I cannot select a range

[Kassie]

I wrote a macro, inter alia containing the following code:

Const constName = "JCMail.xls"
Const constPre = "JC"

Private Sub CommandButton1_Click()
' Application.ScreenUpdating = False
' Gets and Inserts job card number
Range("AB3").Select
' CommandButton1.Deactivate
varRef = ActiveCell.Value
copier
End Sub

Private Sub copier()
' Copies and pastes date
Windows(constName).Activate
Range("G3").Select

When the macro reaches the last line of code (Range("G3").Select), I get the
following error message:

Run-time Error 1004:
Select method of Range class failed

I have used the exact phrase in so many other macros, and it has always
worked. Why can't I do it here? I use this macro to copy info from one
sheet to another (in this case from an input sheet into a jobcard).

[Earl Kiosterud]

Kassie,

Use a break (F9 on that line), and Alt-Tab back to Excel. Ensure that the
desired worksheet is active, not perhaps a chart sheet or something. See if
whatever the active sheet is has anything that would disallow selecting your
range.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Kassie" <[email protected]> wrote in message
news:[email protected]...
>I wrote a macro, inter alia containing the following code:
>
> Const constName = "JCMail.xls"
> Const constPre = "JC"
>
> Private Sub CommandButton1_Click()
> ' Application.ScreenUpdating = False
> ' Gets and Inserts job card number
> Range("AB3").Select
> ' CommandButton1.Deactivate
> varRef = ActiveCell.Value
> copier
> End Sub
>
> Private Sub copier()
> ' Copies and pastes date
> Windows(constName).Activate
> Range("G3").Select
>
> When the macro reaches the last line of code (Range("G3").Select), I get
> the
> following error message:
>
> Run-time Error 1004:
> Select method of Range class failed
>
> I have used the exact phrase in so many other macros, and it has always
> worked. Why can't I do it here? I use this macro to copy info from one
> sheet to another (in this case from an input sheet into a jobcard).
>
>

[Dave Peterson]

And you usually don't have to select/activate something to work with it:

Private Sub copier()
' Copies and pastes date
with workbooks(constName).worksheets("sheet1") '???
.range("g3").copy _
destination:=.range("g6")
end with
end sub

(I have no idea what's really be copied or where you're pasting it though--or
what the worksheet name really is.)

Kassie wrote:
>
> I wrote a macro, inter alia containing the following code:
>
> Const constName = "JCMail.xls"
> Const constPre = "JC"
>
> Private Sub CommandButton1_Click()
> ' Application.ScreenUpdating = False
> ' Gets and Inserts job card number
> Range("AB3").Select
> ' CommandButton1.Deactivate
> varRef = ActiveCell.Value
> copier
> End Sub
>
> Private Sub copier()
> ' Copies and pastes date
> Windows(constName).Activate
> Range("G3").Select
>
> When the macro reaches the last line of code (Range("G3").Select), I get the
> following error message:
>
> Run-time Error 1004:
> Select method of Range class failed
>
> I have used the exact phrase in so many other macros, and it has always
> worked. Why can't I do it here? I use this macro to copy info from one
> sheet to another (in this case from an input sheet into a jobcard).

--

Dave Peterson

[Kassie]

Thanks for the comments. Clearly, I did not describe the problem correctly
or well enough. I think the key phrase in my question was: "This macro is
used to copy information from one sheet to another". When I changed the
Private Sub to Sub only, the macro started working correctly. I really
appreciate your input though.

Kind regards

Kassie

"Kassie" wrote:

> I wrote a macro, inter alia containing the following code:
>
> Const constName = "JCMail.xls"
> Const constPre = "JC"
>
> Private Sub CommandButton1_Click()
> ' Application.ScreenUpdating = False
> ' Gets and Inserts job card number
> Range("AB3").Select
> ' CommandButton1.Deactivate
> varRef = ActiveCell.Value
> copier
> End Sub
>
> Private Sub copier()
> ' Copies and pastes date
> Windows(constName).Activate
> Range("G3").Select
>
> When the macro reaches the last line of code (Range("G3").Select), I get the
> following error message:
>
> Run-time Error 1004:
> Select method of Range class failed
>
> I have used the exact phrase in so many other macros, and it has always
> worked. Why can't I do it here? I use this macro to copy info from one
> sheet to another (in this case from an input sheet into a jobcard).
>
>