Loop sheets and transfer data

Question

My brain is fried and this is easy points for the usual suspects. div is an array holding sheet names. I am looping through sheets in a master book and if one of the master sheets match one of the sheets in the div array, I want to transfer some data from master sheet to a sheet in thisworkbook.

In the event the sheet does not exist in thisworkbook, add one and name it after the master sheet. What's the most efficient way to do this? I feel like nested loops is a bad idea -_- A collection perhaps?

For i = 0 To UBound(div())

    For Each s In book.Worksheets

        wsName = Left(s.Name, 5)

        If div(i) = wsName Then
        If wsExists(wsName) Then
            Set ws = ThisWorkbook.Worksheets(wsName)
            Exit For
            'Debug.Print "true " & ws.name
        Else
            Set ws = ThisWorkbook.Worksheets.Add
            ws.Name = Left(s.Name, 5)
            'Debug.Print "false " & ws.name
        End If
        end if
    Next

    With ws
    .Columns(Start).Resize(, 2).Value = s.Columns("A:B").Value
    .Columns(Start + label).Resize(, cols).Value = s.Columns(Start + label).Resize(, cols).Value
    End With

Next

Do I even need to check if sheet exists? Code stolen from Tim.

Function wsExists(sName As String) As Boolean
Dim sht As Worksheet

On Error Resume Next
Set sht = ThisWorkbook.Sheets(sName)
On Error GoTo 0
wsExists = Not sht Is Nothing

End Function

Edit: I am calling the loop from a separate routine.

Call drop(thisWB, thisRange, ccArr)

where ccArr is

Dim ccArr() As Variant
ccArr = Array("30500", "30510", "30515", "30530", "30600", "30900", "40500")

The routine where above loop resides opens with

Sub drop(book As Workbook, cols As Integer, div As Variant, Optional startCol As Integer)

but I am getting a byref error trying to pass the array ;_;

Answer 1

Your nested loop is superfluous. You can check the sheet name from div directly against the workbook you want to check it against, then add it if needed.

See the code below, which also addresses the concerns in the edits to your OP. I modified the wsExists function to include a set reference to a particular workbook, which I think makes it more dynamic.

'assumes thisWB  and thisRange set above

Dim ccArr() As String, sList As String
sList = "30500,30510,30515,30530,30600,30900,40500"
ccArr = Split(sList, ",")

drop thisWB, thisRange, ccArr 'assumes thisWb and thisRange are set already

' rest of code
'==================================================

Sub drop(book As Workbook, cols As Integer, div() As String, Optional startCol as Integer)

For i = 0 To UBound(div())

    If wsExists(ThisWorkbook, div(i)) Then
        Set ws = ThisWorkbook.Worksheets(div(i))
        Exit For
        'Debug.Print "true " & ws.name
    Else
        Set ws = ThisWorkbook.Worksheets.Add
        ws.Name = div(i)
    End If

    'i think you need this here, otherwise, it will only work on the last worksheet in your loop
    With ws
        Dim s As Worksheet
        Set s = book.Sheets(div(i))
        .Columns(Start).Resize(, 2).Value = s.Columns("A:B").Value
        .Columns(Start + Label).Resize(, cols).Value = s.Columns(Start + Label).Resize(, cols).Value
    End With

Next

End Sub

Function wsExists(wb As Workbook, sName As String) As Boolean
Dim sht As Worksheet

On Error Resume Next
Set sht = wb.Sheets(sName)
On Error GoTo 0
wsExists = Not sht Is Nothing

End Function

Answer 2

Related to the re-sizing code:

This statement ws.Columns(1).Resize(, 2) translates to "2 million+ rows from column 1 and 2"

The solution you found works well but it's not dynamic (hard-coded last row)

This is how I'd setup the copy of columns:

Option Explicit

Public Sub copyCols()
    Dim ws1 As Worksheet, ws2 As Worksheet, rng1 As Range, rng2 As Range
    Dim cols As Long, lr As Long

    Dim col1 As Long    'renamed from "Start" (VBA keyword - property)
    Dim lbl As Long     'renamed from "label" (VBA keyword - Control object)

    Set ws1 = Sheet1    'ws
    Set ws2 = Sheet2    'book.Worksheets(wsName & "-F")

    col1 = 1
    cols = 2
    lbl = 1

    lr = ws2.Cells(ws2.UsedRange.Row + ws2.UsedRange.Rows.Count, "A").End(xlUp).Row

    Set rng1 = ws1.Range(ws1.Cells(1, col1), ws1.Cells(lr, col1 + 1))
    Set rng2 = ws2.Range("A1:B" & lr)

    rng1.Value2 = rng2.Value2

    Set rng1 = ws1.Range(ws1.Cells(1, col1 + lbl), ws1.Cells(lr, col1 + lbl + cols))
    Set rng2 = ws2.Range(ws2.Cells(1, col1 + lbl), ws2.Cells(lr, col1 + lbl + cols))

    rng1.Value2 = rng2.Value2 
End Sub