Variable not Defined Compile error

[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

 

[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)?

 

[Bernie Deitrick]

Paul,

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

HTH,
Bernie
MS Excel MVP

 

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

 

[Bob Phillips]

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)

 

[JE McGimpsey]

Change Sheet2 to a valid codename for a worksheet in that workbook

 

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

 

[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

 

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

 

[Paul3rd]

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.

 

[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

 

[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

 

[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

 

[Dave Peterson]

I don't understand what you want done.

You may want to repost and phrase the question differently.

 

[Bob Phillips]

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]

Thanks Bernie!, That did worked perfectly.
Paul

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