Dear all,
I created the following simple macro using macro recorder in Excel 2007. However, the process was slow and took a minute or so to complete. Do you believe that this is a result of the extensive usage of Selection?
Is there a quicker method to do it?
Thanks.
Thanks.
hary
================
Sub Save_1()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False
'
' Save_1 Macro
'
'
Range("F5:G5").Select
Selection.Copy
Sheets("List_of_inputted_item").Select
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Form_input").Select
ActiveCell.Offset(2, 0).Range("A1:B1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("List_of_inputted_item").Select
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Form_input").Select
ActiveCell.Offset(2, 0).Range("A1:B1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("List_of_inputted_item").Select
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Form_input").Select
ActiveCell.Offset(2, 0).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("List_of_inputted_item").Select
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Form_input").Select
ActiveCell.Offset(0, 1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("List_of_inputted_item").Select
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Form_input").Select
ActiveCell.Offset(2, -1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("List_of_inputted_item").Select
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Form_input").Select
ActiveCell.Offset(0, 1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("List_of_inputted_item").Select
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Form_input").Select
ActiveCell.Select
Application.CutCopyMode = False
Selection.ClearContents
ActiveCell.Offset(-2, -1).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(-4, 0).Range("A1:B1").Select
Selection.ClearContents
ActiveCell.Offset(-2, 0).Range("A1:B1").Select
Selection.ClearContents
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
ActiveSheet.DisplayPageBreaks = True
End Sub
I created the following simple macro using macro recorder in Excel 2007. However, the process was slow and took a minute or so to complete. Do you believe that this is a result of the extensive usage of Selection?
Is there a quicker method to do it?
Thanks.
Thanks.
hary
================
Sub Save_1()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False
'
' Save_1 Macro
'
'
Range("F5:G5").Select
Selection.Copy
Sheets("List_of_inputted_item").Select
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Form_input").Select
ActiveCell.Offset(2, 0).Range("A1:B1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("List_of_inputted_item").Select
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Form_input").Select
ActiveCell.Offset(2, 0).Range("A1:B1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("List_of_inputted_item").Select
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Form_input").Select
ActiveCell.Offset(2, 0).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("List_of_inputted_item").Select
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Form_input").Select
ActiveCell.Offset(0, 1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("List_of_inputted_item").Select
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Form_input").Select
ActiveCell.Offset(2, -1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("List_of_inputted_item").Select
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Form_input").Select
ActiveCell.Offset(0, 1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("List_of_inputted_item").Select
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Form_input").Select
ActiveCell.Select
Application.CutCopyMode = False
Selection.ClearContents
ActiveCell.Offset(-2, -1).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(-4, 0).Range("A1:B1").Select
Selection.ClearContents
ActiveCell.Offset(-2, 0).Range("A1:B1").Select
Selection.ClearContents
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
ActiveSheet.DisplayPageBreaks = True
End Sub