Please help, I've been stuck on this for hours. I have one workbook that is saved as macrobook.xlsm in which column A has a list of worksheet names that I would like to apply a macro to. The worksheets however, are in another workbook called wb05012013.xlsx. The "05012013" refers to a date since the file date is updated and that date value will be in a named cell called "date" in the macrbook.xlsm. I would like the macro to loop through each sheet in wb05012013.xlsx, select all cells and paste values. Then, once the macro comes to a blank cell or end of the list if you will, I'd like it to delete the sheets that are not in the list. Also both of the workbooks are in a shared drive but in the same folder, not sure if that detail makes a difference because the code loops at home, but I get errors at work maybe because the files are stored in shared drives? This is what I have so far...



Sub Newweek()
    Dim mywb As Workbook
    Set mywb = ActiveWorkbook
    Dim wb2 As Workbook
    Workbooks.Open Filename:="E:\archive\wb" & date & ".xlsx"
    Set wb2 = ActiveWorkbook
    
    Dim rngCell As Range
    Dim strSheetActive As String: strSheetActive = ActiveSheet.Name
    Application.ScreenUpdating = False
     
       
        For Each rngCell In .Range("A1:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
            If Trim(rngCell.Value) <> vbNullString Then Call MacroToDoTheWork(rngCell.Value)
        Next rngCell
    
     
    Application.Goto mywb.Worksheets(strSheetActive).Cells(1)
    Application.ScreenUpdating = True
    Set rngCell = Nothing
     
End Sub
 
Private Sub MacroToDoTheWork(strSheetName As String)
    Application.DisplayAlerts = False
    With Worksheets(strSheetName)
        Application.Goto .Cells(1)
        Cells.Select
        Cells.Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues
        
        
    End With
     
End Sub