Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change data with a macro
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? "TomPl" wrote: 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change data with a macro
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 "Unique713" wrote: 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? "TomPl" wrote: 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change data with a macro
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? "TomPl" wrote: 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 "Unique713" wrote: 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? "TomPl" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a Macro which allows me to change the data series of a ch | Charts and Charting in Excel | |||
In Macro How can i Change Autofiltered data one by one | Excel Discussion (Misc queries) | |||
macro to change data in inactive sheet | Excel Discussion (Misc queries) | |||
macro to change data in inactive sheet | Excel Discussion (Misc queries) | |||
Macro to change data in a sheet | Excel Discussion (Misc queries) |