Excel macro help [Solved/Closed]

Report
-
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
-
Hi there,
I am getting an error when I try and run the following macro. I am trying to find the value in one worksheet (final) in another worksheet (years) and then copy the cells underneath. I recorded this macro then changed the find(what: part so I guess this is the problem.
Any help would be greatfully received.

Sheets("years").Select
Selection.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

1 reply

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
macro slightly modified

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.