Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

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
See more

1 reply

Answer 1 / 1
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.
Ask a question