jamieflora
04-28-2007, 06:24 PM
Hi
I would love some help with some code I use daily to update a workbook. The code works for me but it takes a lonnnnnnng time to execute.
1. I am using Microsoft Excel 2003
2. I want to slim down the code and make it run faster. I update about 60 - 70 worksheets every day with this macro.
3. Basically, I have dates in column A, and investment information in columns B - FM. The data starts at row 7 and gets older as it goes down. I have about 7 years worth of data (A7:FM1795). So, after putting in the new prices of all the investments in one worksheet I use this macro to update all of the other calculations, etc. in the other worksheets. So I start in cell B3 of each worksheet and then go and add a new row at row 7. Then I format the new blank row by using the format from row 8. I want to copy what is in cell A7 of the worksheet( Investment Prices & Returns) but I haven't been successful (That would typically be todays date but if I forget 1 or 2 days I need to put in a prior date). For that reason I put in "ActiveCell = Date - 3" (I have the -3 because I am 3 days behind in updating the workbook today). Then I copy the formula in cell B3 from B7:FM7. I then do a paste-special on row 7 so I am only left with the values and not the formulas. After that is done I will go on the next worksheet and do it again. Eventually I will tie it all together and do the whole workbook with just one macro and that is why I want to trim it down now so it will be much better when I run it on all the worksheets together.
I put this macro together by recording several different macros and then combining them, as you will probably see.
4. I don't get any error messages.
5. Here is the code I use right now:
Sub Add_1_Blank_Row_And_Formula()
'
' Add_1_Blank_Row_And_Formula Macro
' Macro recorded 8/26/2006 by
'
' Keyboard Shortcut: Ctrl+q
'
' Turns off screen updating, which helps macro run faster.
Application.ScreenUpdating = False
Range("B3").Select
ActiveCell.Offset(4, 0).Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown
Selection.RowHeight = 12.75
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
Selection.Copy
ActiveCell.Offset(-1, 0).Rows("1:1").EntireRow.Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveCell.Offset(-4, 1).Range("A1").Select
Application.CutCopyMode = False
ActiveCell.Offset(4, -1).Select
ActiveCell = Date - 3
ActiveCell.Offset(-4, 1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(4, 0).Range("A1:FL1").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(-4, 0).Range("A1").Select
Application.CutCopyMode = False
' Turns ScreenUpdating back on.
Application.ScreenUpdating = True
End Sub
I am just starting to learn how to use VBA. I went a got 3 books from the library but they aren't helping me with trimming down this code. I am going to look for some more but in the meantime I would be very grateful and appreciative for any and all help.
Thank you very, very much in advance,
Jamie L. Flora
I would love some help with some code I use daily to update a workbook. The code works for me but it takes a lonnnnnnng time to execute.
1. I am using Microsoft Excel 2003
2. I want to slim down the code and make it run faster. I update about 60 - 70 worksheets every day with this macro.
3. Basically, I have dates in column A, and investment information in columns B - FM. The data starts at row 7 and gets older as it goes down. I have about 7 years worth of data (A7:FM1795). So, after putting in the new prices of all the investments in one worksheet I use this macro to update all of the other calculations, etc. in the other worksheets. So I start in cell B3 of each worksheet and then go and add a new row at row 7. Then I format the new blank row by using the format from row 8. I want to copy what is in cell A7 of the worksheet( Investment Prices & Returns) but I haven't been successful (That would typically be todays date but if I forget 1 or 2 days I need to put in a prior date). For that reason I put in "ActiveCell = Date - 3" (I have the -3 because I am 3 days behind in updating the workbook today). Then I copy the formula in cell B3 from B7:FM7. I then do a paste-special on row 7 so I am only left with the values and not the formulas. After that is done I will go on the next worksheet and do it again. Eventually I will tie it all together and do the whole workbook with just one macro and that is why I want to trim it down now so it will be much better when I run it on all the worksheets together.
I put this macro together by recording several different macros and then combining them, as you will probably see.
4. I don't get any error messages.
5. Here is the code I use right now:
Sub Add_1_Blank_Row_And_Formula()
'
' Add_1_Blank_Row_And_Formula Macro
' Macro recorded 8/26/2006 by
'
' Keyboard Shortcut: Ctrl+q
'
' Turns off screen updating, which helps macro run faster.
Application.ScreenUpdating = False
Range("B3").Select
ActiveCell.Offset(4, 0).Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown
Selection.RowHeight = 12.75
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
Selection.Copy
ActiveCell.Offset(-1, 0).Rows("1:1").EntireRow.Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveCell.Offset(-4, 1).Range("A1").Select
Application.CutCopyMode = False
ActiveCell.Offset(4, -1).Select
ActiveCell = Date - 3
ActiveCell.Offset(-4, 1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(4, 0).Range("A1:FL1").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(-4, 0).Range("A1").Select
Application.CutCopyMode = False
' Turns ScreenUpdating back on.
Application.ScreenUpdating = True
End Sub
I am just starting to learn how to use VBA. I went a got 3 books from the library but they aren't helping me with trimming down this code. I am going to look for some more but in the meantime I would be very grateful and appreciative for any and all help.
Thank you very, very much in advance,
Jamie L. Flora