Hi guys
My first post - my first excel 2000 macro.
I have been reading and searching for a while now, but have had no luck, getting desparate now, can anyone help me complete my "simple" macro?
I thought by recording the bulk of the code, I would then be able to edit in vba, to add the finishing touches.
What I have is two worksheets, the first is split into a number of diffirent sections of data.
Each section is identical, in that, it has a small table of static data, with a nos of rows of data (which use the table above for calculation purposes) below it.
I have tried recording a "relative" macro.
From the starting position in the table, the macro goes down to the first cell in the row of data, then names the cell, before working across the row, naming the cells, until it reaches the end of the row. It then prints a form on worksheet 2 which has formulae referencing the named cells on worksheet 1. After printing the form, the macro returns to the first cell in the next row of worksheet 1.
My problem is, how do I get the macro to repeat the instructions until such time the first cell in the row does not contain any data, when it would then stop.
I find the "looping" code confusing.
I also thought by recording the macro as "relative", by starting from the same relevant cell in each of the diffirent sections that it would print the data from the rows within each diffirent section.
Surprise surprise, when testing each section, it just prints the same cells from the first section.
I have copied below, a few of the lines of code from the start, and the end of the macro, because I don't think I have probably explained my problem very well.
I hope someone out there can point me in the right direction.
Great board by the way ( no i'm not sucking up )
Any tips on good books on vba, I'm in UK.
cheers
Ian
Scotland
. . .
Application.ScreenUpdating = False
ActiveCell.Offset(12, 0).Range("A1").Select
ActiveWorkbook.Names.Add Name:="Name", RefersToR1C1:="='Sheet 1'!R19C1"
ActiveCell.Offset(0, 4).Range("A1").Select
ActiveWorkbook.Names.Add Name:="Annual_Basic_Pay", RefersToR1C1:= _
"='Sheet 1'!R19C5"
ActiveCell.Offset(0, 3).Range("A1").Select
ActiveWorkbook.Names.Add Name:="Hypo_Tax", RefersToR1C1:="='Sheet 1'!R19C8"
'ActiveWindow.SmallScroll ToRight:=7
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveWorkbook.Names.Add Name:="Annual_Hypo_Net", RefersToR1C1:= _
"='Sheet 1'!R19C9"
. . . . . . . . . . .
ActiveCell.Offset(0, 3).Range("A1").Select
ActiveWorkbook.Names.Add Name:="Car_Euro", RefersToR1C1:= _
"='Sheet 1'!R19C55"
Sheets("Sheet 2").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Sheet 1").Select
ActiveCell.Offset(1, -54).Range("A1").Select
End Sub
My first post - my first excel 2000 macro.
I have been reading and searching for a while now, but have had no luck, getting desparate now, can anyone help me complete my "simple" macro?
I thought by recording the bulk of the code, I would then be able to edit in vba, to add the finishing touches.
What I have is two worksheets, the first is split into a number of diffirent sections of data.
Each section is identical, in that, it has a small table of static data, with a nos of rows of data (which use the table above for calculation purposes) below it.
I have tried recording a "relative" macro.
From the starting position in the table, the macro goes down to the first cell in the row of data, then names the cell, before working across the row, naming the cells, until it reaches the end of the row. It then prints a form on worksheet 2 which has formulae referencing the named cells on worksheet 1. After printing the form, the macro returns to the first cell in the next row of worksheet 1.
My problem is, how do I get the macro to repeat the instructions until such time the first cell in the row does not contain any data, when it would then stop.
I find the "looping" code confusing.
I also thought by recording the macro as "relative", by starting from the same relevant cell in each of the diffirent sections that it would print the data from the rows within each diffirent section.
Surprise surprise, when testing each section, it just prints the same cells from the first section.
I have copied below, a few of the lines of code from the start, and the end of the macro, because I don't think I have probably explained my problem very well.
I hope someone out there can point me in the right direction.
Great board by the way ( no i'm not sucking up )
Any tips on good books on vba, I'm in UK.
cheers
Ian
Scotland
. . .
Application.ScreenUpdating = False
ActiveCell.Offset(12, 0).Range("A1").Select
ActiveWorkbook.Names.Add Name:="Name", RefersToR1C1:="='Sheet 1'!R19C1"
ActiveCell.Offset(0, 4).Range("A1").Select
ActiveWorkbook.Names.Add Name:="Annual_Basic_Pay", RefersToR1C1:= _
"='Sheet 1'!R19C5"
ActiveCell.Offset(0, 3).Range("A1").Select
ActiveWorkbook.Names.Add Name:="Hypo_Tax", RefersToR1C1:="='Sheet 1'!R19C8"
'ActiveWindow.SmallScroll ToRight:=7
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveWorkbook.Names.Add Name:="Annual_Hypo_Net", RefersToR1C1:= _
"='Sheet 1'!R19C9"
. . . . . . . . . . .
ActiveCell.Offset(0, 3).Range("A1").Select
ActiveWorkbook.Names.Add Name:="Car_Euro", RefersToR1C1:= _
"='Sheet 1'!R19C55"
Sheets("Sheet 2").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Sheet 1").Select
ActiveCell.Offset(1, -54).Range("A1").Select
End Sub