Problem Copying two Sheets from one workbook to another workbook

[LutonBarry]

Folks, Can you help I open a 'Previous Days' workbook from the one I'm working in with the code below and rename two of the worksheets as you can see. I then want to copy each to the other workbook but before a particular sheet in each case.

I've tried to select them as an Array and copy them but that produced an error indicating the sheets already existed, which they didn't. So I'm trying to copy each one across separately. Issue is how do I reactivate the Previous days report to allow me to copy the other sheet over, the Previous Days Report will differ in name i.e. the date suffix each day.



Private Sub PrepareAllSheetImport()
' PrepareAllSheetImport Macro

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Dim myCheck AsInteger
myCheck =MsgBox("Locate and open previous days Upper Tier Report. Continue?",vbYesNo)
If MsgBox("Previous Days YYMMDD Upper Tier Report", vbYesNo) = vbYesThen
Dim myWorkbook As Workbook
myFile = Application.GetOpenFilename()
Workbooks.Open Filename:=myFile
Set myWorkbook = ActiveWorkbook
Sheets("COpen").Select
Selection.AutoFilter
Sheets("COpen").Name = "CAll"
Sheets("CWOpen").Select
Selection.AutoFilter
Sheets("CWOpen").Name = "CWAll"
Sheets("CAll").Select.Copy Before:=Workbooks("Daily Call &WO Report.xlsm").Sheets("COpen")
Sheets("CWAll").Select.Copy Before:=Workbooks("Daily Call &WO Report.xlsm").Sheets("CWOpen")
Else: Application.ActiveWorkbook.Close SaveChanges:=False

End If
Application.DisplayAlerts = False
myWorkbook.Saved= True
myWorkbook.CloseSaveChanges:=False
Application.DisplayAlerts = True
Application.ScreenUpdating = False
Sheets("CAll").Select
Columns("C:C").Insert Shift:=xlToRight,CopyOrigin:=xlFormatFromLeftOrAbove
Columns("A:A").Delete Shift:=xlToLeft
Columns("A:A").Insert Shift:=xlToRight,CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").FormulaR1C1 = "Day"
Range("B1").Copy
Range("A1").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("A2").Select
Selection.CurrentRegion.Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "Yesterday"
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Columns("C:C").Delete Shift:=xlToLeft
End Sub
[/CODE]

[westconn1]

try like

myworkbook.Sheets("CAll").Copy Before:=Workbooks("Daily Call &WO Report.xlsm").Sheets("COpen")
           myworkbook.Sheets("CWAll").Copy Before:=Workbooks("Daily Call &WO Report.xlsm").Sheets("CWOpen")

[LutonBarry]

westconn1, Crikey simple when you know how. I tried several methods and objects not that one. thanks again