Hi All,

At the moment I am trying to transfer some data from one dynamic workbook to another. A part of this is to see if a value is in a cell on one of the workbooks (openWb) and then run a macro that is in the other book (currentWb). because neither of them have a fixed file name, I think it isn't has straight forward as usual. I have tried a whole bunch of ways round it, but non have worked. any assistance or ideas will be welcomed greatly.

Code:

Option Explicit

Sub Mate_Project()

If Worksheets("VBA DATA").Range("B16").Value <> "" Then

    Dim answerTOP As VbMsgBoxResult
    answerTOP = MsgBox("A project has been previously mated. This action will overwrite the previous project data. Please save the previous project first.", vbOKCancel + vbExclamation, "Project")
    If answerTOP = vbOK Then

    GoTo TopAction

    Else

    GoTo EndAction
    
    End If
    
Else

GoTo TopAction

End If

TopAction:

Dim answer01 As VbMsgBoxResult
answer01 = MsgBox("Before mating a project, please ensure that the project costing spreadsheet has been saved.", vbOKCancel + vbInformation, "Caution!")
If answer01 = vbOK Then

    Dim answer02 As VbMsgBoxResult
        answer02 = MsgBox("You are about to mate project. Confirm?", vbYesNo + vbQuestion, "Confirm Mate")
        If answer02 = vbYes Then

        Dim desPathName As Variant
            desPathName = Application.GetOpenFilename(FileFilter:="Excel Files (*.xlsm*), *.xlsm*", Title:="Please select a Project Costing Sheet")
            If desPathName = False Then
                
                GoTo EndAction
    
                Else
    
                Dim openWb As Workbook
                Set openWb = Workbooks.Open(desPathName)
    
                Dim currentWb As Workbook
                Set currentWb = ThisWorkbook
                
                GoTo FirstAction
    
                End If
        
            Else
        
            GoTo EndAction
        
            End If

Else

GoTo EndAction

End If

FirstAction:

If openWb.Worksheets("DATA SINC").Range("U5").Value = "WT" Then

    GoTo FirstActionB

    Else

    Dim Answer05 As VbMsgBoxResult
    Answer05 = MsgBox("You are only able to mate projects created in Budget Pricing Model OFFSHORE 2015 R4.1 or later.", vbOKOnly + vbCritical, "Project")
    If Answer05 = vbOK Then

    GoTo EndActionB

    Else

    GoTo EndActionB

    End If
    
End If

FirstActionB:



Dim MacroStrNam1 As Name
Set MacroStrNam1 = currentWb

Dim MacroAction1 As String
Set MacroAction1 = MacroStrNam1 & ".xlsm'!Remove_SOW_Row"

Do

With currentWb

Run MacroAction1

End With

Loop Until currentWb.Worksheets("PQT Summary").Range("SOW_Blck02").Offset(-2, 1).Value = "Pipe description"

With currentWb.Worksheets("PQT Summary")

currentWb.Worksheets("VBA DATA").Range("MATEDIDENT").Offset(0, 1).Value = "1"
currentWb.Worksheets("VBA DATA").Range("desPathName").Offset(0, 1).Value = desPathName
currentWb.Worksheets("PQT Summary").Range("C2").Value = openWb.Worksheets("MASTER PRICING TAB").Range("B2")
currentWb.Worksheets("PQT Summary").Range("C3").Value = openWb.Worksheets("MASTER PRICING TAB").Range("B1")

End With

GoTo SecondAction

SecondAction:

If ActiveCell.Value <> "M" Then

GoTo SecondActionB

Else

ActiveCell.Offset(1, 0).Select

GoTo SecondAction

End If

SecondActionB:

If openWb.Worksheets("DATA SINC").Range("B6").Value = "0" Then

    Dim answer04 As VbMsgBoxResult
    answer04 = MsgBox("No pipe data available", vbOKOnly + vbCritical, "Project")
    If answer04 = vbOK Then
    GoTo EndActionB
    Else
    GoTo EndActionB
    End If
    
Else

GoTo ThirdAction

End If

ThirdAction:

openWb.Worksheets("DATA SINC").Range("MATEREF").Value = "M"

currentWb.Worksheets("PQT Summary").Range("SOW_Blck02").Offset(-1, 1) = openWb.Worksheets("DATA SINC").Range("MATEREF").Offset(0, -16)
currentWb.Worksheets("PQT Summary").Range("SOW_Blck02").Offset(-1, 3) = openWb.Worksheets("DATA SINC").Range("MATEREF").Offset(0, -14)
currentWb.Worksheets("PQT Summary").Range("SOW_Blck02").Offset(-1, 4) = openWb.Worksheets("DATA SINC").Range("MATEREF").Offset(0, 3)
currentWb.Worksheets("PQT Summary").Range("SOW_Blck02").Offset(-1, 5) = openWb.Worksheets("DATA SINC").Range("MATEREF").Offset(0, 4)
currentWb.Worksheets("PQT Summary").Range("SOW_Blck02").Offset(-1, 6) = openWb.Worksheets("DATA SINC").Range("MATEREF").Offset(0, -12)
currentWb.Worksheets("PQT Summary").Range("SOW_Blck02").Offset(-1, 7) = openWb.Worksheets("DATA SINC").Range("MATEREF").Offset(0, -11)

openWb.Worksheets("DATA SINC").Range("MATEREF").Select

GoTo ForthAction

ForthAction:

Dim TempoCell As Range
Set TempoCell = ActiveCell

If TempoCell.Offset(1, -16).Value = "" Then

GoTo EndActionC

Else

Dim MacroAction2 As String
Set MacroAction2 = currentWb & ".xlsm'!Insert_SOW_Row"

With currentWb

Run MacroAction2

End With

Dim TempoCell2 As Range
Set TempoCell2 = TempoCell.Offset(1, 0)

currentWb.Worksheets("PQT Summary").Range("SOW_Blck02").Offset(-1, 1) = TempoCell2.Offset(0, -16)
currentWb.Worksheets("PQT Summary").Range("SOW_Blck02").Offset(-1, 3) = TempoCell2.Offset(0, -14)
currentWb.Worksheets("PQT Summary").Range("SOW_Blck02").Offset(-1, 4) = TempoCell2.Offset(0, 3)
currentWb.Worksheets("PQT Summary").Range("SOW_Blck02").Offset(-1, 5) = TempoCell2.Offset(0, 4)
currentWb.Worksheets("PQT Summary").Range("SOW_Blck02").Offset(-1, 6) = TempoCell2.Offset(0, -12)
currentWb.Worksheets("PQT Summary").Range("SOW_Blck02").Offset(-1, 7) = TempoCell2.Offset(0, -11)

TempoCell2.Select

GoTo ForthAction

End If

EndActionB:

With openWb
.Save
.Close

End With

GoTo EndAction

EndActionC:

With openWb
.Save
.Close

End With

Application.StatusBar = False

Beep

Dim answer03 As VbMsgBoxResult
answer03 = MsgBox("Project Mated", vbOKOnly, "Project")
If answer03 = vbOK Then

GoTo EndAction

Else

GoTo EndAction

End If

EndAction:

End Sub
Thanks

Dale