Variable not Defined Compile error

P

Paul3rd

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
 
D

Dave Peterson

Do you have a worksheet that has a codename of Sheet2?

Did you really mean to put that address in 8 cells (b2:i2)?
 
B

Bernie Deitrick

Paul,

You need a sheet in your workbook whose codename is Sheet2....

HTH,
Bernie
MS Excel MVP
 
G

Gary''s Student

You need to establish Sheet1 and Sheet2:

Dim Sheet1 as Worksheet
Dim Sheet2 as Worksheet

Set Sheet1=Sheets("Sheet1")
Set Sheet2=Sheets("Sheet2")

etc.
 
C

Chip Pearson

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)
 
P

Paul3rd

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
 
P

Paul3rd

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

Paul3rd

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
 
P

Paul3rd

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
 
B

Bernie Deitrick

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
 
D

Dave Peterson

I don't understand what you want done.

You may want to repost and phrase the question differently.
 
P

Paul3rd

Thanks Bernie!, That did worked perfectly.
Paul

Bernie Deitrick said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top