Previous Topic Next Topic

Excel macro help

Solved/Closed
Fi - Apr 29, 2011 at 01:10 AM
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
 - Apr 29, 2011 at 01:21 AM
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

Answer 1 / 1
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
 810
Apr 29, 2011 at 01:21 AM
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.
0