Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Change data with a macro
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 16
Default Change data with a macro
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


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 35,218
Default Change data with a macro
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]"

makes things a lot easier to update/check.



Unique713 wrote:

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

--

Dave Peterson
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 16
Default Change data with a macro
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" wrote:

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]"

makes things a lot easier to update/check.



Unique713 wrote:

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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 342
Default Change data with a macro
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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 16
Default Change data with a macro
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.

"TomPl" wrote:

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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Creating a Macro which allows me to change the data series of a ch Cath Charts and Charting in Excel 6 October 2nd 08 02:45 PM
In Macro How can i Change Autofiltered data one by one Vijay Kotian Excel Discussion (Misc queries) 1 September 5th 06 10:46 AM
macro to change data in inactive sheet Diana Excel Discussion (Misc queries) 1 April 28th 06 08:36 PM
macro to change data in inactive sheet Diana Excel Discussion (Misc queries) 1 April 28th 06 08:10 PM
Macro to change data in a sheet Diana Excel Discussion (Misc queries) 1 April 28th 06 08:01 PM


All times are GMT +1. The time now is 12:39 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement