with the below code. If cell H1=1 the macro will copy active sheet and create a new workbook. I am trying to achieve if H1 is not 1 then to copy active sheet the workbook that was created when H1=1 and paste it to the last sheet. the workbook will be closed. with the current code it just opens the workbook but does not copy it over.
VBA Code:
Sub chkDatesCopyDutyRoster1()
Dim ws As Worksheet, Sh As Worksheet, Owb As Workbook
Set ws = ActiveSheet
Set Sh = Sheets("RA BUILD")
If Sheets("Bid Results").Range("AR1") = True Then
If DateSerial(ws.Range("J1").Value, ws.Range("G1").Value, ws.Range("H1").Value) < Sh.Range("B1").Value Or _
DateSerial(ws.Range("J1").Value, ws.Range("G1").Value, ws.Range("H1").Value) > Sh.Range("I1").Value Then
MsgBox "CHANGE THE DATE OF THE LAST DAY OF BID IN SHEET BID RESULTS TO CONTUINE EXPORT"
Exit Sub
End If
ElseIf Sheets("Bid Results").Range("AR1") = False Then
If DateSerial(ws.Range("J1").Value, ws.Range("G1").Value, ws.Range("H1").Value) < Sh.Range("AT1").Value Or _
DateSerial(ws.Range("J1").Value, ws.Range("G1").Value, ws.Range("H1").Value) > Sh.Range("BA1").Value Then
MsgBox "CHANGE THE DATE OF THE LAST DAY OF BID IN SHEET BID RESULTS TO CONTUINE EXPORT"
Exit Sub
End If
End If
Dim wb As Workbook
Set ws = ActiveSheet
If Range("H1") = 1 Then
ws.Unprotect Password:="password"
ws.Copy
ws.Protect Password:="password"
With ActiveWorkbook
ws.Cells.Copy .Sheets(1).Range("A1")
ActiveWorkbook.SaveAs fileName:= _
"C:\Schedule\" & Range("I1") & " " & "SCH & EL" & ".xlsx", FileFormat _
:=xlOpenXMLWorkbook, CreateBackup:=False
End With
Call FormulaToValue
ActiveSheet.Name = Range("H1")
ws.Protect Password:="password"
ActiveWorkbook.Save
Else
Dim ws1 As Worksheet, wb2 As Workbook
Application.ScreenUpdating = False
Set wb1 = ActiveSheet
wb1.Unprotect Password:="password"
Set wb2 = Workbooks.Open(" C:\ Schedule\" & Range("I1") & " " & "SCH & EL" & ".xlsx")
wb1.Sheets("BASE SCH").Copy After:=wb2.Sheets(wb2.Sheets.Count)
Application.ScreenUpdating = True
Call FormulaToValue
ActiveSheet.Name = Range("H1")
ws.Protect Password:="password"
ActiveWorkbook.Save
End If
End Sub