| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable not Defined Compile error
Hello, This code triggers a compile error, variable not defined.
Sheet2 is the culprit, how can I correct this? Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.DisplayAlerts = False Sheet2.Range("B2:I2") = Sheet1.UsedRange.Address Application.DisplayAlerts = True End Sub Thanks in advance for any help. Paul |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Variable not Defined Compile error
Do you have a worksheet that has a codename of Sheet2?
Did you really mean to put that address in 8 cells (b2:i2)? Paul3rd wrote: Hello, This code triggers a compile error, variable not defined. Sheet2 is the culprit, how can I correct this? Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.DisplayAlerts = False Sheet2.Range("B2:I2") = Sheet1.UsedRange.Address Application.DisplayAlerts = True End Sub Thanks in advance for any help. Paul -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Variable not Defined Compile error
Dave, This sheet (Sheet2) is a temporary worksheet created by code in a
module that lets the user copy a range of cells (values only), close the workbook, and then paste that range into whatever workbook is currently open. So, yes I need the complete cell range saved as an address. Thanks for your reply, Paul "Dave Peterson" wrote: Do you have a worksheet that has a codename of Sheet2? Did you really mean to put that address in 8 cells (b2:i2)? Paul3rd wrote: Hello, This code triggers a compile error, variable not defined. Sheet2 is the culprit, how can I correct this? Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.DisplayAlerts = False Sheet2.Range("B2:I2") = Sheet1.UsedRange.Address Application.DisplayAlerts = True End Sub Thanks in advance for any help. Paul -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Variable not Defined Compile error
I don't understand what you want done.
You may want to repost and phrase the question differently. Paul3rd wrote: Dave, This sheet (Sheet2) is a temporary worksheet created by code in a module that lets the user copy a range of cells (values only), close the workbook, and then paste that range into whatever workbook is currently open. So, yes I need the complete cell range saved as an address. Thanks for your reply, Paul "Dave Peterson" wrote: Do you have a worksheet that has a codename of Sheet2? Did you really mean to put that address in 8 cells (b2:i2)? Paul3rd wrote: Hello, This code triggers a compile error, variable not defined. Sheet2 is the culprit, how can I correct this? Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.DisplayAlerts = False Sheet2.Range("B2:I2") = Sheet1.UsedRange.Address Application.DisplayAlerts = True End Sub Thanks in advance for any help. Paul -- Dave Peterson -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Variable not Defined Compile error
Paul,
You need a sheet in your workbook whose codename is Sheet2.... HTH, Bernie MS Excel MVP "Paul3rd" wrote in message ... Hello, This code triggers a compile error, variable not defined. Sheet2 is the culprit, how can I correct this? Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.DisplayAlerts = False Sheet2.Range("B2:I2") = Sheet1.UsedRange.Address Application.DisplayAlerts = True End Sub Thanks in advance for any help. Paul |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Variable not Defined Compile error
Bernie, Thanks for your reply,
The workbook (CTest.xls) only has one worksheet. Sheet2 is in whatever workbook currently open that has the following module, it allows the user to paste the saved range from CTest.xls even though that workbook is closed. Sub CMacro() Dim ExcelSheet As Object Application.DisplayAlerts = False Set ExcelSheet = CreateObject("Excel.Sheet") With Workbooks("Copy of ApptDis.xls") .Worksheets("Sheet1").Range("B2:I2").Copy ExcelSheet.Application.Visible = True With ExcelSheet.ActiveSheet.Range("B2:I2") .PasteSpecial xlPasteValues ExcelSheet.SaveAs "C:\CTest.xls" Application.DisplayAlerts = True End With End With End Sub If I add a sheet(Sheet2) to CTest.xls will it cause an error? Paul "Bernie Deitrick" wrote: Paul, You need a sheet in your workbook whose codename is Sheet2.... HTH, Bernie MS Excel MVP "Paul3rd" wrote in message ... Hello, This code triggers a compile error, variable not defined. Sheet2 is the culprit, how can I correct this? Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.DisplayAlerts = False Sheet2.Range("B2:I2") = Sheet1.UsedRange.Address Application.DisplayAlerts = True End Sub Thanks in advance for any help. Paul |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Variable not Defined Compile error
Try explicitly setting Sheet2:
Dim Sheet1 As Worksheet Dim Sheet2 As Worksheet Set Sheet1 = Worksheets("Sheet1") On Error Resume Next Application.DisplayAlerts = False Worksheets("Added Sheet").Delete Application.DisplayAlerts = True Set Sheet2 = Worksheets.Add Sheet2.Name = "Added Sheet" Sheet2.Range("B2:I2") = Sheet1.UsedRange.Address And then Sheet2.whatever will work... HTH, Bernie MS Excel MVP "Paul3rd" wrote in message ... Bernie, Thanks for your reply, The workbook (CTest.xls) only has one worksheet. Sheet2 is in whatever workbook currently open that has the following module, it allows the user to paste the saved range from CTest.xls even though that workbook is closed. Sub CMacro() Dim ExcelSheet As Object Application.DisplayAlerts = False Set ExcelSheet = CreateObject("Excel.Sheet") With Workbooks("Copy of ApptDis.xls") .Worksheets("Sheet1").Range("B2:I2").Copy ExcelSheet.Application.Visible = True With ExcelSheet.ActiveSheet.Range("B2:I2") .PasteSpecial xlPasteValues ExcelSheet.SaveAs "C:\CTest.xls" Application.DisplayAlerts = True End With End With End Sub If I add a sheet(Sheet2) to CTest.xls will it cause an error? Paul "Bernie Deitrick" wrote: Paul, You need a sheet in your workbook whose codename is Sheet2.... HTH, Bernie MS Excel MVP "Paul3rd" wrote in message ... Hello, This code triggers a compile error, variable not defined. Sheet2 is the culprit, how can I correct this? Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.DisplayAlerts = False Sheet2.Range("B2:I2") = Sheet1.UsedRange.Address Application.DisplayAlerts = True End Sub Thanks in advance for any help. Paul |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Variable not Defined Compile error
Thanks Bernie!, That did worked perfectly.
Paul "Bernie Deitrick" wrote: Try explicitly setting Sheet2: Dim Sheet1 As Worksheet Dim Sheet2 As Worksheet Set Sheet1 = Worksheets("Sheet1") On Error Resume Next Application.DisplayAlerts = False Worksheets("Added Sheet").Delete Application.DisplayAlerts = True Set Sheet2 = Worksheets.Add Sheet2.Name = "Added Sheet" Sheet2.Range("B2:I2") = Sheet1.UsedRange.Address And then Sheet2.whatever will work... HTH, Bernie MS Excel MVP "Paul3rd" wrote in message ... Bernie, Thanks for your reply, The workbook (CTest.xls) only has one worksheet. Sheet2 is in whatever workbook currently open that has the following module, it allows the user to paste the saved range from CTest.xls even though that workbook is closed. Sub CMacro() Dim ExcelSheet As Object Application.DisplayAlerts = False Set ExcelSheet = CreateObject("Excel.Sheet") With Workbooks("Copy of ApptDis.xls") .Worksheets("Sheet1").Range("B2:I2").Copy ExcelSheet.Application.Visible = True With ExcelSheet.ActiveSheet.Range("B2:I2") .PasteSpecial xlPasteValues ExcelSheet.SaveAs "C:\CTest.xls" Application.DisplayAlerts = True End With End With End Sub If I add a sheet(Sheet2) to CTest.xls will it cause an error? Paul "Bernie Deitrick" wrote: Paul, You need a sheet in your workbook whose codename is Sheet2.... HTH, Bernie MS Excel MVP "Paul3rd" wrote in message ... Hello, This code triggers a compile error, variable not defined. Sheet2 is the culprit, how can I correct this? Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.DisplayAlerts = False Sheet2.Range("B2:I2") = Sheet1.UsedRange.Address Application.DisplayAlerts = True End Sub Thanks in advance for any help. Paul |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Variable not Defined Compile error
You need to establish Sheet1 and Sheet2:
Dim Sheet1 as Worksheet Dim Sheet2 as Worksheet Set Sheet1=Sheets("Sheet1") Set Sheet2=Sheets("Sheet2") etc. -- Gary''s Student - gsnu200763 "Paul3rd" wrote: Hello, This code triggers a compile error, variable not defined. Sheet2 is the culprit, how can I correct this? Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.DisplayAlerts = False Sheet2.Range("B2:I2") = Sheet1.UsedRange.Address Application.DisplayAlerts = True End Sub Thanks in advance for any help. Paul |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Variable not Defined Compile error
Dim Sheet1 as Worksheet
I would avoid using "SheetN" as a variable name since these are the (default) code names of the sheets. It might become confusing. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Gary''s Student" wrote in message ... You need to establish Sheet1 and Sheet2: Dim Sheet1 as Worksheet Dim Sheet2 as Worksheet Set Sheet1=Sheets("Sheet1") Set Sheet2=Sheets("Sheet2") etc. -- Gary''s Student - gsnu200763 "Paul3rd" wrote: Hello, This code triggers a compile error, variable not defined. Sheet2 is the culprit, how can I correct this? Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.DisplayAlerts = False Sheet2.Range("B2:I2") = Sheet1.UsedRange.Address Application.DisplayAlerts = True End Sub Thanks in advance for any help. Paul |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Variable not Defined Compile error
Hi, Thanks for your reply,
The workbook (CTest.xls) only has one sheet(Sheet1). When I try Dim Sheet2 As Worksheet or WorksheetFunction I get a "Runtime Error 9, subscript out of range". Sheet2 is whatever current workbook is open and has the following module to allow the user to paste the saved range from CTest.xls even though it's closed. Sub CMacro() Dim ExcelSheet As Object Application.DisplayAlerts = False Set ExcelSheet = CreateObject("Excel.Sheet") With Workbooks("Copy of ApptDis.xls") .Worksheets("Sheet1").Range("B2:I2").Copy ExcelSheet.Application.Visible = True With ExcelSheet.ActiveSheet.Range("B2:I2") .PasteSpecial xlPasteValues ExcelSheet.SaveAs "C:\CTest.xls" Application.DisplayAlerts = True End With End With End Sub How can I declare Sheet2 as a variable in a workbook where it doesn't exist? Thanks for your help, Paul "Gary''s Student" wrote: You need to establish Sheet1 and Sheet2: Dim Sheet1 as Worksheet Dim Sheet2 as Worksheet Set Sheet1=Sheets("Sheet1") Set Sheet2=Sheets("Sheet2") etc. -- Gary''s Student - gsnu200763 "Paul3rd" wrote: Hello, This code triggers a compile error, variable not defined. Sheet2 is the culprit, how can I correct this? Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.DisplayAlerts = False Sheet2.Range("B2:I2") = Sheet1.UsedRange.Address Application.DisplayAlerts = True End Sub Thanks in advance for any help. Paul |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Variable not Defined Compile error
Sheet2 is the codename. Do you have a second sheet, and is the codename
Sheet2? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Paul3rd" wrote in message ... Hello, This code triggers a compile error, variable not defined. Sheet2 is the culprit, how can I correct this? Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.DisplayAlerts = False Sheet2.Range("B2:I2") = Sheet1.UsedRange.Address Application.DisplayAlerts = True End Sub Thanks in advance for any help. Paul |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Variable not Defined Compile error
Bob, Thanks for your reply,
The workbook (CTest.xls) only has one sheet (Sheet1), Sheet2 is in whatever workbook currently open and has the following module which allows the user to paste the saved range from Sheet1 even though that workbook is closed. Sub CMacro() Dim ExcelSheet As Object Application.DisplayAlerts = False Set ExcelSheet = CreateObject("Excel.Sheet") With Workbooks("Copy of ApptDis.xls") .Worksheets("Sheet1").Range("B2:I2").Copy ExcelSheet.Application.Visible = True With ExcelSheet.ActiveSheet.Range("B2:I2") .PasteSpecial xlPasteValues ExcelSheet.SaveAs "C:\CTest.xls" Application.DisplayAlerts = True End With End With End Sub "Bob Phillips" wrote: Sheet2 is the codename. Do you have a second sheet, and is the codename Sheet2? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Paul3rd" wrote in message ... Hello, This code triggers a compile error, variable not defined. Sheet2 is the culprit, how can I correct this? Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.DisplayAlerts = False Sheet2.Range("B2:I2") = Sheet1.UsedRange.Address Application.DisplayAlerts = True End Sub Thanks in advance for any help. Paul |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Variable not Defined Compile error
Then you need to address the workbook as well as the sheet, and you cannot
use the codename in another workbook. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Paul3rd" wrote in message ... Bob, Thanks for your reply, The workbook (CTest.xls) only has one sheet (Sheet1), Sheet2 is in whatever workbook currently open and has the following module which allows the user to paste the saved range from Sheet1 even though that workbook is closed. Sub CMacro() Dim ExcelSheet As Object Application.DisplayAlerts = False Set ExcelSheet = CreateObject("Excel.Sheet") With Workbooks("Copy of ApptDis.xls") .Worksheets("Sheet1").Range("B2:I2").Copy ExcelSheet.Application.Visible = True With ExcelSheet.ActiveSheet.Range("B2:I2") .PasteSpecial xlPasteValues ExcelSheet.SaveAs "C:\CTest.xls" Application.DisplayAlerts = True End With End With End Sub "Bob Phillips" wrote: Sheet2 is the codename. Do you have a second sheet, and is the codename Sheet2? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Paul3rd" wrote in message ... Hello, This code triggers a compile error, variable not defined. Sheet2 is the culprit, how can I correct this? Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.DisplayAlerts = False Sheet2.Range("B2:I2") = Sheet1.UsedRange.Address Application.DisplayAlerts = True End Sub Thanks in advance for any help. Paul |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Variable not Defined Compile error
Change Sheet2 to a valid codename for a worksheet in that workbook
In article , Paul3rd wrote: Hello, This code triggers a compile error, variable not defined. Sheet2 is the culprit, how can I correct this? Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.DisplayAlerts = False Sheet2.Range("B2:I2") = Sheet1.UsedRange.Address Application.DisplayAlerts = True End Sub Thanks in advance for any help. Paul |
|
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Variable not Defined Compile error
Hello,
Thanks for your reply, The workbook only has one sheet (Sheet1). The following code is in a module on Sheet2 which is any active sheet in another workbook. Sub CMacro() Dim ExcelSheet As Object Application.DisplayAlerts = False Set ExcelSheet = CreateObject("Excel.Sheet") With Workbooks("Copy of ApptDis.xls") .Worksheets("Sheet1").Range("B2:I2").Copy ExcelSheet.Application.Visible = True With ExcelSheet.ActiveSheet.Range("B2:I2") .PasteSpecial xlPasteValues ExcelSheet.SaveAs "C:\CTest.xls" Application.DisplayAlerts = True End With End With End Sub It allows Sheet2 to reference the saved range from a closed workbook. "JE McGimpsey" wrote: Change Sheet2 to a valid codename for a worksheet in that workbook In article , Paul3rd wrote: Hello, This code triggers a compile error, variable not defined. Sheet2 is the culprit, how can I correct this? Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.DisplayAlerts = False Sheet2.Range("B2:I2") = Sheet1.UsedRange.Address Application.DisplayAlerts = True End Sub Thanks in advance for any help. Paul |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Compile error, Variable not defined | Excel Programming | |||
| VBAProject name compile error, not defined at compile time | Excel Programming | |||
| Incremental Numbering: Compile Error - Variable not defined | Excel Programming | |||
| Variable not defined compile error | Excel Programming | |||
| Compile error, variable not defined | Excel Programming | |||
Hybrid Mode
