Hmm, try this:
Sub Transmit()
Dim fPATH As String, wasOPEN As Boolean
Dim wsMaster As Worksheet, wbDEST As Workbook
Application.ScreenUpdating = False
fPATH = ThisWorkbook.Path & Application.PathSeparator
Set wsMaster = ThisWorkbook.Sheets("Compiled Record")
On Error Resume Next
Set wbDEST = Workbooks("InsuredRecord.xlsm")
If wbDEST Is Nothing Then
Set wbDEST = Workbooks.Open(fPATH & "InsuredRecord.xlsm")
Else
wasOPEN = True
End If
If Not wbDEST Is Nothing Then
With wbDEST.Sheets("Sheet1")
.UsedRange.Clear
wsMaster.Range("A:N, BG:BV").Copy .Range("A1")
.Columns.AutoFit
End With
If wasOPEN Then wbDEST.Save Else wbDEST.Close True
Else
MsgBox "Could not find InsuredRecord"
End If
wasOPEN = False
Set wbDEST = Nothing
Set wbDEST = Workbooks("ControlRecord.xlsm")
If wbDEST Is Nothing Then
Set wbDEST = Workbooks.Open(fPATH & "ControlRecord.xlsm")
Else
wasOPEN = True
End If
If Not wbDEST Is Nothing Then
With wbDEST.Sheets("Sheet1")
.UsedRange.Clear
wsMaster.Range("A:C, O:S").Copy .Range("A1")
.Columns.AutoFit
End With
If wasOPEN Then wbDEST.Save Else wbDEST.Close True
Else
MsgBox "Could not find ControlRecord"
End If
wasOPEN = False
Set wbDEST = Nothing
Set wbDEST = Workbooks("PolicyRecord.xlsm")
If wbDEST Is Nothing Then
Set wbDEST = Workbooks.Open(fPATH & "PolicyRecord.xlsm")
Else
wasOPEN = True
End If
If Not wbDEST Is Nothing Then
With wbDEST.Sheets("Sheet1")
.UsedRange.Clear
wsMaster.Range("A:N, T:BF").Copy .Range("A1")
.Columns.AutoFit
End With
If wasOPEN Then wbDEST.Save Else wbDEST.Close True
Else
MsgBox "Could not find PolicyRecord"
End If
wasOPEN = False
Application.ScreenUpdating = True
End Sub
Bookmarks