Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to retrieve information
I created a workbook with several sheets and a summary page. The summary page
pulls information from each of the sheets. I want to create a macro that will duplicate the information from the last row that information has been entered into where all the rows do not have a zero value (the sheets are set up where the cells have a value of zero unless you enter information or delete the zero value). I have never used macros before. Can someone break this down to me step by step? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to retrieve information
If you want to move the last row with real data, can we assume that we can
ignore all rows which contain only blanks or zeros?? -- Gary''s Student - gsnu200816 "Unique713" wrote: I created a workbook with several sheets and a summary page. The summary page pulls information from each of the sheets. I want to create a macro that will duplicate the information from the last row that information has been entered into where all the rows do not have a zero value (the sheets are set up where the cells have a value of zero unless you enter information or delete the zero value). I have never used macros before. Can someone break this down to me step by step? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to retrieve information
I think it is safe to say yes. here is a copy of the macro. I just want it to
pull the information from the following row when it is run. for example, the macro is set to pull all the information from the same row of every worksheet. I want the macro when it is run to go ahead to the next row of each work sheet and insert the data into the summary page. 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 End Sub "Gary''s Student" wrote: If you want to move the last row with real data, can we assume that we can ignore all rows which contain only blanks or zeros?? -- Gary''s Student - gsnu200816 "Unique713" wrote: I created a workbook with several sheets and a summary page. The summary page pulls information from each of the sheets. I want to create a macro that will duplicate the information from the last row that information has been entered into where all the rows do not have a zero value (the sheets are set up where the cells have a value of zero unless you enter information or delete the zero value). I have never used macros before. Can someone break this down to me step by step? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Retrieve Information based on Condition | Excel Discussion (Misc queries) | |||
how to retrieve information that was saved on a floppy disk? | New Users to Excel | |||
how to retrieve information that was saved on a floppy disk? | Excel Worksheet Functions | |||
how to retrieve information that was saved on a floppy disk? | Excel Discussion (Misc queries) | |||
How do I retrieve deleted information from an Excel worksheet? | Excel Worksheet Functions |