ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Can not set WorkBook variable (https://www.excelbanter.com/excel-discussion-misc-queries/178817-can-not-set-workbook-variable.html)

jlclyde
Can not set WorkBook variable
 
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[_4_]
Can not set WorkBook variable
 
Maybe because you spelled "Workbook" wrong?

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



HTH,
JP


On Mar 5, 11:57*am, jlclyde wrote:
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

jlclyde
Can not set WorkBook variable
 
On Mar 5, 11:07*am, JP wrote:
Maybe because you spelled "Workbook" wrong?

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

HTH,
JP

On Mar 5, 11:57*am, jlclyde wrote:



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- Hide quoted text -

- 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
Can not set WorkBook variable
 
You need to select the sheet before you select the cell.

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

Jim Thomlinson


"jlclyde" wrote:

On Mar 5, 11:07 am, JP wrote:
Maybe because you spelled "Workbook" wrong?

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

HTH,
JP

On Mar 5, 11:57 am, jlclyde wrote:



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- Hide quoted text -

- 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.

jlclyde
Can not set WorkBook variable
 
On Mar 5, 12:56*pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote:
You need to select the sheet before you select the cell.

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

Jim Thomlinson



"jlclyde" wrote:
On Mar 5, 11:07 am, JP wrote:
Maybe because you spelled "Workbook" wrong?

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

HTH,
JP

On Mar 5, 11:57 am, jlclyde wrote:

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- Hide quoted text -

- 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.- Hide quoted text -

- Show quoted text -

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

JP[_4_]
Can not set WorkBook variable
 
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 ToolsOptions, 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


On Mar 5, 5:34*pm, jlclyde wrote:
On Mar 5, 12:56*pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-





This-.com wrote:
You need to select the sheet before you select the cell.

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

Jim Thomlinson

"jlclyde" wrote:
On Mar 5, 11:07 am, JP wrote:
Maybe because you spelled "Workbook" wrong?

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

HTH,
JP

On Mar 5, 11:57 am, jlclyde wrote:

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- Hide quoted text -

- 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.- Hide quoted text -

- Show quoted text -

Thanks. *I have it wrapped up now. *I appreciate your help.
Jay- Hide quoted text -

- Show quoted text -


All times are GMT +1. The time now is 02:37 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com