More efficient code than this?
 

Hello all,

I recorded this macro using Excel's Macro Recorder and making tweaks
afterwards. After reading up on VBA I have a feeling that the code is
probably bloated with unecessary commands/lines. It also runs fairly slow on
my computer.

What the macro does is copy a worksheet to a new book, copies that whole
sheet and pastes special as values. Then it does a save as and closes...

Sub Macro1()

Sheets("Invoice").Select
Sheets("Invoice").Copy
Cells.Select
Range("C11").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

ActiveWorkbook.SaveAs "C:\Documents and Settings\GIT " & Range("G8") & ".xls"
ActiveWorkbook.Close
End Sub

More efficient code than this?
 

Sub Macro1()
Sheets("Invoice").Copy
cells.value = cells.value 'works on active sheet
wtih ActiveWorkbook
.SaveAs "C:\Documents and Settings\GIT " & Range("G8") & ".xls"
.Close
end with
End Sub
--
HTH...

Jim Thomlinson


"chrismv48" wrote:

Hello all,

I recorded this macro using Excel's Macro Recorder and making tweaks
afterwards. After reading up on VBA I have a feeling that the code is
probably bloated with unecessary commands/lines. It also runs fairly slow on
my computer.

What the macro does is copy a worksheet to a new book, copies that whole
sheet and pastes special as values. Then it does a save as and closes...

Sub Macro1()

Sheets("Invoice").Select
Sheets("Invoice").Copy
Cells.Select
Range("C11").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

ActiveWorkbook.SaveAs "C:\Documents and Settings\GIT " & Range("G8") & ".xls"
ActiveWorkbook.Close
End Sub

More efficient code than this?
 

This is actually slower and errors at the end??

"Jim Thomlinson" wrote:

Sub Macro1()
Sheets("Invoice").Copy
cells.value = cells.value 'works on active sheet
wtih ActiveWorkbook
.SaveAs "C:\Documents and Settings\GIT " & Range("G8") & ".xls"
.Close
end with
End Sub
--
HTH...

Jim Thomlinson


"chrismv48" wrote:

Hello all,

I recorded this macro using Excel's Macro Recorder and making tweaks
afterwards. After reading up on VBA I have a feeling that the code is
probably bloated with unecessary commands/lines. It also runs fairly slow on
my computer.

What the macro does is copy a worksheet to a new book, copies that whole
sheet and pastes special as values. Then it does a save as and closes...

Sub Macro1()

Sheets("Invoice").Select
Sheets("Invoice").Copy
Cells.Select
Range("C11").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

ActiveWorkbook.SaveAs "C:\Documents and Settings\GIT " & Range("G8") & ".xls"
ActiveWorkbook.Close
End Sub

More efficient code than this?
 

Try this...

Sub Macro1()
Sheets("Invoice").Copy
usedrange.value = usedrange.value 'works on active sheet
with ActiveWorkbook
.SaveAs "C:\Documents and Settings\GIT " & Range("G8") & ".xls"
.Close
end with
End Sub

--
HTH...

Jim Thomlinson


"chrismv48" wrote:

This is actually slower and errors at the end??

"Jim Thomlinson" wrote:

Sub Macro1()
Sheets("Invoice").Copy
cells.value = cells.value 'works on active sheet
wtih ActiveWorkbook
.SaveAs "C:\Documents and Settings\GIT " & Range("G8") & ".xls"
.Close
end with
End Sub
--
HTH...

Jim Thomlinson


"chrismv48" wrote:

Hello all,

I recorded this macro using Excel's Macro Recorder and making tweaks
afterwards. After reading up on VBA I have a feeling that the code is
probably bloated with unecessary commands/lines. It also runs fairly slow on
my computer.

What the macro does is copy a worksheet to a new book, copies that whole
sheet and pastes special as values. Then it does a save as and closes...

Sub Macro1()

Sheets("Invoice").Select
Sheets("Invoice").Copy
Cells.Select
Range("C11").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

ActiveWorkbook.SaveAs "C:\Documents and Settings\GIT " & Range("G8") & ".xls"
ActiveWorkbook.Close
End Sub