Macro to open a second workbook and run macro from the first workbook

[dellzy]

Dear Excel Experts,

I have a set of macros (below is my code) that should do the job on another workbook. I found a macro that can open another workbook but it only does the job to open it but the rests of actions it tries to do it on the original workbook. I don't know how to "direct" the macros to open workbook AND do actions on THAT workbook then prompt "Save As.."

Appreciate anyone's help on my problem.

Sub DelegateWork()'On Error Resume Next
Dim lngLastRow As Long
Dim lngNumberOfRows As Long
Dim lngI As Long
Dim strMainSheetName As String
Dim currSheet As Worksheet
Dim prevSheet As Worksheet
Dim Staff As Integer
Dim WS As Worksheet
Dim i As Long
Dim excelfile As String


excelfile = "CTT_" & Text(TODAY(), "ddmmyyyy") & ".xlsb"
Workbooks.Open "C:\Users\Dahlia\Downloads\Test\" & excelfile
'Current worksheet in workbook
Set prevSheet = ActiveWorkbook.ActiveSheet
'First worksheet name
strMainSheetName = prevSheet.Name
'Number of rows in worksheet
lngLastRow = prevSheet.Cells(Rows.Count, 1).End(xlUp).Row
Staff = Application.InputBox("How many to delegate?")
'Number of rows to split among worksheets
lngNumberOfRows = (lngLastRow / Staff) + 1
'Worksheet counter for added worksheets
lngI = 1
While lngLastRow > lngNumberOfRows
Set currSheet = ThisWorkbook.Worksheets.Add


With currSheet
.Move After:=Worksheets(Worksheets.Count)
.Name = strMainSheetName + "(" + CStr(lngI) + ")"
End With
With prevSheet.Rows(lngNumberOfRows + 1 & ":" & lngLastRow).EntireRow
.Cut currSheet.Range("A2")
End With
lngLastRow = currSheet.Cells(Rows.Count, 1).End(xlUp).Row
Set prevSheet = currSheet
lngI = lngI + 1
Wend

For i = 2 To Sheets.Count
        Sheets(1).Rows(1).Copy Destination:=Sheets(i).Rows(1)
    Next

End Sub

 

[Andrew Poulsom]

Try changing ThisWorkbook to ActiveWorkbook.

 

[dellzy]

Hi Andrew Poulsom

Tq so much! Never thot the error is so small but so significant! It works just great now! Yeayy!

Try changing ThisWorkbook to ActiveWorkbook.