Can not set WorkBook variable

[jlclyde]

I am trying to copy a group of cells from one spread sheet and move it
to another sheet after the 2nd sheet is opened. It will not let me
set the variable WkBk2. Let me know if you see nay issues with this
code.

Thanks,
Jay

Private Sub OptionButton1_Click()
Dim C1, C2 As Range
Dim WkBk2 As Workbook
Dim Sht2 As Worksheet
Set C1 = Selection
Dim NextRow As Long

Workbooks.Open Filename:="G:\Masters\External Sales Masters
\Pumpernickel Press\Current CGC run info sheets - Regular PMP
Runs.xls"
Set WkBk2 = Wokbook("Current CGC run info sheets - Regular PMP
Runs.xls")
Set Sht2 = WkBk2.Sheet1
Set C2 = WkBk2.Sht2.Range("A4:AC10")
NextRow = Sht2.Range("B65536").End(xlUp).Row + 2

C2.Copy
Range("A" & NextRow).PasteSpecial (xlPasteAll)
C1.Copy
Range("B" & NextRow).PasteSpecial (xlPasteValues)
Range("J" & NextRow, "R" & NextRow + 8).Copy
Range("J" & NextRow).PasteSpecial (xlPasteValues)
Range("B" & NextRow).NumberFormat = ";;;"
Range("B" & NextRow + 1) = "Run " & Range("B" & NextRow +
1).Value

Unload Me
End Sub

 

[JP]

Maybe because you spelled "Workbook" wrong?

Set WkBk2 = Wokbook("Current CGC run info sheets - Regular PMP
Runs.xls")



HTH,
JP

 

[jlclyde]

Maybe because you spelled "Workbook" wrong?

Set WkBk2 = Wokbook("Current CGC run info sheets - Regular PMP
Runs.xls")

HTH,
JP






- Show quoted text -

JP,
I feel like a heel. I went over this for a while and gave up after it
started to throw so many errors. this definitly put me on track to
fix the issues. Here is the new code and I have run into antoher
problem. I can not seem to select the cell that i want, and I am sure
that the spelling is correct this time.
Jay

Private Sub OptionButton1_Click()
Dim C1, C2 As Range
Dim WkBk2 As Workbook
Dim Sht2 As Worksheet
Set C1 = Selection
Dim NextRow As Long

Workbooks.Open Filename:="G:\Masters\External Sales Masters
\Pumpernickel Press\Current CGC run info sheets - Regular PMP
Runs.xls"
Set WkBk2 = Workbooks("Current CGC run info sheets - Regular PMP
Runs.xls")
Set Sht2 = Worksheets("Runs 1+")
Set C2 = WkBk2.Worksheets("Runs 1+").Range("A4:AC10")
NextRow = Sht2.Range("B65536").End(xlUp).Row + 2

C2.Copy
Sht2.Range("A" & NextRow).PasteSpecial (xlPasteAll)
C1.Copy
Sht2.Range("B" & NextRow).PasteSpecial (xlPasteValues)
Sht2.Range("J" & NextRow, "R" & NextRow + 8).Copy
Sht2.Range("J" & NextRow).PasteSpecial (xlPasteValues)
Sht2.Range("B" & NextRow).NumberFormat = ";;;"
Sht2.Range("B" & NextRow + 1) = "Run " & Range("B" & NextRow +
1).Value
$$ Sht2.Range("B" & NextRow).Select ' It will not recognise
this.
Unload Me
End Sub
It is having trouble with the line that has $$ in front of it.
Obviously theses are not in the code.

 

[Jim Thomlinson]

You need to select the sheet before you select the cell.

Sht2.Select
Sht2.Range("B" & NextRow).Select

 

[jlclyde]

You need to select the sheet before you select the cell.

        Sht2.Select
        Sht2.Range("B" & NextRow).Select  
--
HTH...

Jim Thomlinson









- Show quoted text -

Thanks. I have it wrapped up now. I appreciate your help.
Jay

 

[JP]

Hey, at least you are back on track. Using "Option Explicit" and
turning on the auto list members features will limit the amount of
spelling errors somewhat.

In the VB Editor, click Tools>Options, on the Editor tab make sure
each of these is checked off:

Auto Syntax Check
Require Variable Declaration
Auto List Members
Auto Quick Info
Auto Data Tips


HTH,
JP