Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last row of data and paste
I am very new at macros and had recorded one for a worksheet I use all the
time. I copied/pasted the macro into another workbook that I could use this the macro for but there is less data and has the potential to grow. In the macro I recorded I had copied a formula and then autofilled down to last row of data. How do I edit the macro to find last row and copy the formula down. I am ending up with unecessary rows of formulas where there is no data. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last row of data and paste
As always, post YOUR code for comments and suggestions.
-- Don Guillett Microsoft MVP Excel SalesAid Software "Jen_T" wrote in message ... I am very new at macros and had recorded one for a worksheet I use all the time. I copied/pasted the macro into another workbook that I could use this the macro for but there is less data and has the potential to grow. In the macro I recorded I had copied a formula and then autofilled down to last row of data. How do I edit the macro to find last row and copy the formula down. I am ending up with unecessary rows of formulas where there is no data. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last row of data and paste
Sorry about that,, it is abit lengthy and I am sure can be cleaned up:
Cells.Select Selection.Columns.AutoFit Range("A2").Select ActiveWindow.FreezePanes = True Workbooks.Open Filename:= _ "J:\\Monthly Exports\\Jan09systemdataexport.XLS" Windows("Nicole_Fill_Export_File.xls").Activate Columns("B:B").Select Selection.Insert Shift:=xlToRight Range("B1").Select ActiveCell.FormulaR1C1 = "New Create Date" Range("A1").Select Selection.EntireColumn.Insert Selection.EntireColumn.Insert Range("B1").Select ActiveCell.FormulaR1C1 = "Prog Mth" Range("A1").Select ActiveCell.FormulaR1C1 = "Prog Yr" Range("D1").Select ' Update file name below................ Windows("Jan09systemdataexport.XLS").Activate ActiveWindow.SmallScroll ToRight:=1 Windows("Nicole_Fill_Export_File.xls").Activate Columns("C:C").Select Selection.Insert Shift:=xlToRight Range("C1").Select ActiveCell.FormulaR1C1 = "Record Location" Columns("C:C").EntireColumn.AutoFit Range("E1").Select Windows("Jan09systemdataexport.XLS").Activate Windows("Nicole_Fill_Export_File.xls").Activate Windows("Jan09systemdataexport.XLS").Activate ActiveWindow.LargeScroll ToRight:=1 Columns("G:G").Select Selection.Copy Windows("Nicole_Fill_Export_File.xls").Activate Sheets("Lookup").Select Range("E1").Select ActiveSheet.Paste Windows("Jan09systemdataexport.XLS").Activate ActiveWindow.LargeScroll ToRight:=-1 Columns("E:E").Select Application.CutCopyMode = False Selection.Copy Windows("Nicole_Fill_Export_File.xls").Activate Range("F1").Select ActiveSheet.Paste Columns("E:F").Select Range("F1").Activate Application.CutCopyMode = False ActiveWorkbook.Names.Add Name:="date", RefersToR1C1:="=Lookup!C5:C6" Range("E2").Select Sheets("Clean Data").Select Range("E2").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[4],date,2,FALSE)" Selection.NumberFormat = "m/d/yyyy" Selection.AutoFill Destination:=Range("E2:E2003") Range("E2:E2003").Select Range("B2").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[3],Prog,2,FALSE)" Selection.NumberFormat = "[$-409]mmm-yy;@" Range("B2").Select Selection.AutoFill Destination:=Range("B2:B2003") Range("B2:B2003").Select Range("A2").Select ActiveCell.FormulaR1C1 = "=YEAR(RC[1])" Selection.AutoFill Destination:=Range("A2:A2003") Range("A2:A2003").Select Range("A2").Select ActiveWindow.SmallScroll ToRight:=11 Range("Q1").Select Selection.End(xlToLeft).Select Range("Q1:BP1").Select Selection.Replace What:="State (", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:=")", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Range("CK1:CN1").Select Selection.NumberFormat = "m/d/yyyy" ActiveWindow.SmallScroll ToRight:=-5 Range("A1").Select End Sub "Don Guillett" wrote: As always, post YOUR code for comments and suggestions. -- Don Guillett Microsoft MVP Excel SalesAid Software "Jen_T" wrote in message ... I am very new at macros and had recorded one for a worksheet I use all the time. I copied/pasted the macro into another workbook that I could use this the macro for but there is less data and has the potential to grow. In the macro I recorded I had copied a formula and then autofilled down to last row of data. How do I edit the macro to find last row and copy the formula down. I am ending up with unecessary rows of formulas where there is no data. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find data and copy and paste | Excel Programming | |||
Find next empty row & paste data | Excel Programming | |||
Find, Match data and paste data between two workbooks | Excel Discussion (Misc queries) | |||
I need to find a macro to find data cut and paste to another colu. | Excel Programming | |||
Find cell and paste data | Excel Programming |