Help with speeding up a macro

[jamieflora]

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

[mdmackillop]

Hi Jamie,
Welcome to VBAX
Here's some code to try. This replicates your own macro, but will not loop through rows. If you can sanitise and post a sheet from your workbook, (you can do this using Manage Attachments in the Go Advanced reply section), this can be taken a bit further.
Regards
MD
Sub Add_1_Blank_Row_And_Formula()

' Keyboard Shortcut: Ctrl+q
'
' Turns off screen updating, which helps macro run faster.
Application.ScreenUpdating = False
Rows(7).Insert
With Cells(7, 1)
.Value = Date - 3
.RowHeight = 12.75
Cells(3, 2).Copy
.Offset(, 1).Resize(, 168).PasteSpecial xlPasteFormulas
.Offset(, 1).Resize(, 168).PasteSpecial xlPasteValues
End With
Cells(3, 2).Select

Application.CutCopyMode = False
' Turns ScreenUpdating back on.
Application.ScreenUpdating = True
End Sub

[Paul_Hossler]

A WB (Small one!!!) with limited samples of data would help, but some guesses:

1. Could you Copy row7, and Insert it to create a "holder" with the formating?
2. Instead of formulas, CopyPasteSpecial, etc. it'd be faster to do the math in VBA and just put the values where they need to go

[jamieflora]

OK, I replaced the code I have for the one you gave me mdmackillop. It seems to run a lot faster now, thank you.

I have 1 question and an error that I would love some more help on regarding this code.

The question is: I want cell A7 to = cell A7 in a different worksheet (Investment Prices & Returns). I can't seem to figure out how to do it.

The error is that after copying cell B3 throughout the new row that we inserted I only get #VALUE in all of the cells instead of the numbers I was getting before. How do I correct that?

The code follows: (I have added red comments where I believe the two problems occur.)


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
Rows(7).Insert
With Cells(7, 1)
.Value = Date - 4 (I want this value to = cell A7 in the worksheet Investment Prices & Returns, not the Date - 4 I have now)
.RowHeight = 12.75
Cells(3, 2).Copy
.Offset(, 1).Resize(, 168).PasteSpecial xlPasteFormulas
.Offset(, 1).Resize(, 168).PasteSpecial xlPasteValues (This only gives me the #VALUE error not the results to the formula that is copied from cell B3)
End With
Rows(8).Select
Selection.Copy
Rows(7).Select
Selection.PasteSpecial Paste:=xlPasteFormats
Cells(3, 2).Select
Application.CutCopyMode = False
' Turns ScreenUpdating back on.
Application.ScreenUpdating = True
End Sub

Once again, thank you so much for your help. This is going to greatly decrease the time spent on these updates every day.

Sincerely,

Jamie L. Flora

PS I attached (At least I think I attached it correctly) parts of two of the worksheets that I use in this workbook if that would be of any help. I also added a couple of lines in the code above to get the formatting I wanted from row 8.

I just opened up the attached worksheet and I have some links that don't update because the rest of the workbood isn't attached. Row 6 on both worksheets should show the names of the individual investments.

[mdmackillop]

Sub Add_1_Blank_Row_And_Formula()
Application.ScreenUpdating = False
Rows(7).Insert
With Cells(7, 1)
Select Case ActiveSheet.Name
Case "Investment Prices & Returns"
.Value = Range("A7").Value
Case Else
.Value = Date - 4
End Select
.RowHeight = 12.75
Cells(3, 2).Copy
.Offset(, 1).Resize(, 168).PasteSpecial xlPasteFormulas
.Offset(, 1).Resize(, 168).Copy
.Offset(, 1).Resize(, 168).PasteSpecial xlPasteValues
End With
Rows(8).Select
Selection.Copy
Rows(7).Select
Selection.PasteSpecial Paste:=xlPasteFormats
Cells(3, 2).Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

[jamieflora]

mdmackillop,

Now the code works perfectly. Thank you very much again.

Sincerely,

Jamie L. Flora

[johnske]

I see Malcolm beat me to it, oh well - another approach...

Option Explicit

Sub Add_1_Blank_Row_And_Formula()

Application.ScreenUpdating = False
Rows(7).Insert
Rows(8).Copy Rows(7) '< copies formats etc
Rows(7).ClearContents '< formats only wanted
Range("A7") = Range("A8").Value '< date previously in A7 is now in A8
With Range("B7:FM7")
.Formula = Range("B3").Formula '< apply formula in B3
.Value = .Value '< convert to values
End With
Range("B3").Select
Application.ScreenUpdating = True

End Sub

[jamieflora]

Thanks for all the help,

Just one question for you all that have helped so far or anyone else. I have been reading several books and on the internet but I can't seem to find an easy way to copy one cell from one worksheet to a cell in another worksheet.

Malcom, you used the following to get the cell value from another sheet:

With Cells(7, 1)
Select Case ActiveSheet.Name
Case "Investment Prices & Returns"
.Value = Range("A7").Value
Case Else
.Value = Date - 4
End Select
All I actually need is the value from A7 from "Investment Prices & Returns", not the "date - 4". Do I have to use the "Select Case, etc." or couldn't I use something like "value = Sheets(Investment Prices & Returns).Cells(7,1). I don't think it is that simple because I haven't been able to get something like that to work (Although I am very new to all this).

Sincerely,

Jamie L. Flora

[jamieflora]

Hi,

I was just going through and updating all my worksheets and found an error.

With Cells(7, 1)
Select Case ActiveSheet.Name
Case "Investment Prices & Returns"
.Value = Range("A7").Value (The macro I run doesn't find a value here)
Case Else
.Value = Date - 4 (So it puts this value in instead, which I don't want)
End Select
I have triple and quadruple checked and the sheet is named "Investment Prices & Returns", just like the code calls for. Just thinking that that might be the problem.

I am at a loss right now.

Sincerely,

Jamie L. Flora