Change data with a macro

U

Unique713

I need some sort of format for a macro that i created. All I need it to do is
move to the next row anytime it is run. For example, if my data is in d4 i
want the macro to move to and insert the data from the following row, d5. And
then the next time it is run it will do the same thing and insert the
information from d6. I only need to change the information where there is a
title (see macro). The macro is being used in a worbook that has mulitple
pages and certain peices of information is being pulled from each page onto
one summary page. Please help me! its for a project I have been working on
for 2 months and i still cant figure it out! here is a copy of the macro.


Range("C1").Select
ActiveCell.FormulaR1C1 = "=Master!R[3]C"
Range("C2").Select
Range("E4").Select
ActiveCell.FormulaR1C1 = "='Riverview East Owners'!RC[-1]"
Range("E5").Select
ActiveCell.FormulaR1C1 = "='Liberty House Condominium'!R[-1]C[-1]"
Range("E6").Select
ActiveCell.FormulaR1C1 = "='Liberty Terrace'!R[-2]C[-1]"
Range("E7").Select
ActiveCell.FormulaR1C1 = "='Liberty Court'!R[-3]C[-1]"
Range("E8").Select
ActiveCell.FormulaR1C1 = "='Liberty View'!R[-4]C[-1]"
Range("E9").Select
ActiveCell.FormulaR1C1 = "='200-210 E. 65th St.'!R[-5]C[-1]"
Range("E10").Select
ActiveCell.FormulaR1C1 = "='761-799 7th Ave. '!R[-6]C[-1]"
Range("E11").Select
ActiveCell.FormulaR1C1 = "='Executive Condominium'!R[-7]C[-1]"
Range("E12").Select
ActiveCell.FormulaR1C1 = "='Cove Club'!R[-8]C[-1]"
Range("E13").Select
ActiveCell.FormulaR1C1 = "='Greenwhich Court'!R[-9]C[-1]"
Range("E14").Select
ActiveCell.FormulaR1C1 = "='The Regatta'!R[-10]C[-1]"
Range("E15").Select
ActiveCell.FormulaR1C1 = "='The Soundings'!R[-11]C[-1]"
Range("F4").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='Riverview East Owners'!RC[7]"
Range("F5").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='Liberty House Condominium'!R[-1]C[7]"
Range("F6").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='Liberty Terrace'!R[-2]C[7]"
Range("F7").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='Liberty Court'!R[-3]C[7]"
Range("F8").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='Liberty View'!R[-4]C[7]"
Range("F9").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='200-210 E. 65th St.'!R[-5]C[7]"
Range("F10").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='761-799 7th Ave. '!R[-6]C[7]"
Range("F11").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='Executive Condominium'!R[-7]C[7]"
Range("F12").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='Cove Club'!R[-8]C[7]"
Range("F13").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='Greenwhich Court'!R[-9]C[7]"
Range("F14").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='The Regatta'!R[-10]C[7]"
Range("F15").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='The Soundings'!R[-11]C[7]"
Range("G4").Select
Selection.FillRight
Range("G5").Select
Selection.FillRight
Range("G6").Select
Selection.FillRight
Range("G7").Select
Selection.FillRight
Range("G8").Select
Selection.FillRight
Range("G9").Select
Selection.FillRight
Range("G10").Select
Selection.FillRight
Range("G11").Select
Selection.FillRight
Range("G12").Select
Selection.FillRight
Range("G13").Select
Selection.FillRight
Range("G14").Select
Selection.FillRight
Range("G15").Select
Selection.FillRight
Range("H4").Select
Selection.FillRight
Range("H5").Select
Selection.FillRight
Range("H6").Select
Selection.FillRight
Range("H7").Select
Selection.FillRight
Range("H8").Select
Selection.FillRight
Range("H9").Select
Selection.FillRight
Range("H10").Select
Selection.FillRight
Range("H11").Select
Selection.FillRight
Range("H12").Select
Selection.FillRight
Range("H13").Select
Selection.FillRight
Range("H14").Select
Selection.FillRight
Range("H15").Select
Selection.FillRight
Range("J4").Select
ActiveCell.FormulaR1C1 = "='Riverview East Owners'!RC[-1]"
Range("J5").Select
ActiveCell.FormulaR1C1 = "='Liberty House Condominium'!R[-1]C[-1]"
Range("J6").Select
ActiveCell.FormulaR1C1 = "='Liberty Terrace'!R[-2]C[-1]"
Range("J7").Select
ActiveCell.FormulaR1C1 = "='Liberty Court'!R[-3]C[-1]"
Range("J8").Select
ActiveCell.FormulaR1C1 = "='Liberty View'!R[-4]C[-1]"
Range("J9").Select
ActiveCell.FormulaR1C1 = "='200-210 E. 65th St.'!R[-5]C[-1]"
Range("J10").Select
ActiveCell.FormulaR1C1 = "='761-799 7th Ave. '!R[-6]C[-1]"
Range("J11").Select
ActiveCell.FormulaR1C1 = "='Executive Condominium'!R[-7]C[-1]"
Range("J12").Select
ActiveCell.FormulaR1C1 = "='Cove Club'!R[-8]C[-1]"
Range("J13").Select
ActiveCell.FormulaR1C1 = "='Greenwhich Court'!R[-9]C[-1]"
Range("J14").Select
ActiveCell.FormulaR1C1 = "='The Regatta'!R[-10]C[-1]"
Range("J15").Select
ActiveCell.FormulaR1C1 = "='The Soundings'!R[-11]C[-1]"
Range("K4").Select
Selection.FillRight
Range("K5").Select
Selection.FillRight
Range("K6").Select
Selection.FillRight
Range("K7").Select
Selection.FillRight
Range("K8").Select
Selection.FillRight
Range("K9").Select
Selection.FillRight
Range("K10").Select
Selection.FillRight
Range("K11").Select
Selection.FillRight
Range("K12").Select
Selection.FillRight
Range("K13").Select
Selection.FillRight
Range("K14").Select
Selection.FillRight
Range("K15").Select
Selection.FillRight
Range("L15").Select
Selection.FillRight
Range("L14").Select
Selection.FillRight
Range("L13").Select
Selection.FillRight
Range("L12").Select
Selection.FillRight
Range("L11").Select
Selection.FillRight
Range("L10").Select
Selection.FillRight
Range("L9").Select
Selection.FillRight
Range("L8").Select
Selection.FillRight
Range("L7").Select
Selection.FillRight
Range("L6").Select
Selection.FillRight
Range("L5").Select
Selection.FillRight
Range("L4").Select
Selection.FillRight
Range("E19").Select
ActiveCell.FormulaR1C1 = "='30 Lincoln (S&P)'!R[-15]C[-1]"
Range("E20").Select
ActiveCell.FormulaR1C1 = "='The Highgate (P&S)'!R[-16]C[-1]"
Range("E21").Select
ActiveCell.FormulaR1C1 = "='Milro Associates'!R[-17]C[-1]"
Range("E22").Select
ActiveCell.FormulaR1C1 = "='The Claridge House (P&H)'!R[-18]C[-1]"
Range("E23").Select
ActiveCell.FormulaR1C1 = "='Courtney Associates'!R[-19]C[-1]"
Range("E24").Select
ActiveCell.FormulaR1C1 = "='Stonehenge Project'!R[-20]C[-1]"
Range("F19").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='30 Lincoln (S&P)'!R[-15]C[7]"
Range("F20").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='The Highgate (P&S)'!R[-16]C[7]"
Range("F21").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='Milro Associates'!R[-17]C[7]"
Range("F22").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='The Claridge House (P&H)'!R[-18]C[7]"
Range("F23").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='Courtney Associates'!R[-19]C[7]"
Range("F24").Select
ActiveCell.FormulaR1C1 = "='M:\[Payroll 2009.xls]Stonehenge
Project'!R[-20]C"
Range("F24").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='Stonehenge Project'!R[-20]C[7]"
Range("G19").Select
Selection.FillRight
Range("G20").Select
Selection.FillRight
Range("G21").Select
Selection.FillRight
Range("G22").Select
Selection.FillRight
Range("G23").Select
Selection.FillRight
Range("G24").Select
Selection.FillRight
Range("H24").Select
Selection.FillRight
Range("H23").Select
Selection.FillRight
Range("H22").Select
Selection.FillRight
Range("H21").Select
Selection.FillRight
Range("H20").Select
Selection.FillRight
Range("H19").Select
Selection.FillRight
Range("J19").Select
ActiveCell.FormulaR1C1 = "='30 Lincoln (S&P)'!R[-15]C[-1]"
Range("J20").Select
ActiveCell.FormulaR1C1 = "='The Highgate (P&S)'!R[-16]C[-1]"
Range("J21").Select
ActiveCell.FormulaR1C1 = "='Milro Associates'!R[-17]C[-1]"
Range("J22").Select
ActiveCell.FormulaR1C1 = "='The Claridge House (P&H)'!R[-18]C[-1]"
Range("J23").Select
ActiveCell.FormulaR1C1 = "='Courtney Associates'!R[-19]C[-1]"
Range("J24").Select
ActiveCell.FormulaR1C1 = "='Stonehenge Project'!R[-20]C[-1]"
Range("K19").Select
Selection.FillRight
Range("K20").Select
Selection.FillRight
Range("K21").Select
Selection.FillRight
Range("K22").Select
Selection.FillRight
Range("K23").Select
Selection.FillRight
Range("K24").Select
Selection.FillRight
Range("L19").Select
Selection.FillRight
Range("L20").Select
Selection.FillRight
Range("L21").Select
Selection.FillRight
Range("L22").Select
Selection.FillRight
Range("L23").Select
Selection.FillRight
Range("L24").Select
Selection.FillRight
Range("E29").Select
ActiveCell.FormulaR1C1 = "='Milford Mgt. Corp.'!R[-25]C[-1]"
Range("E30").Select
ActiveCell.FormulaR1C1 = "='Milford Consultants'!R[-26]C[-1]"
Range("F29").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='Milford Mgt. Corp.'!R[-25]C[7]"
Range("F30").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='Milford Consultants'!R[-26]C[6]"
Range("G29").Select
Selection.FillRight
Range("G30").Select
Selection.FillRight
Range("H29").Select
Selection.FillRight
Range("H30").Select
Selection.FillRight
Range("J30").Select
ActiveCell.FormulaR1C1 = "='Milford Consultants'!R[-26]C[-1]"
Range("L29").Select
ActiveCell.FormulaR1C1 = "='Milford Mgt. Corp.'!R[-25]C[-1]"
Range("L30").Select
ActiveCell.FormulaR1C1 = "='Milford Consultants'!R[-26]C[-2]"
Range("D1").Select
 
D

Dave Peterson

You have tons of stuff going on. Some of it in column E, F, G, ..., L.

I don't see anything that is plopped into D4.

Maybe this will help (or not!).

If you select the first receiving cell (it looks like C1 in your code), you
could use:

with activesheet.range("C1")
.formular1c1 = "=master!r[3]c"

'E4 is 2 to the right and 3 down (that's the offset portion)
.offset(2, 3).formular1c1 = "='Riverview East Owners'!RC[-1]"

'E5 is 2 to the right and 4 down
.offset(2, 4).formular1c1 = "='Liberty House Condominium'!R[-1]C[-1]"

......

End with

This expects that the starting cell is selected and all the formulas stay the
same.

Dropping this kind of stuff:
Range("E6").Select
ActiveCell.FormulaR1C1 = "='Liberty Terrace'!R[-2]C[-1]" and replacing it with:
.offset(2, 5).formulaR1C1 = "='Liberty Terrace'!R[-2]C[-1]"
Click to expand...

makes things a lot easier to update/check.


I need some sort of format for a macro that i created. All I need it to do is
move to the next row anytime it is run. For example, if my data is in d4 i
want the macro to move to and insert the data from the following row, d5. And
then the next time it is run it will do the same thing and insert the
information from d6. I only need to change the information where there is a
title (see macro). The macro is being used in a worbook that has mulitple
pages and certain peices of information is being pulled from each page onto
one summary page. Please help me! its for a project I have been working on
for 2 months and i still cant figure it out! here is a copy of the macro.

Range("C1").Select
ActiveCell.FormulaR1C1 = "=Master!R[3]C"
Range("C2").Select
Range("E4").Select
ActiveCell.FormulaR1C1 = "='Riverview East Owners'!RC[-1]"
Range("E5").Select
ActiveCell.FormulaR1C1 = "='Liberty House Condominium'!R[-1]C[-1]"
Range("E6").Select
ActiveCell.FormulaR1C1 = "='Liberty Terrace'!R[-2]C[-1]"
Range("E7").Select
ActiveCell.FormulaR1C1 = "='Liberty Court'!R[-3]C[-1]"
Range("E8").Select
ActiveCell.FormulaR1C1 = "='Liberty View'!R[-4]C[-1]"
Range("E9").Select
ActiveCell.FormulaR1C1 = "='200-210 E. 65th St.'!R[-5]C[-1]"
Range("E10").Select
ActiveCell.FormulaR1C1 = "='761-799 7th Ave. '!R[-6]C[-1]"
Range("E11").Select
ActiveCell.FormulaR1C1 = "='Executive Condominium'!R[-7]C[-1]"
Range("E12").Select
ActiveCell.FormulaR1C1 = "='Cove Club'!R[-8]C[-1]"
Range("E13").Select
ActiveCell.FormulaR1C1 = "='Greenwhich Court'!R[-9]C[-1]"
Range("E14").Select
ActiveCell.FormulaR1C1 = "='The Regatta'!R[-10]C[-1]"
Range("E15").Select
ActiveCell.FormulaR1C1 = "='The Soundings'!R[-11]C[-1]"
Range("F4").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='Riverview East Owners'!RC[7]"
Range("F5").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='Liberty House Condominium'!R[-1]C[7]"
Range("F6").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='Liberty Terrace'!R[-2]C[7]"
Range("F7").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='Liberty Court'!R[-3]C[7]"
Range("F8").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='Liberty View'!R[-4]C[7]"
Range("F9").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='200-210 E. 65th St.'!R[-5]C[7]"
Range("F10").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='761-799 7th Ave. '!R[-6]C[7]"
Range("F11").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='Executive Condominium'!R[-7]C[7]"
Range("F12").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='Cove Club'!R[-8]C[7]"
Range("F13").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='Greenwhich Court'!R[-9]C[7]"
Range("F14").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='The Regatta'!R[-10]C[7]"
Range("F15").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='The Soundings'!R[-11]C[7]"
Range("G4").Select
Selection.FillRight
Range("G5").Select
Selection.FillRight
Range("G6").Select
Selection.FillRight
Range("G7").Select
Selection.FillRight
Range("G8").Select
Selection.FillRight
Range("G9").Select
Selection.FillRight
Range("G10").Select
Selection.FillRight
Range("G11").Select
Selection.FillRight
Range("G12").Select
Selection.FillRight
Range("G13").Select
Selection.FillRight
Range("G14").Select
Selection.FillRight
Range("G15").Select
Selection.FillRight
Range("H4").Select
Selection.FillRight
Range("H5").Select
Selection.FillRight
Range("H6").Select
Selection.FillRight
Range("H7").Select
Selection.FillRight
Range("H8").Select
Selection.FillRight
Range("H9").Select
Selection.FillRight
Range("H10").Select
Selection.FillRight
Range("H11").Select
Selection.FillRight
Range("H12").Select
Selection.FillRight
Range("H13").Select
Selection.FillRight
Range("H14").Select
Selection.FillRight
Range("H15").Select
Selection.FillRight
Range("J4").Select
ActiveCell.FormulaR1C1 = "='Riverview East Owners'!RC[-1]"
Range("J5").Select
ActiveCell.FormulaR1C1 = "='Liberty House Condominium'!R[-1]C[-1]"
Range("J6").Select
ActiveCell.FormulaR1C1 = "='Liberty Terrace'!R[-2]C[-1]"
Range("J7").Select
ActiveCell.FormulaR1C1 = "='Liberty Court'!R[-3]C[-1]"
Range("J8").Select
ActiveCell.FormulaR1C1 = "='Liberty View'!R[-4]C[-1]"
Range("J9").Select
ActiveCell.FormulaR1C1 = "='200-210 E. 65th St.'!R[-5]C[-1]"
Range("J10").Select
ActiveCell.FormulaR1C1 = "='761-799 7th Ave. '!R[-6]C[-1]"
Range("J11").Select
ActiveCell.FormulaR1C1 = "='Executive Condominium'!R[-7]C[-1]"
Range("J12").Select
ActiveCell.FormulaR1C1 = "='Cove Club'!R[-8]C[-1]"
Range("J13").Select
ActiveCell.FormulaR1C1 = "='Greenwhich Court'!R[-9]C[-1]"
Range("J14").Select
ActiveCell.FormulaR1C1 = "='The Regatta'!R[-10]C[-1]"
Range("J15").Select
ActiveCell.FormulaR1C1 = "='The Soundings'!R[-11]C[-1]"
Range("K4").Select
Selection.FillRight
Range("K5").Select
Selection.FillRight
Range("K6").Select
Selection.FillRight
Range("K7").Select
Selection.FillRight
Range("K8").Select
Selection.FillRight
Range("K9").Select
Selection.FillRight
Range("K10").Select
Selection.FillRight
Range("K11").Select
Selection.FillRight
Range("K12").Select
Selection.FillRight
Range("K13").Select
Selection.FillRight
Range("K14").Select
Selection.FillRight
Range("K15").Select
Selection.FillRight
Range("L15").Select
Selection.FillRight
Range("L14").Select
Selection.FillRight
Range("L13").Select
Selection.FillRight
Range("L12").Select
Selection.FillRight
Range("L11").Select
Selection.FillRight
Range("L10").Select
Selection.FillRight
Range("L9").Select
Selection.FillRight
Range("L8").Select
Selection.FillRight
Range("L7").Select
Selection.FillRight
Range("L6").Select
Selection.FillRight
Range("L5").Select
Selection.FillRight
Range("L4").Select
Selection.FillRight
Range("E19").Select
ActiveCell.FormulaR1C1 = "='30 Lincoln (S&P)'!R[-15]C[-1]"
Range("E20").Select
ActiveCell.FormulaR1C1 = "='The Highgate (P&S)'!R[-16]C[-1]"
Range("E21").Select
ActiveCell.FormulaR1C1 = "='Milro Associates'!R[-17]C[-1]"
Range("E22").Select
ActiveCell.FormulaR1C1 = "='The Claridge House (P&H)'!R[-18]C[-1]"
Range("E23").Select
ActiveCell.FormulaR1C1 = "='Courtney Associates'!R[-19]C[-1]"
Range("E24").Select
ActiveCell.FormulaR1C1 = "='Stonehenge Project'!R[-20]C[-1]"
Range("F19").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='30 Lincoln (S&P)'!R[-15]C[7]"
Range("F20").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='The Highgate (P&S)'!R[-16]C[7]"
Range("F21").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='Milro Associates'!R[-17]C[7]"
Range("F22").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='The Claridge House (P&H)'!R[-18]C[7]"
Range("F23").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='Courtney Associates'!R[-19]C[7]"
Range("F24").Select
ActiveCell.FormulaR1C1 = "='M:\[Payroll 2009.xls]Stonehenge
Project'!R[-20]C"
Range("F24").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='Stonehenge Project'!R[-20]C[7]"
Range("G19").Select
Selection.FillRight
Range("G20").Select
Selection.FillRight
Range("G21").Select
Selection.FillRight
Range("G22").Select
Selection.FillRight
Range("G23").Select
Selection.FillRight
Range("G24").Select
Selection.FillRight
Range("H24").Select
Selection.FillRight
Range("H23").Select
Selection.FillRight
Range("H22").Select
Selection.FillRight
Range("H21").Select
Selection.FillRight
Range("H20").Select
Selection.FillRight
Range("H19").Select
Selection.FillRight
Range("J19").Select
ActiveCell.FormulaR1C1 = "='30 Lincoln (S&P)'!R[-15]C[-1]"
Range("J20").Select
ActiveCell.FormulaR1C1 = "='The Highgate (P&S)'!R[-16]C[-1]"
Range("J21").Select
ActiveCell.FormulaR1C1 = "='Milro Associates'!R[-17]C[-1]"
Range("J22").Select
ActiveCell.FormulaR1C1 = "='The Claridge House (P&H)'!R[-18]C[-1]"
Range("J23").Select
ActiveCell.FormulaR1C1 = "='Courtney Associates'!R[-19]C[-1]"
Range("J24").Select
ActiveCell.FormulaR1C1 = "='Stonehenge Project'!R[-20]C[-1]"
Range("K19").Select
Selection.FillRight
Range("K20").Select
Selection.FillRight
Range("K21").Select
Selection.FillRight
Range("K22").Select
Selection.FillRight
Range("K23").Select
Selection.FillRight
Range("K24").Select
Selection.FillRight
Range("L19").Select
Selection.FillRight
Range("L20").Select
Selection.FillRight
Range("L21").Select
Selection.FillRight
Range("L22").Select
Selection.FillRight
Range("L23").Select
Selection.FillRight
Range("L24").Select
Selection.FillRight
Range("E29").Select
ActiveCell.FormulaR1C1 = "='Milford Mgt. Corp.'!R[-25]C[-1]"
Range("E30").Select
ActiveCell.FormulaR1C1 = "='Milford Consultants'!R[-26]C[-1]"
Range("F29").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='Milford Mgt. Corp.'!R[-25]C[7]"
Range("F30").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='Milford Consultants'!R[-26]C[6]"
Range("G29").Select
Selection.FillRight
Range("G30").Select
Selection.FillRight
Range("H29").Select
Selection.FillRight
Range("H30").Select
Selection.FillRight
Range("J30").Select
ActiveCell.FormulaR1C1 = "='Milford Consultants'!R[-26]C[-1]"
Range("L29").Select
ActiveCell.FormulaR1C1 = "='Milford Mgt. Corp.'!R[-25]C[-1]"
Range("L30").Select
ActiveCell.FormulaR1C1 = "='Milford Consultants'!R[-26]C[-2]"
Range("D1").Select
Click to expand...
 
T

TomPl

There is, i believe, a better way to accomplish what you want to do and it
does not require the use of a macro.

On each of your data sheets, select the entire last row. Right click and
copy. Right click and "Insert Copied Cells". All of your formulas have been
changed to the next row. You only need to insert the correct new data in the
last row and you have preserved the previous data in the row above it.

You can also expedite this process by selecting all the the data sheets
before performing the inserted row and you will only have to do it once.
Don't forget to deselect the grouped sheets before correcting the data in the
last row.

I hope this makes sense and help you.

Tom
 
U

Unique713

The macro does what it needs to do. It pulls the information from the cells
that you see. I need to tweak it so that evertime it is run, it moves to the
following row of each of the sheets it is pulling the information from.
Example: the active sheet pulls information from the "master" sheet cell C4
and puts it into C1 on my active sheet. The next time it is run i need it to
still put the information into C1 but get the information from "master" sheet
cell C5. And then the next time it is run it will put the information from
"master" sheet cell C6 into my active sheet C1. The macro is supposed to
replace the information everytime it is run.

Dave Peterson said:
You have tons of stuff going on. Some of it in column E, F, G, ..., L.

I don't see anything that is plopped into D4.

Maybe this will help (or not!).

If you select the first receiving cell (it looks like C1 in your code), you
could use:

with activesheet.range("C1")
.formular1c1 = "=master!r[3]c"

'E4 is 2 to the right and 3 down (that's the offset portion)
.offset(2, 3).formular1c1 = "='Riverview East Owners'!RC[-1]"

'E5 is 2 to the right and 4 down
.offset(2, 4).formular1c1 = "='Liberty House Condominium'!R[-1]C[-1]"

......

End with

This expects that the starting cell is selected and all the formulas stay the
same.

Dropping this kind of stuff:
Range("E6").Select
ActiveCell.FormulaR1C1 = "='Liberty Terrace'!R[-2]C[-1]" and replacing it with:
.offset(2, 5).formulaR1C1 = "='Liberty Terrace'!R[-2]C[-1]"
Click to expand...

makes things a lot easier to update/check.


I need some sort of format for a macro that i created. All I need it to do is
move to the next row anytime it is run. For example, if my data is in d4 i
want the macro to move to and insert the data from the following row, d5. And
then the next time it is run it will do the same thing and insert the
information from d6. I only need to change the information where there is a
title (see macro). The macro is being used in a worbook that has mulitple
pages and certain peices of information is being pulled from each page onto
one summary page. Please help me! its for a project I have been working on
for 2 months and i still cant figure it out! here is a copy of the macro.

Range("C1").Select
ActiveCell.FormulaR1C1 = "=Master!R[3]C"
Range("C2").Select
Range("E4").Select
ActiveCell.FormulaR1C1 = "='Riverview East Owners'!RC[-1]"
Range("E5").Select
ActiveCell.FormulaR1C1 = "='Liberty House Condominium'!R[-1]C[-1]"
Range("E6").Select
ActiveCell.FormulaR1C1 = "='Liberty Terrace'!R[-2]C[-1]"
Range("E7").Select
ActiveCell.FormulaR1C1 = "='Liberty Court'!R[-3]C[-1]"
Range("E8").Select
ActiveCell.FormulaR1C1 = "='Liberty View'!R[-4]C[-1]"
Range("E9").Select
ActiveCell.FormulaR1C1 = "='200-210 E. 65th St.'!R[-5]C[-1]"
Range("E10").Select
ActiveCell.FormulaR1C1 = "='761-799 7th Ave. '!R[-6]C[-1]"
Range("E11").Select
ActiveCell.FormulaR1C1 = "='Executive Condominium'!R[-7]C[-1]"
Range("E12").Select
ActiveCell.FormulaR1C1 = "='Cove Club'!R[-8]C[-1]"
Range("E13").Select
ActiveCell.FormulaR1C1 = "='Greenwhich Court'!R[-9]C[-1]"
Range("E14").Select
ActiveCell.FormulaR1C1 = "='The Regatta'!R[-10]C[-1]"
Range("E15").Select
ActiveCell.FormulaR1C1 = "='The Soundings'!R[-11]C[-1]"
Range("F4").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='Riverview East Owners'!RC[7]"
Range("F5").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='Liberty House Condominium'!R[-1]C[7]"
Range("F6").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='Liberty Terrace'!R[-2]C[7]"
Range("F7").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='Liberty Court'!R[-3]C[7]"
Range("F8").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='Liberty View'!R[-4]C[7]"
Range("F9").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='200-210 E. 65th St.'!R[-5]C[7]"
Range("F10").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='761-799 7th Ave. '!R[-6]C[7]"
Range("F11").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='Executive Condominium'!R[-7]C[7]"
Range("F12").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='Cove Club'!R[-8]C[7]"
Range("F13").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='Greenwhich Court'!R[-9]C[7]"
Range("F14").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='The Regatta'!R[-10]C[7]"
Range("F15").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='The Soundings'!R[-11]C[7]"
Range("G4").Select
Selection.FillRight
Range("G5").Select
Selection.FillRight
Range("G6").Select
Selection.FillRight
Range("G7").Select
Selection.FillRight
Range("G8").Select
Selection.FillRight
Range("G9").Select
Selection.FillRight
Range("G10").Select
Selection.FillRight
Range("G11").Select
Selection.FillRight
Range("G12").Select
Selection.FillRight
Range("G13").Select
Selection.FillRight
Range("G14").Select
Selection.FillRight
Range("G15").Select
Selection.FillRight
Range("H4").Select
Selection.FillRight
Range("H5").Select
Selection.FillRight
Range("H6").Select
Selection.FillRight
Range("H7").Select
Selection.FillRight
Range("H8").Select
Selection.FillRight
Range("H9").Select
Selection.FillRight
Range("H10").Select
Selection.FillRight
Range("H11").Select
Selection.FillRight
Range("H12").Select
Selection.FillRight
Range("H13").Select
Selection.FillRight
Range("H14").Select
Selection.FillRight
Range("H15").Select
Selection.FillRight
Range("J4").Select
ActiveCell.FormulaR1C1 = "='Riverview East Owners'!RC[-1]"
Range("J5").Select
ActiveCell.FormulaR1C1 = "='Liberty House Condominium'!R[-1]C[-1]"
Range("J6").Select
ActiveCell.FormulaR1C1 = "='Liberty Terrace'!R[-2]C[-1]"
Range("J7").Select
ActiveCell.FormulaR1C1 = "='Liberty Court'!R[-3]C[-1]"
Range("J8").Select
ActiveCell.FormulaR1C1 = "='Liberty View'!R[-4]C[-1]"
Range("J9").Select
ActiveCell.FormulaR1C1 = "='200-210 E. 65th St.'!R[-5]C[-1]"
Range("J10").Select
ActiveCell.FormulaR1C1 = "='761-799 7th Ave. '!R[-6]C[-1]"
Range("J11").Select
ActiveCell.FormulaR1C1 = "='Executive Condominium'!R[-7]C[-1]"
Range("J12").Select
ActiveCell.FormulaR1C1 = "='Cove Club'!R[-8]C[-1]"
Range("J13").Select
ActiveCell.FormulaR1C1 = "='Greenwhich Court'!R[-9]C[-1]"
Range("J14").Select
ActiveCell.FormulaR1C1 = "='The Regatta'!R[-10]C[-1]"
Range("J15").Select
ActiveCell.FormulaR1C1 = "='The Soundings'!R[-11]C[-1]"
Range("K4").Select
Selection.FillRight
Range("K5").Select
Selection.FillRight
Range("K6").Select
Selection.FillRight
Range("K7").Select
Selection.FillRight
Range("K8").Select
Selection.FillRight
Range("K9").Select
Selection.FillRight
Range("K10").Select
Selection.FillRight
Range("K11").Select
Selection.FillRight
Range("K12").Select
Selection.FillRight
Range("K13").Select
Selection.FillRight
Range("K14").Select
Selection.FillRight
Range("K15").Select
Selection.FillRight
Range("L15").Select
Selection.FillRight
Range("L14").Select
Selection.FillRight
Range("L13").Select
Selection.FillRight
Range("L12").Select
Selection.FillRight
Range("L11").Select
Selection.FillRight
Range("L10").Select
Selection.FillRight
Range("L9").Select
Selection.FillRight
Range("L8").Select
Selection.FillRight
Range("L7").Select
Selection.FillRight
Range("L6").Select
Selection.FillRight
Range("L5").Select
Selection.FillRight
Range("L4").Select
Selection.FillRight
Range("E19").Select
ActiveCell.FormulaR1C1 = "='30 Lincoln (S&P)'!R[-15]C[-1]"
Range("E20").Select
ActiveCell.FormulaR1C1 = "='The Highgate (P&S)'!R[-16]C[-1]"
Range("E21").Select
ActiveCell.FormulaR1C1 = "='Milro Associates'!R[-17]C[-1]"
Range("E22").Select
ActiveCell.FormulaR1C1 = "='The Claridge House (P&H)'!R[-18]C[-1]"
Range("E23").Select
ActiveCell.FormulaR1C1 = "='Courtney Associates'!R[-19]C[-1]"
Range("E24").Select
ActiveCell.FormulaR1C1 = "='Stonehenge Project'!R[-20]C[-1]"
Range("F19").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='30 Lincoln (S&P)'!R[-15]C[7]"
Range("F20").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='The Highgate (P&S)'!R[-16]C[7]"
Range("F21").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='Milro Associates'!R[-17]C[7]"
Range("F22").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='The Claridge House (P&H)'!R[-18]C[7]"
Range("F23").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='Courtney Associates'!R[-19]C[7]"
Range("F24").Select
ActiveCell.FormulaR1C1 = "='M:\[Payroll 2009.xls]Stonehenge
Project'!R[-20]C"
Range("F24").Select
Selection.FillRight
ActiveCell.FormulaR1C1 = "='Stonehenge Project'!R[-20]C[7]"
Range("G19").Select
Selection.FillRight
Range("G20").Select
Selection.FillRight
Range("G21").Select
Selection.FillRight
Range("G22").Select
Selection.FillRight
Range("G23").Select
Selection.FillRight
Range("G24").Select
Selection.FillRight
Range("H24").Select
Selection.FillRight
Range("H23").Select
Selection.FillRight
Range("H22").Select
Selection.FillRight
Range("H21").Select
Selection.FillRight
Range("H20").Select
Selection.FillRight
Range("H19").Select
Selection.FillRight
Range("J19").Select
ActiveCell.FormulaR1C1 = "='30 Lincoln (S&P)'!R[-15]C[-1]"
Range("J20").Select
ActiveCell.FormulaR1C1 = "='The Highgate (P&S)'!R[-16]C[-1]"
Range("J21").Select
ActiveCell.FormulaR1C1 = "='Milro Associates'!R[-17]C[-1]"
Range("J22").Select
ActiveCell.FormulaR1C1 = "='The Claridge House (P&H)'!R[-18]C[-1]"
Range("J23").Select
ActiveCell.FormulaR1C1 = "='Courtney Associates'!R[-19]C[-1]"
Range("J24").Select
ActiveCell.FormulaR1C1 = "='Stonehenge Project'!R[-20]C[-1]"
Range("K19").Select
Click to expand...
Click to expand...
 
U

Unique713

I am not trying to preserve the row above it. Different information is being
pulled from each sheet into a shummary sheet so the set up is different.
Thank you for taking the time out to reply anyway.
 
T

TomPl

Another way to do it would be to change all of your formulas to "Offset"
formulas.

For example, Enter the the formula without quotes "=OFFSET('Riverview East
Owners'!D4,$A$1,0)" in cell E4. This assumes that in cell A1 you have the
number of rows you want to offset. Each time you add 1 to cell A1 all of
your formula will look to the next row down.

Tom
 
U

Unique713

Can you show me where in my macro i would do that? If at all possible can you
put your example where you see my code for the "master" information?
 
T

TomPl

This solution does not require the use of a macro. You would one time go
into your workbook and change all of the formulas according to to the example
I have provided (a much easier solution than adjusting the macro for a one
time event). The value of cell A1 would then control the row that your
formulas are looking to.

If, however, this is part of a larger macro and you still want to include
the updateing of the row in the larger macro then you could replace all of
your code with:

Range("A1").value = Range("A1").value + 1

When 1 is added to the value of cell A1 all of the offset formulas take
their value from the next row down.

Tom
 
U

Unique713

I am fairly new to Macros and I keep getting a syntax error when I try your
example. Is there anyway for you to re-write a portion of the macro to
illustrate what you r trying to say?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro to shift to next row 2
Macro to shift 3
How do I edit this macro? 5
I need a simple loop with a 4+ row added in. 5
Help with Macro 3
Problem with Macro 7
Excel Excel Macro Help 2
Hard reference in a macro 1

Top