CODEWITHSUNDEEP
excelvba
spenser1235yahoocom
spenser1235yahoocom

Reputation: 43

Choose Worksheet based on Cell Value

Using Excel 365. I am trying to copy a sheet from a template in "Tournament Master Format 36.xls" (All tabs are numbers based on how many teams are in the bracket) based on the number of teams from ThisWorkbook sheet Data, Cell K4.

Sub CopyPaste()

    Dim Size As String

    Size = "ThisWorkbook.Sheets("Data").Range("K4")"

    Workbooks("Tournament Master Format 36.xls").Sheets(Size).Range("A1:AO311").Copy _
    ThisWorkbook.Sheets("Sheet4").Range("A1") 

End Sub

Updated Code:

Sub CopyPaste()

Dim Size As String
Dim i As Integer
Dim SheetNum As String
Dim ws As Worksheet

    Workbooks("Scheduling Test Template.xlsx").Sheets("Data").Activate

For i = 2 To 10
    'Size = Cells(i, 11).Value
    Size = CStr(Workbooks("Scheduling Test 
    Template.xlsx").Sheets("Data").Range("K4").Value)

    'SheetNum = CStr(Cells(i, 10)) + " & Under"
    SheetNum = i
    Debug.Print Size

    If Size > 0 Then
        Set ws = 

    ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets
    (ThisWorkbook.Sheets.Count))
        ws.Name = SheetNum

        Workbooks("Tournament Master Format 36.xls").Sheets(Size)
        .Range("A1:AO311").Copy ThisWorkbook.Sheets(SheetNum).Range ("A1")
    End If
Next i

End Sub

Upvotes: 4

Views: 67

Answers (1)

PuravTheGreat
PuravTheGreat

Reputation: 136

I can't comment because I don't have enough reputation, but try using Debug.Print Size and see if this is the correct value of Size. If it is the correct value, now check whether there exists a sheet with the same value as Size.

Also, I would recommend against using .Activate and instead using:

Size = Workbooks("Scheduling Test Template.xlsx").Sheets("Data").Range("K4").Value

Ps. If you comment under this answer, I will be able to join the conversation with you and braX.

Upvotes: 3

Related Questions