Bub_the_Zombie
Board Regular
- Joined
- Nov 1, 2016
- Messages
- 53
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
Could someone please tell me how to fix the second code.
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
Code:
WS.Range("B7").Paste
Could someone please tell me how to fix the second code.
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
Code:
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