run macro from workbook A to perform on workbook B

Hi there,

I currently have 2 workbooks. Workbook A contains the macro that I have created. Workbook B is a workbook that has been given to me by a work colleague that I dont want to modify or add any modules to.

Basically my macro is functioning, but I when I call it I dont want it to operate on Workbook A I want it to operate on a particular sheet in Workbook B.

I seen from a lot of posts on the net that the following code

Application.Run ("[workbook name!macro]")

seems to be able to do this but I believe from what I have seen, you have to add this to Workbook B in my case, and I dont want to do this as I want to keep the original workbook intact.

Is there a way to do this? If you need the workbooks I am working on I can provide them upon request but Im sure the syntax isn't too demanding - I hope!

cheers for any help,

Jag

Hi there, yeah well I've tried the code below, but it still only seems to work on the current workbook, or workbook A in my example:

Public Sub XAmple()
Dim CTIP As Workbook
Dim wsCD As Worksheet, wsNew As Worksheet
Dim HL As Hyperlink
Dim rngCopy As Range
On Error GoTo ExitPoint
Application.ScreenUpdating = False

Set CTIP = Workbooks("Workbook B.xls")
Set wsCD = Sheets("Compartment Details")
Set wsNew = Sheets.Add(After:=Sheets(Sheets.Count))
With CTIP
    For Each HL In wsCD.Hyperlinks
        wsCD.Activate: HL.Follow
        Set rngCopy = ActiveCell.Offset(, 1 - ActiveCell.Column).Resize(, 20)
        wsNew.Cells(wsNew.Rows.Count, "B").End(xlUp).Offset(1).Resize(, 20).Value = rngCopy.Value
        Set rngCopy = Nothing
    Next HL
ExitPoint:
    Set wsCD = Nothing
    Set wsNew = Nothing
    Application.ScreenUpdating = True
End With
End Sub

Jag

Hi there,

I've tried the above method within my workbooks but I still cant seem to get it working. All it seems to do is just add another new sheet onto the target workbook but doesnt do anything else.

Does anyone know what I am doing wrong with the following code:

Public Sub example1()
Dim CTIP As Workbook
Dim wsCD As Worksheet, wsNew As Worksheet
Dim HL As Hyperlink
Dim rngCopy As Range
Dim vHeaders As Variant

vHeaders = Array("Compt. Code", "Section", "Para", "Performance Statement", "ACA", "0" & Chr(10) & "FTS", _
            "1" & Chr(10) & "FTS", "2" & Chr(10) & "FTS", "3" & Chr(10) & "FTS", "4" & Chr(10) & "FTS", _
            "5" & Chr(10) & "FTS", "MOD", "Criteria", "Condition", "Remarks", "Reqd By", "Test Type", _
            "Client Approval", "Event Count", "Chap Only", "Sect Only")

On Error GoTo ExitPoint
Application.ScreenUpdating = False

Set CTIP = Workbooks("CVF-10078983-FNA-03-WholeShip Compartment Inspection Matrix.xls")
With CTIP
CTIP.Activate
    Set wsCD = Sheets("Compartment Details")
    Set wsNew = Sheets.Add(After:=Sheets(Sheets.Count))

    

   ' CTIP.Activate
    For Each HL In CTIP.wsCD.Hyperlinks
        CTIP.wsCD.Activate: HL.Follow
        Set rngCopy = ActiveCell.Offset(, 1 - ActiveCell.Column).Resize(, 20)
        wsNew.Cells(wsNew.Rows.Count, "B").End(xlUp).Offset(1).Resize(, 20).Value = rngCopy.Value
        wsNew.Cells(wsNew.Rows.Count, "A").End(xlUp).Offset(1).Value = wsCD.Cells(HL.Range.Row, 5).Value
        Set rngCopy = Nothing
    Next HL
End With

With CTIP.wsNew
    CTIP.Activate
    CTIP.wsNew.Activate
    With .Range("A1").Resize(, UBound(vHeaders) + 1)
        .Value = vHeaders
    End With
    .Cells.Select
    Selection.Columns.AutoFit
    Selection.Rows.AutoFit
End With

CTIP.wsNew.Range("A1").Select
ExitPoint:
Set wsCD = Nothing
Set wsNew = Nothing
CTIP.Application.ScreenUpdating = True
End Sub

Cheers,

Jag