| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| variable substitution in a formula linking to external workbook | Excel Discussion (Misc queries) | |||
| External reference with variable workbook name? | New Users to Excel | |||
| How do I link to a workbook whose name is variable? | Excel Discussion (Misc queries) | |||
| Use of variable to identify range of sheets in a workbook | Excel Discussion (Misc queries) | |||
| Getting data from another workbook based on variable | Excel Discussion (Misc queries) | |||
Linear Mode
