R
RobN
Near the bottom part of the code (below), ie "SH.Copy", when running the
code and it reaches that line, the operation to do that takes an unusually
long time. The status bar at that time reveals information as follows:
Cell (Press Esc to cancel) and a very slow moving green progress bar.
I don't understand why it should take about 30 secs or more to copy a sheet,
especially when that sheet has hardly any data and no formulas! (The formula
is ="GJ-" & TEXT(GenEntry!H2,"dd-mm-yy") & " for Inv# " &Y1&".txt")
When I copy that sheet manually, the process is faster than a speeding
bullet!
Also, if I save the workbook as a 2003 version it runs MUCH faster!
Is this just one of those miriad of version 2007 problems, or does there
have to be some changes in the code to accommodate whatever Version 2007 has
mucked up?
Rob
Sub mExportToMYOB()
On Error GoTo Oops
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim newFileName As String
Set WB = Workbooks("Journal Maker.xlsm")
Set SH = WB.Sheets("GenExportData")
Set rng = SH.Range("V1")
Dim drive As String
Dim dir As String
drive = "C"
dir = "C:\Documents and Settings\Rob\Desktop"
'other stuff......
ChDrive drive
ChDir dir
newFileName = rng.Text
SH.Copy
Application.DisplayAlerts = False
With ActiveWorkbook
.SaveAs Filename:=newFileName, _
FileFormat:=xlText
.Close
End With
code and it reaches that line, the operation to do that takes an unusually
long time. The status bar at that time reveals information as follows:
Cell (Press Esc to cancel) and a very slow moving green progress bar.
I don't understand why it should take about 30 secs or more to copy a sheet,
especially when that sheet has hardly any data and no formulas! (The formula
is ="GJ-" & TEXT(GenEntry!H2,"dd-mm-yy") & " for Inv# " &Y1&".txt")
When I copy that sheet manually, the process is faster than a speeding
bullet!
Also, if I save the workbook as a 2003 version it runs MUCH faster!
Is this just one of those miriad of version 2007 problems, or does there
have to be some changes in the code to accommodate whatever Version 2007 has
mucked up?
Rob
Sub mExportToMYOB()
On Error GoTo Oops
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim newFileName As String
Set WB = Workbooks("Journal Maker.xlsm")
Set SH = WB.Sheets("GenExportData")
Set rng = SH.Range("V1")
Dim drive As String
Dim dir As String
drive = "C"
dir = "C:\Documents and Settings\Rob\Desktop"
'other stuff......
ChDrive drive
ChDir dir
newFileName = rng.Text
SH.Copy
Application.DisplayAlerts = False
With ActiveWorkbook
.SaveAs Filename:=newFileName, _
FileFormat:=xlText
.Close
End With