Excel macro help
Solved/Closed
Fi
-
Apr 29, 2011 at 01:10 AM
venkat1926
venkat1926
- Posts
- 1864
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- August 7, 2021
Related:
- Excel macro help
- Excel macro to create new sheet based on value in cells - Guide
- Excel macro auto increment number - Guide
- Excel macro create new workbook and copy data ✓ - Forum - Excel
- Excel macro to create new workbook based on value in cells - Forum - Excel
- Excel macro to insert rows between data - Forum - Excel
1 reply
venkat1926
Apr 29, 2011 at 01:21 AM
- Posts
- 1864
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- August 7, 2021
Apr 29, 2011 at 01:21 AM
macro slightly modified
there is no need for so many selection. learn how to write a macro without selection
for e.g
instead of
Sheets("years").Select
ActiveSheet.Cells.Find(What:=Sheets("final").Range("I20").Value, After:=Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
you can just write
Sheets("years").Cells.Find(What:=Sheets("final").Range("I20").Value, After:=Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
in find method many of them are defaults and can be omitted. see help "find"
perhpas "what" and "lookat" are important.
Sub test() Sheets("years").Select ActiveSheet.Cells.Find(What:=Sheets("final").Range("I20").Value, After:=Range("A1"), LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Range(Selection, Selection.End(xlDown)).Select Application.CutCopyMode = False Selection.Copy Sheets("runhours").Select Range("B2").Select ActiveSheet.Paste Sheets("FINAL").Select End Sub
there is no need for so many selection. learn how to write a macro without selection
for e.g
instead of
Sheets("years").Select
ActiveSheet.Cells.Find(What:=Sheets("final").Range("I20").Value, After:=Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
you can just write
Sheets("years").Cells.Find(What:=Sheets("final").Range("I20").Value, After:=Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
in find method many of them are defaults and can be omitted. see help "find"
perhpas "what" and "lookat" are important.