jordanburch
Active Member
- Joined
- Jun 10, 2016
- Messages
- 440
- Office Version
- 2016
Sub COSARimportfinal21currentmonth()
Dim MyFile As String
Dim erow As Long
Dim Filepath As String
Dim wb1 As Workbook, wb2 As Workbook
' Dim ThisWorkbook.Worksheets("Variables").Range("A1").Value As String
' Dim ThisWorkbook.Worksheets("Variables").Range("A3").Value As String
' Dim ThisWorkbook.Worksheets("Variables").Range("A4").Value As String
' Dim ThisWorkbook.Worksheets("Variables").Range("A6").Value As String
' Dim ThisWorkbook.Worksheets("Variables").Range("A3").Value As String
Dim fn As String
Dim fn2 As String
Dim fn3 As String
Dim fn4 As String
Dim ShtName1 As String
Dim ShtName2 As String
Dim ShtName3 As String
ShtName1 = "Detail Lines"
ShtName2 = "Detail"
ShtName3 = "Detail -"
' ThisWorkbook.Worksheets("Variables").Range("A6").Value = InputBox("Enter month Name I.E. YYYYMM:", Default:="202005")
'ThisWorkbook.Worksheets("Variables").Range("A1").Value = InputBox("Enter month I.E. 08-MAY20", Default:="08-MAY20")
'ThisWorkbook.Worksheets("Variables").Range("A3").Value = Right(ThisWorkbook.Worksheets("Variables").Range("A1").Value, 5)
' ThisWorkbook.Worksheets("Variables").Range("A3").Value = "FY" & Right(ThisWorkbook.Worksheets("Variables").Range("A3").Value, 2)
'ThisWorkbook.Worksheets("Variables").Range("A4").Value = ThisWorkbook.Worksheets("Variables").Range("A3").Value
fn = Left(ThisWorkbook.Worksheets("Variables").Range("A1").Value, 6)
fn2 = Right(ThisWorkbook.Worksheets("Variables").Range("A1").Value, 2)
fn3 = Right(ThisWorkbook.Worksheets("Variables").Range("A6").Value, 2)
Application.ScreenUpdating = False
Worksheets.Add(After:=Worksheets(1)).Name = "CO SAR"
Set wb1 = ThisWorkbook
fn4 = Right(ThisWorkbook.Worksheets("Variables").Range("A1").Value, 5)
Filepath = "K:\SHARED\TRANSFER\Enterprise Wide Suspense Initiative\Source Files\21 Field Details\" & ThisWorkbook.Worksheets("Variables").Range("A4").Value & "\" & ThisWorkbook.Worksheets("Variables").Range("A1").Value & "\Field Detail Lines\"
'K:\SHARED\TRANSFER\Enterprise Wide Suspense Initiative\DRP\2020 DRP\2020-05 Reporting Cycle
MyFile = "CO21army" & fn4 & ".xlsx"
Dim strFileName As String
Dim strFileExists As String
strFileName = Filepath & MyFile
strFileExists = Dir(strFileName)
If strFileExists = "" Then
MsgBox "The current month 21 CO SAR file does not exist"
Else
erow = wb1.Sheets("CO SAR").Cells(Rows.Count, 14).End(xlUp).Offset(1, 0).Row
Set wb2 = Workbooks.Open(Filepath & MyFile)
With wb2
Dim ShtName As String
ShtName = "Sheet 1"
If Evaluate("isref('" & ShtName & "'!A1)") Then
'sheet exists do something
Else
'sheet doesn't exist do something else
If Evaluate("isref('" & ShtName1 & "'!A1)") Then
wb2.Sheets("Detail Lines").Range("q2:q1000").Value = MyFile
.Sheets("Detail Lines").Range("c2:q1000").Copy Destination:=wb1.Worksheets("CO SAR").Cells(erow, 1)
.Close savechanges:=False
ElseIf Evaluate("isref('" & ShtName3 & "'!A1)") Then
.Sheets("Detail Lines").Range("c21000").Copy Destination:=wb1.Worksheets("CO SAR").Cells(erow, 1)
.Close savechanges:=False
ElseIf Evaluate("isref('" & ShtName2 & "'!A1)") Then
.Sheets("Detail Lines").Range("c21000").Copy Destination:=wb1.Worksheets("CO SAR").Cells(erow, 1)
.Close savechanges:=False
End If
End With
End If
Application.ScreenUpdating = True
End Sub
compile error
end with without if
is the error
Dim MyFile As String
Dim erow As Long
Dim Filepath As String
Dim wb1 As Workbook, wb2 As Workbook
' Dim ThisWorkbook.Worksheets("Variables").Range("A1").Value As String
' Dim ThisWorkbook.Worksheets("Variables").Range("A3").Value As String
' Dim ThisWorkbook.Worksheets("Variables").Range("A4").Value As String
' Dim ThisWorkbook.Worksheets("Variables").Range("A6").Value As String
' Dim ThisWorkbook.Worksheets("Variables").Range("A3").Value As String
Dim fn As String
Dim fn2 As String
Dim fn3 As String
Dim fn4 As String
Dim ShtName1 As String
Dim ShtName2 As String
Dim ShtName3 As String
ShtName1 = "Detail Lines"
ShtName2 = "Detail"
ShtName3 = "Detail -"
' ThisWorkbook.Worksheets("Variables").Range("A6").Value = InputBox("Enter month Name I.E. YYYYMM:", Default:="202005")
'ThisWorkbook.Worksheets("Variables").Range("A1").Value = InputBox("Enter month I.E. 08-MAY20", Default:="08-MAY20")
'ThisWorkbook.Worksheets("Variables").Range("A3").Value = Right(ThisWorkbook.Worksheets("Variables").Range("A1").Value, 5)
' ThisWorkbook.Worksheets("Variables").Range("A3").Value = "FY" & Right(ThisWorkbook.Worksheets("Variables").Range("A3").Value, 2)
'ThisWorkbook.Worksheets("Variables").Range("A4").Value = ThisWorkbook.Worksheets("Variables").Range("A3").Value
fn = Left(ThisWorkbook.Worksheets("Variables").Range("A1").Value, 6)
fn2 = Right(ThisWorkbook.Worksheets("Variables").Range("A1").Value, 2)
fn3 = Right(ThisWorkbook.Worksheets("Variables").Range("A6").Value, 2)
Application.ScreenUpdating = False
Worksheets.Add(After:=Worksheets(1)).Name = "CO SAR"
Set wb1 = ThisWorkbook
fn4 = Right(ThisWorkbook.Worksheets("Variables").Range("A1").Value, 5)
Filepath = "K:\SHARED\TRANSFER\Enterprise Wide Suspense Initiative\Source Files\21 Field Details\" & ThisWorkbook.Worksheets("Variables").Range("A4").Value & "\" & ThisWorkbook.Worksheets("Variables").Range("A1").Value & "\Field Detail Lines\"
'K:\SHARED\TRANSFER\Enterprise Wide Suspense Initiative\DRP\2020 DRP\2020-05 Reporting Cycle
MyFile = "CO21army" & fn4 & ".xlsx"
Dim strFileName As String
Dim strFileExists As String
strFileName = Filepath & MyFile
strFileExists = Dir(strFileName)
If strFileExists = "" Then
MsgBox "The current month 21 CO SAR file does not exist"
Else
erow = wb1.Sheets("CO SAR").Cells(Rows.Count, 14).End(xlUp).Offset(1, 0).Row
Set wb2 = Workbooks.Open(Filepath & MyFile)
With wb2
Dim ShtName As String
ShtName = "Sheet 1"
If Evaluate("isref('" & ShtName & "'!A1)") Then
'sheet exists do something
Else
'sheet doesn't exist do something else
If Evaluate("isref('" & ShtName1 & "'!A1)") Then
wb2.Sheets("Detail Lines").Range("q2:q1000").Value = MyFile
.Sheets("Detail Lines").Range("c2:q1000").Copy Destination:=wb1.Worksheets("CO SAR").Cells(erow, 1)
.Close savechanges:=False
ElseIf Evaluate("isref('" & ShtName3 & "'!A1)") Then
.Sheets("Detail Lines").Range("c21000").Copy Destination:=wb1.Worksheets("CO SAR").Cells(erow, 1)
.Close savechanges:=False
ElseIf Evaluate("isref('" & ShtName2 & "'!A1)") Then
.Sheets("Detail Lines").Range("c21000").Copy Destination:=wb1.Worksheets("CO SAR").Cells(erow, 1)
.Close savechanges:=False
End If
End With
End If
Application.ScreenUpdating = True
End Sub
compile error
end with without if
is the error