0

I hope someone can help me. I'm trying to create a loop to open different workbooks. The idea is every workbook has it's filename that will be called based on the value found in cell B1. I am able to do so - open, copy & paste (specific range) to the MainWorkbook but it only copies the reference found the on the 1st sheet and not those that are in sheets 2, 3, etc. I'm not so sure if the question is clear but basically each sheet has a different value in cell B1 which corresponds to a file in my shared drive. I got no error or anything but sometimes excel just shows gray. So far, I have below:

Sub OpenSesamé()

On Error Resume Next
Application.AskToUpdateLinks = False
Application.EnableEvents = False
Application.DisplayAlerts = False

Dim MainWrbk As Workbook
Dim starting_ws As Worksheet
Dim varCellValue As String
Set MainWrbk = ThisWorkbook
    varCellValue = Range("B1").Text

    i = 1
    Sheets(i).Select


    Do
        Workbooks.Open "\\Shared_Drive\" & varCellValue & ".xlsx"
        ActiveWorkbook.Sheets("30 MINUTES INTERVAL").Select
        Range("A3:H51").Copy
        MainWrbk.Activate
        Range("C4").PasteSpecial Paste:=xlPasteValues
            Application.CutCopyMode = False
        ActiveWindow.ActivatePrevious
        ActiveWorkbook.Close

        i = i + 1
        Sheets(i).Select

    Loop Until ActiveSheet.Name = "Summ"


End Sub

Thanks in advance!

Mesut Akcan
  • 899
  • 7
  • 19
Tony_Popony
  • 1
  • 3
  • 1
    FYI - it's best to [avoid using `.Activate`/`.Select`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) which can cause unforeseen issues. – BruceWayne Jan 27 '20 at 16:43
  • 2
    Remove `On Error Resume Next`. That just hides errors. – BigBen Jan 27 '20 at 16:44
  • Set workbook variables Instead of `ActiveWindow.ActivatePrevious` and `ActiveWorkbook.Close` – EEM Jan 27 '20 at 16:58
  • You need two loops one to open the workbook, and a different one to go through the worksheets. Suggest to use For each ws in wb.worksheets *(ws and wb variable for worksheet and workbook respectively)* – EEM Jan 27 '20 at 17:04
  • Thanks a lot guys! Indeed taking out "On Error" line helped in debugging. I appreciate it. Well, I tried deactivating the ActivatePrevious - Close commands and it worked. Then I just added few lines to close all other workbooks, other than the main one. I think when the two deactivated lines are there, it loops back to the same Sheet1 reference cell since I already closed that workbook. Then it does the command till the Summ!. – Tony_Popony Jan 29 '20 at 09:06

0 Answers0