0

I have two workbooks.

WB_A contains multiple sheets. Relevant sheets are Master_Macro_Test and the last worksheet. This last worksheet is added automatically, therefore it has no defined name. After further processes are done, it will be deleted afterwards anyway.

WB_B contains a single worksheet.

    Dim t As Long, N As Long, c As Collection, M As Long
    Dim z As Long, vd As String, va As String, L As Long
    Set TargetWorkbook = Excel.Workbooks("WB_A.xlsm")

    TargetWorkbook.Activate
    Sheets.Add After:=Sheets(Sheets.Count)  'adding the sheet
        With Range("A1:C1")
            .Value = Array("x", "y", "z")
            .Font.Bold = True
            .Borders(xlEdgeBottom).LineStyle = xlContinuous
        End With
    
    TargetWorkbook.Sheets("Master_Macro_Test").Columns("D:D").Copy TargetWorkbook.Sheets(Sheets.Count).Range("A1")

    TargetWorkbook.Sheets(Sheets.Count).Range("D:D").RemoveDuplicates Columns:=1, Header:=xlYes

    N = Cells(TargetWorkbook.Sheets(Sheets.Count).Rows.Count, "A").End(xlUp).Row
    TargetWorkbook.Sheets("Master_Macro_Test").Activate
    M = Cells(TargetWorkbook.Sheets("Master_Macro_Test").Rows.Count, "D").End(xlUp).Row
    Workbooks.Open(FileName).Activate
    L = Cells(ActiveSheet.Rows.Count, "D").End(xlUp).Row

Working only in the WB_A. Need for switching between mastersheet and last sheet. The last sheet is defined by Sheets(Sheets.Count).

TargetWorkbook.Sheets(Sheets.Count).Activate
For t = 2 To N
    Set c = Nothing
    Set c = New Collection
    vd = Sheets("Master_Macro_Test").Cells(t, "D").Value 'matching x
    For z = 2 To M
        va = Sheets(Sheets.Count).Cells(z, "A").Value 'matching x
        If va = vd Then
            On Error Resume Next
                c.Add TargetWorkbook.Sheets(Sheets.Count).Cells(z, "B").Value, CStr(Cells(z, "B").Value)
            On Error GoTo 0
        End If
    Next z
    TargetWorkbook.Sheets(Sheets.Count).Cells(t, "B").Value = c.Count 'y
Next t

Working between WB_A and WB_B.

For t = 2 To N
    Set c = Nothing
    Set c = New Collection
    vd = TargetWorkbook.Sheets("Master_Macro_Test").Cells(t, "D").Value 'x
    Workbooks.Open(FileName).Activate
    For z = 14 To L
        vs = ActiveSheet.Cells(z, "D").Value 'x
        If vd = vs Then
            On Error Resume Next
                c.Add TargetWorkbook.Sheets(Sheets.Count).Cells(z, "B").Value, CStr(Cells(z, "B").Value)
            On Error GoTo 0
        End If
    Next z
    TargetWorkbook.Worksheets(Worksheets.Count).Activate
    Worksheets(Worksheets.Count).Cells(t, "C").Value = c.Count 'z
Next t

It works, however the .Activate part is really annoying. I would love for the second loop, which allows me to insert data correctly to the last sheet of WB_A, without the need of activting WB_A or WB_B each time.

Audiogott
  • 95
  • 2
  • 12
  • Maybe have a read of https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba - which discusses avoiding `Select` - but the principles remain the same. – CLR Aug 21 '20 at 11:45
  • Sample this link https://www.thespreadsheetguru.com/blog/vba-guide-to-referencing-excel-worksheet-tab to learn worksheets & workbook referencing. – Puntal Aug 21 '20 at 12:37
  • @Puntal, your reference gives a Page not found. – Paul Ogilvie Aug 21 '20 at 13:33
  • 1
    @PaulOgilvie Thank you for bringing it to notice. Correct link as follows https://www.thespreadsheetguru.com/blog/vba-guide-to-referencing-excel-worksheet-tabs – Puntal Aug 21 '20 at 13:43
  • Well, I have tried setting the Shees or Workbooks as variables to reference, however it is not working. Activating each sheet individually seems like an annoying, but necessary step – Audiogott Aug 28 '20 at 10:07

0 Answers0