ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Macro to retrieve information (https://www.excelbanter.com/excel-discussion-misc-queries/211572-macro-retrieve-information.html)

Unique713
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?

Gary''s Student
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?

Unique713
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?


All times are GMT +1. The time now is 09:05 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com