|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Need Help to Run an Existing Macro From Current Workbook to Another Workbook
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 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 |
#2
|
||||
|
||||
Without having tested, change the these lines:
Code:
Sub testing() Dim ws As Worksheet, fndRng As Range For Each ws In ThisWorkbook.Sheets Code:
Sub testing(wkbook) Dim ws As Worksheet, fndRng As Range For Each ws In wkbook.Sheets Code:
testing Workbooks("PPE102815.xlsm") Code:
Set wkb = Workbooks.Open("G:\1. Payroll Files\2016\PPE102815.xls") testing wkb |
#3
|
|||
|
|||
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 |
|
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 |