Changing the variable name from myWB to newWKB doesn't make the code any more
clear.

You may want to review that code that I suggested to see if is equivalent and
more clear. (I think that it is.)

Paul3rd wrote:

Dave, Good idea!
The following code works just fine, things might have been confused by the
MyWB designation. I changed that to NewWKB, Closed it and added
Application.Visible = True.
Thanks for all your help. I'm just learning to write code and things can get
hairy!
Paul
Sub NewModule()
Dim NewWKB As Workbook

Application.DisplayAlerts = False
Set NewWKB = Workbooks.Add("C:\FolderAppt\CTest.xlt")
NewWKB.Application.Visible = False
With Workbooks("Copy of ApptDis.xls")
.Worksheets("Sheet1").Range("B2:I2").Copy

With NewWKB.Sheets("Sheet1").Range("B2:I2")
.PasteSpecial xlPasteValues

NewWKB.SaveAs ("C:\CCTest.xls")
NewWKB.Close (True)
Application.Visible = True

End With
End With

End Sub

"Dave Peterson" wrote:

I didn't test this, but I don't think your program is closing Jan5CopyTest.xls.

But you are hiding the excel application with this line:
MyWB.Application.Visible = False

I'm not sure why you'd want to do this, but if there is a reason, you'll
probably(!) want to make it visible before the code ends:

Application.Visible = True

If you add this line after myWb is closed, then the reference to myWb won't be
good.

In fact, I'm not sure why you didn't just use:
Application.Visible = False
on that other line.

ps. Since the workbook that owns the code is Jan5CopyTest.xls, it would
probably be safer to use:

With thisworkbook
instead of:
With Workbooks("Jan5copytest.xls")

Then you won't have to worry about what happens if/when that file is renamed.

You have a few with/end with structures that really don't have much between
those lines.

I think that this is easier to read (after you delete the comments!):

Option Explicit
Sub NewModule()
Dim MyWB As Workbook

Set MyWB = Workbooks.Add("C:\FolderAppt\CTest.xlt")

ThisWorkbook.Worksheets("Sheet1").Range("B2:I2").C opy

'I'd just use the topleft cell of the range.
'Excel will adjust the pasted range to match the
'dimensions of the copied range
MyWB.Worksheets("Sheet1").Range("B2").PasteSpecial
Paste:=xlPasteValues

'no need for ()' in the next two lines
Application.DisplayAlerts = False
MyWB.SaveAs Filename:="C:\CCTest.xls"
Application.DisplayAlerts = True

MyWB.Close savechanges:=False

End Sub



Paul3rd wrote:

The name of the workbook that holds this code is Jan5copytest.xls.
This is the current code.
Sub NewModule()
Dim MyWB As Workbook

Application.DisplayAlerts = False
Set MyWB = Workbooks.Add("C:\FolderAppt\CTest.xlt")
With Workbooks("Jan5copytest.xls")
.Worksheets("Sheet1").Range("B2:I2").Copy

MyWB.Application.Visible = False
With MyWB.Sheets("Sheet1").Range("B2:I2")
.PasteSpecial xlPasteValues

MyWB.SaveAs ("C:\CCTest.xls")
MyWB.Close (False)

Application.DisplayAlerts = True
End With
End With

End Sub

"Dave Peterson" wrote:

ps. What's the name of the workbook that holds this code?

Dave Peterson wrote:

How about posting your current version of the code?

Paul3rd wrote:

Dave, sorry for the confusion.
My original code did create a new workbook.
Now, Jan5copytest.xls is open, it has a command button that triggers the
module.
The module creates a new workbook from the template (CTest.xlt), copies a
range to it, saves the new workbook as (CCTest.xls) and then closes it.
Which is what I wanted.
The module is also closing Jan5copytest.xls which is not what I wanted.
I won't use the character anymore.
Paul

"Dave Peterson" wrote:

I don't understand.

I thought you wanted a worksheet added to an existing workbook--not a new
workbook.

And if Jan5copytest.xls is already open (you used it in your With statement),
why are you trying to open it again.

And is there a reason you're not copying the code from the VBE and pasting it
into your message.

To me, those "" brackets mean that the stuff you posted isn't the same as the
code you're testing.

Paul3rd wrote:

Hello Dave,
You are correct, I didn't write the complete path to CTest.xlt.
I have the code working correctly now (in a new module) except for one small
thing...
The Jan5copytest.xls closes after code executes and I want to keep it open.
(I tried inserting a line Workbook.Open("C:\Jan5copytest') but it didn't work.

Sub NewModule()
Dim MyWB As Workbook

Application.DisplayAlerts = False
Set MyWB = Workbooks.Add("C:\FolderAppt\CTest.xlt")
With Workbooks("Jan5copytest.xls")
.Worksheets("Sheet1").Range("B2:I2").Copy


MyWB.Application.Visible = False
With MyWB.Sheets("Sheet1").Range("B2:I2")
.PasteSpecial xlPasteValues

MyWB.SaveAs ("C:\CCTest.xls")
MyWB.Close (True)
Workbooks.Open ("C:\Documents and Settings\Administrator\My Documents\Excel Code Tests\Jan5copytest.xls")
Application.DisplayAlerts = True
End With
End With

End Sub

"Dave Peterson" wrote:

Remove or comment the "on error resume next" line.

I bet that it's masking an error.

(You sure that C:\cTest.xlt actually exists?)

Paul3rd wrote:

Dave, Thanks for your reply, I changed the code to the following but no new
workbook/worksheet is created.

Sub CTMacro()
On Error Resume Next
Dim ExcelSheet As Object

Application.DisplayAlerts = False
Set ExcelSheet = Sheets.Add(Type:="C:\CTest.xlt")

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:\DaveTest.xls"
Application.DisplayAlerts = True
End With
End With
End Sub

"Dave Peterson" wrote:

Dim ExcelSheet As Object
set ExcelSheet = sheets.Add(type:="c:\ctest.xlt")



Paul3rd wrote:

Hello, I can't get the following code to execute correctly. It creates a new
worksheet, but not from a template as I intended.
Can anyone help?
Sub CTMacro()
On Error Resume Next
Dim ExcelSheet As Object

Application.DisplayAlerts = False
Set ExcelSheet = CreateObject("Excel.Sheet")
Set ExcelSheet = ExcelSheet.Workbooks.Add("C:\CTest.xlt")

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
Thanks in advance for any help.
Paul

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson