molinamike
New Member
- Joined
- May 9, 2016
- Messages
- 10
Hello Gurus,
I am running VBA to copy data from a closed csv file and then paste into a specific cell within an open workbook. This workbook has fifteen worksheets and each sheet has a similar code for their respective sheet. The problem is that the code is running way to slow. Below is an example of the code for one of the sheets. Would someone review the code being used and tell me if I can make any adjustments that would speed things up? I really appreciate any help that can be provided.
Sub TransferData_MOD()
Application.ScreenUpdating = False
'showallrecods turns off filters
Call ShowAllRecords
'the below codes deletes all previous data before bringing in new data
Sheets("MOD").Activate
Sheets("MOD").Range("A3").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
'code used to bring in new data
Workbooks.Open Filename:="C:\DB3\MOD.CSV"
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("DB3.xlsb").Activate
Sheets("MOD").Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Workbooks("MOD.CSV").Close SaveChanges:=False
Windows("DB3.xlsb").Activate
'the below lines copies helper cells containing formulas and then pastes them as values
Sheets("MOD").Range("P3").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
I am running VBA to copy data from a closed csv file and then paste into a specific cell within an open workbook. This workbook has fifteen worksheets and each sheet has a similar code for their respective sheet. The problem is that the code is running way to slow. Below is an example of the code for one of the sheets. Would someone review the code being used and tell me if I can make any adjustments that would speed things up? I really appreciate any help that can be provided.
Sub TransferData_MOD()
Application.ScreenUpdating = False
'showallrecods turns off filters
Call ShowAllRecords
'the below codes deletes all previous data before bringing in new data
Sheets("MOD").Activate
Sheets("MOD").Range("A3").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
'code used to bring in new data
Workbooks.Open Filename:="C:\DB3\MOD.CSV"
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("DB3.xlsb").Activate
Sheets("MOD").Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Workbooks("MOD.CSV").Close SaveChanges:=False
Windows("DB3.xlsb").Activate
'the below lines copies helper cells containing formulas and then pastes them as values
Sheets("MOD").Range("P3").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub