Need Help to Run an Existing Macro From Current Workbook to Another Workbook

rsrasc · #1 05-06-2018, 07:06 AM

I have two Excel files with the following names:

FY16-Payroll_Hours_Report_New
PPE102815

Both are macro enabled, and they are located under the following path:

"G:\1. Payroll Files\2016\FY16-Payroll_Hours_Report_New.xlsm"
"G:\1. Payroll Files\2016\PPE102815.xlsm"

The following code was provided by NoSparks and is currently located in the file named "FY16-Payroll_Hours_Report_New".

Code:

Sub testing()
Dim ws As Worksheet, fndRng As Range
For Each ws In ThisWorkbook.Sheets
    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

Currently, I'm running the following code from this file "FY16-Payroll_Hours_Report_New" to open the PPE102815 file, saving it as macro enabled, and activating the workbook but when I run the above macro it doesn't work.

Code:

Sub Set_Open_ExistingWorkbook()

    Dim wkb As Workbook
    Set wkb = Workbooks.Open("G:\1. Payroll Files\2016\PPE102815.xls")

   
End Sub

Sub SaveWorkBookAsMacroEnable()
'
' Macro1 Macro
'

'
    ActiveWorkbook.SaveAs Filename:="G:\1. Payroll Files\2016\PPE102815.xlsm", _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub


Sub Activate_Payroll_File()

Workbooks("PPE102815.xlsm").Activate

 


End Sub

With your help, I would like to be able to run this macro from the current location of the macro ( "FY16-Payroll_Hours_Report_New") so it can run or apply the macro to the file "PPE102815 ".

Hope someone can help.

Thank you in advance for your assistance and cooperation.

Regards,
rsrasc

p45cal · #2 05-06-2018, 08:37 AM

Without having tested, change the these lines:

Code:

Sub testing()
Dim ws As Worksheet, fndRng As Range
For Each ws In ThisWorkbook.Sheets

to:

Code:

Sub testing(wkbook)
Dim ws As Worksheet, fndRng As Range 
For Each ws In wkbook.Sheets

then you can call your testing procedure in several ways, eg.:

Code:

testing Workbooks("PPE102815.xlsm")

or:

Code:

Set wkb = Workbooks.Open("G:\1. Payroll Files\2016\PPE102815.xls")
testing wkb

rsrasc · #3 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!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Sharing a Workbook that contains array formula link to other workbook	Ahmad.rage22@gmail.com	Excel	0	03-12-2018 10:15 PM
data entered in one workbook should be updated in other relevant workbook based on the date	vedha	Excel	0	04-24-2015 08:45 PM
How to add attached excel workbook with hyperlink in the same workbook	lynchbro	Excel Programming	0	02-24-2015 01:29 PM
Range(Cell1,Cell2) Error on another workbook controlling some other workbook?	tinfanide	Excel Programming	1	02-09-2012 04:08 PM
macro to transfer data from one workbook to another workbook	virsojour	Excel Programming	5	02-01-2011 08:58 PM