How to get the macro recorded referentially

jkpd2000 · May 17th, 2002, 01:18 PM

I have a great problem. I am recording a macro. I am finding a empty cell and filling it with the data.

next time when i run the macro , it is finding the empty cell correctly , but filling the same old cell which is already filled.

i want the data filled in the empty cell when ever i run that macro. it should advance for each run.

[ This Message was edited by: jkpd2000 on 2002-05-17 12:27 ]

Mudface · May 17th, 2002, 01:21 PM

Could you post up your current code, or the part that's giving you trouble if it's a bit long. It might just be a case of getting the next empty cell, but I can't tell from your original post.

jkpd2000 · May 17th, 2002, 01:25 PM

Workbooks.Open Filename:= _
"C:pouninv_17_May_02.xls"
Workbooks.Open Filename:="master_pouniv.xls"
Sheets("Uninvoice Receipts Tracker (2)").Copy After:=Workbooks( _
"pouninv_17_May_02.xls").Sheets(2)
Windows("master_pouniv.xls").Activate
ActiveWindow.Close
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
ActiveSheet.Next.Select
ActiveSheet.Next.Select
Range("A1").Select
Range(Selection, Cells(ActiveCell.Row, 1)).Select
Cells.Find(What:="purchased", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
.Activate
Selection.Style = "Comma"
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
ActiveCell.FormulaR1C1 = "=summary!R[5]C[-3]"
Range("K5").Select
ActiveCell.FormulaR1C1 = "=summary!R[3]C[-3]"
Range("K7").Select
Sheets("summary").Select
Range("H19").Select
Sheets("Uninvoice Receipts Tracker (2)").Select
ActiveCell.FormulaR1C1 = "=summary!R[12]C[-3]"
Range("K6").Select
ActiveCell.FormulaR1C1 = "=R[1]C-R[-1]C-R[-2]C"
Range("K10").Select
ActiveCell.FormulaR1C1 = "=summary!R[-1]C[-1]"
Range("K11").Select
ActiveCell.FormulaR1C1 = "=summary!R[-3]C[-1]"
Range("K13").Select
ActiveCell.FormulaR1C1 = "=summary!R[6]C[-1]"
Range("K12").Select
ActiveCell.FormulaR1C1 = "=R[1]C-R[-1]C-R[-2]C"
Range("J14").Select
Selection.Copy
Range("K14").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("K18").Select
ActiveCell.FormulaR1C1 = "=summary!R[-9]C"
Range("K19").Select
ActiveCell.FormulaR1C1 = "=summary!R[-11]C"
Range("K21").Select
ActiveCell.FormulaR1C1 = "=summary!R[-2]C"
Range("K20").Select
ActiveCell.FormulaR1C1 = "=R[1]C-R[-1]C-R[-2]C"
Range("J22").Select
Selection.Copy
Range("K22").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A2").Select
ActiveWorkbook.Save
Range("A2").Select
ActiveWorkbook.SaveAs Filename:= _
"C:master_pouniv.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Sheets("pouninv").Select
ActiveWindow.SelectedSheets.Delete
Sheets("summary").Select
ActiveWindow.SelectedSheets.Delete
Range("A1").Select
ActiveWorkbook.Save
ActiveWindow.Close

Mudface · May 17th, 2002, 01:56 PM

Right, as far as I can make out you're doing the following: -

1. Opening up two workbooks, copying a sheet from one of them to the master, closing it and selecting the sheet you just copied (I think).

2. Finding the word 'purchased' then a blank, selecting the blank cell and applying a format to it.

So far so good, we can discount the first bits, although you could certainly edit out some of the extraneous bits that the macro recorder has put in for you.

3. You're then putting in a few formulas and linking cells from the summary sheet to the originally copied sheet. This is where I'm having problems following exactly what you want and where you want the formulas and copied data to go. Do you want the next set of data to go into the next column after the first set, or one row down? Bit confused I'm afraid.

jkpd2000 · May 17th, 2002, 03:49 PM

You got it right Mud.

say the data is like this
a--b--c--d
1--1
1--1
first run it should fill the c column
and next run it should fill d column

after first run
a--b--c--d
1--1--1
1--1--1

after next run
a--b--c--d
1--1--1--1
1--1--1--1

P.S. -- used instead of space.

[ This Message was edited by: jkpd2000 on 2002-05-17 14:51 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 17th, 2002, 01:18 PM	#1
jkpd2000 Board Regular Join Date: May 2002 Posts: 91	I have a great problem. I am recording a macro. I am finding a empty cell and filling it with the data. next time when i run the macro , it is finding the empty cell correctly , but filling the same old cell which is already filled. i want the data filled in the empty cell when ever i run that macro. it should advance for each run. [ This Message was edited by: jkpd2000 on 2002-05-17 12:27 ]

May 17th, 2002, 01:21 PM	#2
Mudface MrExcel MVP Join Date: Feb 2002 Location: Sunny, spring-like Hull Posts: 3,339	Could you post up your current code, or the part that's giving you trouble if it's a bit long. It might just be a case of getting the next empty cell, but I can't tell from your original post.

May 17th, 2002, 01:25 PM	#3
jkpd2000 Board Regular Join Date: May 2002 Posts: 91	Workbooks.Open Filename:= _ "C:pouninv_17_May_02.xls" Workbooks.Open Filename:="master_pouniv.xls" Sheets("Uninvoice Receipts Tracker (2)").Copy After:=Workbooks( _ "pouninv_17_May_02.xls").Sheets(2) Windows("master_pouniv.xls").Activate ActiveWindow.Close ActiveSheet.Previous.Select ActiveSheet.Previous.Select ActiveSheet.Next.Select ActiveSheet.Next.Select Range("A1").Select Range(Selection, Cells(ActiveCell.Row, 1)).Select Cells.Find(What:="purchased", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Activate Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _ .Activate Selection.Style = "Comma" With Selection.Font .Name = "Arial" .FontStyle = "Regular" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)" ActiveCell.FormulaR1C1 = "=summary!R[5]C[-3]" Range("K5").Select ActiveCell.FormulaR1C1 = "=summary!R[3]C[-3]" Range("K7").Select Sheets("summary").Select Range("H19").Select Sheets("Uninvoice Receipts Tracker (2)").Select ActiveCell.FormulaR1C1 = "=summary!R[12]C[-3]" Range("K6").Select ActiveCell.FormulaR1C1 = "=R[1]C-R[-1]C-R[-2]C" Range("K10").Select ActiveCell.FormulaR1C1 = "=summary!R[-1]C[-1]" Range("K11").Select ActiveCell.FormulaR1C1 = "=summary!R[-3]C[-1]" Range("K13").Select ActiveCell.FormulaR1C1 = "=summary!R[6]C[-1]" Range("K12").Select ActiveCell.FormulaR1C1 = "=R[1]C-R[-1]C-R[-2]C" Range("J14").Select Selection.Copy Range("K14").Select ActiveSheet.Paste Application.CutCopyMode = False Range("K18").Select ActiveCell.FormulaR1C1 = "=summary!R[-9]C" Range("K19").Select ActiveCell.FormulaR1C1 = "=summary!R[-11]C" Range("K21").Select ActiveCell.FormulaR1C1 = "=summary!R[-2]C" Range("K20").Select ActiveCell.FormulaR1C1 = "=R[1]C-R[-1]C-R[-2]C" Range("J22").Select Selection.Copy Range("K22").Select ActiveSheet.Paste Application.CutCopyMode = False Range("A2").Select ActiveWorkbook.Save Range("A2").Select ActiveWorkbook.SaveAs Filename:= _ "C:master_pouniv.xls", FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False Sheets("pouninv").Select ActiveWindow.SelectedSheets.Delete Sheets("summary").Select ActiveWindow.SelectedSheets.Delete Range("A1").Select ActiveWorkbook.Save ActiveWindow.Close

May 17th, 2002, 01:56 PM	#4
Mudface MrExcel MVP Join Date: Feb 2002 Location: Sunny, spring-like Hull Posts: 3,339	Right, as far as I can make out you're doing the following: - 1. Opening up two workbooks, copying a sheet from one of them to the master, closing it and selecting the sheet you just copied (I think). 2. Finding the word 'purchased' then a blank, selecting the blank cell and applying a format to it. So far so good, we can discount the first bits, although you could certainly edit out some of the extraneous bits that the macro recorder has put in for you. 3. You're then putting in a few formulas and linking cells from the summary sheet to the originally copied sheet. This is where I'm having problems following exactly what you want and where you want the formulas and copied data to go. Do you want the next set of data to go into the next column after the first set, or one row down? Bit confused I'm afraid.

May 17th, 2002, 03:49 PM	#5
jkpd2000 Board Regular Join Date: May 2002 Posts: 91	You got it right Mud. say the data is like this a--b--c--d 1--1 1--1 first run it should fill the c column and next run it should fill d column after first run a--b--c--d 1--1--1 1--1--1 after next run a--b--c--d 1--1--1--1 1--1--1--1 P.S. -- used instead of space. [ This Message was edited by: jkpd2000 on 2002-05-17 14:51 ]