Microsoft Office Forums - View Single Post - Need Help to Run an Existing Macro From Current Workbook to Another Workbook

rsrasc · 05-06-2018, 10:05 AM

Than you p45cal for your posting. Somehow I managed to change the "For Each ws In ThisWorkbook.Sheets (old line) to "For Each ws In ActiveWorkbook.Worksheets" and it is working.

Code:

Sub testing()
Dim ws As Worksheet, fndRng As Range
For Each ws In ActiveWorkbook.Worksheets
'For Each ws In ThisWorkbook.Sheets (old line)
    With ws.Range("Y:Y")
        Set fndRng = .Find(What:="G", LookIn:=xlValues, LookAt:=xlWhole, _
            SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
        If Not fndRng Is Nothing Then
            fndRng.Offset(-1).Resize(, 2).Value = fndRng.Resize(, 2).Value
            fndRng.EntireRow.Delete
        End If
    End With
Next ws
End Sub

Thanks again!