VBA - copy tab to new workbook and save

[RobynLouise]

Hi

I have workbooks saved for each of our distribution centres, each workbook contains multiple tabs detailing different things. I want to extract one particular tab out of each of these worksheets, there are 80+ so don't want to do it manually! Using existing VBA that I have tweaked, I've come up with the following. It's not working (I am a beginner) and I'm looking for some help to get it working! This VBA is in a sheet which essentially houses the list of distribution centres. I have named each distribution centre name cell "file 1, file 2" etc

Sub Test1()


TotalCentres = Range("TotalCentres") (this is a countif of how many are in the list)


For bLoop = 1 To TotalCentres

Workbooks.Open Filename:="H:\Template0.xlsm"

' Wait time
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 8
WaitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait WaitTime


Workbooks.Open Filename:= _
"xxxxxxxxxxxxxxxxxxxxxxxxx\profiles" & Range("File" & bLoop) & ".xlsx" _
, Password:="xxx", UpdateLinks:=0

Worksheets("Employment").Activate
Sheets(“Employment”).Copy After.= _
Workbooks(“Template0.xlsm”).Sheets(1)

' Wait time
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 20
WaitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait WaitTime




Workbooks("Template0.xlsm").Activate
Sheets("Employment”).Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False


Application.DisplayAlerts = False
Sheets(1).Delete
Application.DisplayAlerts = True


ActiveWorkbook.SaveAs _
Filename:="xxxxxxxxxxx\profiles\Employment records" & cll_centre & ".xlsm", _
Password:="xxx"



ActiveWorkbook.Close savechanges:=False

' Wait time
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 3
WaitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait WaitTime


Next cll
End With
End Sub



Am I missing something obvious? Is there an easier way to do this (for a beginner!)