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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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