Forum Home
Press F1
 
Thread ID: 36252 2003-08-04 06:17:00 This Excel97 Macro doesn't work Woof (2402) Press F1
Post ID Timestamp Content User
165181 2003-08-04 06:17:00 I have a small spreadsheet listing of debtors aged trial balance totals with the aging for each given month on a separate row (Date, Total, Current, 1 mth, 2 mth, etc)

Below that the spreadheet ' mirrors ' those cells to provide a calculation as to the % of the total per aging

I ' m trying to create a macro to use for updating with a new month ' s figures so I want it to insert fresh cells in the top ' table ' , copy the cells (some have calculations) from the previous row into these new cells, put zero values into the value filds and then do the same thing for the lower (%) table but without putting in zeros because they are all calculations) before returning to the top table ' s new row first cell ready to input the figures for the new month.

But the second copy and paste (for the % table) does NOT occur and I cannot see why.

Any helpful comments appreciated, please

Here is the macro :-


Sub DRSstatsNewRow()
'
' DRSstatsNewRow Macro
' Macro recorded 4/08/2003 by .
'
' Keyboard Shortcut: Ctrl+r
'
Range("A6:I6").Select
Selection.Insert Shift:=xlDown
Range("A7:I7").Select
Selection.Copy
Range("A6").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A6").Select
ActiveCell.FormulaR1C1 = "1/0/1900"
Range("B6").Select
ActiveCell.FormulaR1C1 = "0"
Range("E6").Select
ActiveCell.FormulaR1C1 = "0"
Range("F6").Select
ActiveCell.FormulaR1C1 = "0"
Range("G6").Select
ActiveCell.FormulaR1C1 = "0"
Range("H6").Select
ActiveCell.FormulaR1C1 = "0"
Range("I6").Select
ActiveCell.FormulaR1C1 = "0"
Range("A28:I28").Select
Selection.Insert Shift:=xlDown
Range("A29:I29").Select
Selection.Copy
Range("A28").Select
ActiveSheet.Paste
Range("A6").Select
Application.CutCopyMode = False
End Sub 		Woof (2402)
165182 2003-08-04 12:42:00 Hi, Im not entirely sure what you want to achieve by pasting in those rows as I would use a master sheet myself or use group outlining .

Anyways, onto your question - do you want to insert the date of 1/0/99 & fill rest with blanks for row 28?

If so try this . . .


Sub DRSstatsNewRow()
'
' DRSstatsNewRow Macro
' Macro recorded 4/08/2003 by .
'
' Keyboard Shortcut: Ctrl+r
'
Range("A6:I6") . Select
Selection . Insert Shift:=xlDown
Range("A7:I7") . Select
Selection . Copy
Range("A6") . Select
ActiveSheet . Paste
Application . CutCopyMode = False
Range("A6") . Value = "1/0/1900"
Range("B6:I6") . Value = 0
Range("A28:I28") . Select
Selection . Insert Shift:=xlDown
Range("A28") . Value = "1/0/1900"
Range("A6") . Select
Application . CutCopyMode = False
End Sub


hth 		parry (27)
165183 2003-08-04 12:46:00 mmm for some reason the code didnt paste here properly. The line Range("A6").Select< BR>ActiveSheet.Paste is actually two lines ...

Range("A6").Select
ActiveSheet.Paste 		parry (27)
165184 2003-08-05 10:42:00 Hi there
Don't know if this will be of help but, did you write the macro, or record it . I have found that the best way to put these together is to record the macro the way you want to happen, even if you do it in stages then cut and paste them together .

Also

ActiveSheet . Paste
Range("A6") . Select
Application . CutCopyMode = False
End Sub

should that be

ActiveSheet . Paste
Application . CutCopyMode = False
Range("A6") . Select
End Sub

like in the first copy step, or is it OK .

Cheers
Craigb 		Craigb (688)
1