Help, Need one range fixed.

[Bub_the_Zombie]

I have made a macro using the macro recorder to take all instances of a name from a list and copy them (and the range of data) to a different area on the page. The macro works great when it is run from the VBA screen, but not when I try to apply it to a command button.

I defined the WS to the correct sheet it needs to work on, but one piece of it stays wrong with each attempt, either range or "object doesn't support this property or method". I put the results of the macro recorder, then my attempt to clean it up and apply it to a command button in separate code boxes below.

Currently the problem that is being highlighted when it is debugged is

WS.Range("B7").Paste

Could someone please tell me how to fix the second code.

Sub FIND_TOM()

    ActiveWindow.SmallScroll Down:=-3
    Range("B100:F139").Select
    Selection.AutoFilter
    ActiveSheet.Range("$B$100:$F$139").AutoFilter Field:=5, Criteria1:="TOM"
    ActiveWindow.SmallScroll Down:=-12
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-84
    Range("B7").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.AutoFilter
End Sub

Private Sub daily_sheets_Click()

Dim WS As Worksheet
Set WS = Sheets("daily sheets")


    WS.Range("B100:F139").AutoFilter
    WS.Range("$B$100:$F$139").AutoFilter Field:=5, Criteria1:="TOM"
    WS.Range("B100:F147").Copy
    
    WS.Range("B7").Paste 'this is the problem
    
    Application.CutCopyMode = False
    Selection.AutoFilter
    
End Sub

 

[SQUIDD]

hi

would it not work if you simply put

call FIND_TOM

within your command button and remove the rest?

Dave

 

[JLGWhiz]

this syntax will copy and paste your data.

WS.Range("B100:F147").Copy  WS.Range("B7")

Another way is to first select B7 then use ActiveSheet.Paste

 

[Bub_the_Zombie]

JLGWhiz, thanks! Works perfect!

Squidd, still learning excel, really only two weeks out the gate. That is an awesome command, is there a way to get that command to work from another work sheet? In the above example the button to activate that code is on a separate sheet (sheet 1) and the actions take place on (sheet 2).

 

[JLGWhiz]

JLGWhiz, thanks! Works perfect!

).

You're welcome,
regards, JLG

 

[SQUIDD]

you can call a macro from any command button and it will execute the code that is in the macro, so if that macros said,

do something on sheet 1, do something on sheet 2 and finally do something on sheet 3, then the macro will do this. no matter where you call the macro.

dave