About Us

Paul3rd

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Compile error, Variable not defined	RJR[_2_]	Excel Programming	4	March 23rd 07 02:21 AM
VBAProject name compile error, not defined at compile time	Matthew Dodds	Excel Programming	1	December 13th 05 07:17 PM
Incremental Numbering: Compile Error - Variable not defined	BEEJAY	Excel Programming	9	September 27th 05 08:59 PM
Variable not defined compile error	Phil Hageman[_4_]	Excel Programming	4	June 17th 05 01:52 PM
Compile error, variable not defined	davegb	Excel Programming	5	May 19th 05 04:41 PM

Menu

About Us