Looping through Workbooks with multiple named exceptions

[AdamMalbon]

Sorry if this is similar to previous posts. I can't quite find one that solves my problem. I am attempting to compile multiple daily data tables into a database containing several years of information. I want to skip my personal macro workbook, as well as the sheet I am pasting into. This code works until it hits my master workbook ("FRMC.xlsm"). It will not jump to the next open workbook, it just loops through the if statement perpetually. Code below:

Sub compileFR()


Dim wb As workbook


For Each wb In Application.Workbooks
    If wb.Name = "Personal.xlsb" Or wb.Name = "frmc.xlsm" Then
        'Do nothing
        Else
 
 
    Sheets("MASTER").Select
    
                Range("b3", Range("b3").End(xlDown).Offset(0, 12)).Select
                selection.Copy
                
                Windows("frmc.xlsm").Activate
                Range("a3").End(xlDown).Offset(1, 0).Select
                
                selection.PasteSpecial Paste:=xlPasteValues
                
                Columns("A:A").Select
                selection.NumberFormat = "m/d/yyyy"
                ActiveWorkbook.Save
                
                Columns("A:M").Select
                 ActiveSheet.Range("$A$1:$M$25795").RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13), Header:=xlYes
                
                Application.CutCopyMode = False
                
                Application.DisplayAlerts = False
                wb.Close (n)
               
                
End If
Next
End Sub

 

[Yongle]

should the code not be...

[COLOR=#ff0000]wb.[/COLOR]Sheets("MASTER").Select

 

[AdamMalbon]

Thanks Yongle. Unfortunately it didn't solve my issue. I also tried resetting the variable within the For Each. No good.

Sub compileFR()

Dim wb As Workbook
Set wb = ActiveWorkbook

For Each wb In Application.Workbooks
   
  Set wb = ActiveWorkbook
  If wb.Name = "Personal.xlsb" Or wb.Name = "frmc.xlsm" Then
         'Do nothing
        Else
 wb.Sheets("MASTER").Select
    
                Range("b3", Range("b3").End(xlDown).Offset(0, 12)).Select
                selection.Copy
                
                Windows("frmc.xlsm").Activate
                Range("a3").End(xlDown).Offset(1, 0).Select
                
                selection.PasteSpecial Paste:=xlPasteValues
                
                Columns("A:A").Select
                selection.NumberFormat = "m/d/yyyy"
                ActiveWorkbook.Save
                
                Columns("A:M").Select
                 ActiveSheet.Range("$A$1:$M$25795").RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13), Header:=xlYes
                
                
                    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add Key:=Range _
        ("A:A"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
'        .Apply
    End With
    
    
                Application.CutCopyMode = False
                
                Application.DisplayAlerts = False
                wb.Close (n)
               

End If
Next
End Sub

 

[Yongle]

Why have you added

Set wb = ActiveWorkbook

If you want wb activated try..

wb.Activate

 

[AdamMalbon]

I thought it would help if I reset the variable within the code

Why have you added

Set wb = ActiveWorkbook

If you want wb activated try..

wb.Activate

 

[AdamMalbon]

...within the loop

 

[Yongle]

We are not moving forward here so let's start again. I need to understand what you are trying to achieve.

I looked at your original code and noticed that it is looping through all open workbooks and closing them all (except for the 2 that are being excluded) but there is no other reference to them in your code. Presumably you want to do something other than close them

Which range do you want to be copied from which worksheet in which workbooks and where do you expect it to be pasted to?

Thanks

 

[AdamMalbon]

Ok; I have data in hundreds of workbooks (one for each day for ~4yrs). The data is I columns "A:M". The height of the range varies as this is a dynamic workforce report.

I want to paste the data into a master copy (FRMC.xlsm). I want to keep open FRMC and my personal macro book, because I am only opening 10-15 books at a time.

Thanks for your time.

We are not moving forward here so let's start again. I need to understand what you are trying to achieve.

I looked at your original code and noticed that it is looping through all open workbooks and closing them all (except for the 2 that are being excluded) but there is no other reference to them in your code. Presumably you want to do something other than close them

Which range do you want to be copied from which worksheet in which workbooks and where do you expect it to be pasted to?

Thanks

 

[Yongle]

You did not answer my question in full, so rather than guessing, I will make comments and leave you to amend the code accordingly

The problem is that you are relying on active workbooks and active sheets rather than telling VBA which specific workbook and which specific sheet to amend.
Selecting ranges is usually unnecessary, but VBA does need to know what to copy or paste and where to find that range
I suggest you go back to your original code and ensure that every range has a reference to both workbook and worksheet (if a worksheet variable has been set then it only needs that variable)

Declare your master sheet and workbook so that they can be referred to later

Dim frmc as Workbook, master As Worksheet
Set frmc = Workbooks("frmc.xlsm")
Set master = frmc.Sheets("Master")

Instead of:
Range("b3", Range("b3").End(xlDown).Offset(0, 12)).Select
selection.Copy
If this is the first sheet in workbook wb, use this

wb.Sheets(1).Range("b3", Range("b3").End(xlDown).Offset(0, 12)).Copy

Instead of:
Windows("frmc.xlsm").Activate
Range("a3").End(xlDown).Offset(1, 0).Select
selection.PasteSpecial Paste:=xlPasteValues

master.Range("a3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues

etc

 

[Yongle]

Apologies for the extra line
Range("b3",
(using my mobile phone)