Excel VBA activate worksheet

Question

I need to activate a specific worksheet. The code is meant to create worksheets with a specif name. I need to paste something from a another worksheet into all these newly created worksheets. The code that I'm using is below. But I'm having a hard time activating the newly created worksheet to paste what I want.

Sub octo()

'Dim ws As Worksheet
    Dim Ki As Range
    Dim ListSh As Range
    Workbooks.Open ("C:\Users\Dash\Dropbox\Randika\Misc\Emmash timesheets\timesheet.xlsx")
    With Worksheets("PPE 05-17-15")
        Set ListSh = .Range("B4:B" & .Cells(.Rows.Count, "B").End(xlUp).Row)
    End With

    On Error Resume Next
    For Each Ki In ListSh
        If Len(Trim(Ki.Value)) > 0 Then
            If Len(Worksheets(Ki.Value).Name) = 0 Then

                Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = Ki.Value
'open template
    Workbooks.Open ("C:\Users\Dash\Dropbox\Randika\Misc\Emmash timesheets\octo_template.xls")
    Range("A1:L31").Select
    Selection.Copy

    Worksheets(Ki.Value).Activate

        If ThisWorkbook.Saved = False Then
        ThisWorkbook.Save
    End If
            End If
        End If
    Next Ki

End Sub

There is no need to Activate or Select to do this [see here for some ideas on how to avoid these](http://stackoverflow.com/a/10717999/445425) — chris neilsen, Jun 02 '15 at 21:20

Olle Sjögren · Answer 1 · 2015-06-02T22:00:42.343

Both Workbooks.Open and Worksheets.Add return references to the opened and added objects, which you can use to directly access and modify them - and in your case, to paste data.

Example:

Dim oSourceSheet As Worksheet
Dim oTargetSheet As Worksheet

Set oSourceSheet = Sheet1    'Set reference to any sheet, Sheet1 in my example
Set oTargetSheet = Worksheets.Add(After:=Worksheets(Worksheets.Count))
oSourceSheet.Range("A1:L31").Copy
oTargetSheet.Paste

Set oSourceSheet = Nothing
Set oTargetSheet = Nothing

score 0 · Accepted Answer · answered Jun 03 '15 at 08:31

I think that is what you need.
As what been mentioned by chris, there is no need Activate or Select. Hope the following code solve your problem.

Option Explicit
Dim MyTemplateWorkbook As Workbook
Dim MyDataWorkbook As Workbook
Dim MyTemplateWorksheet As Worksheet
Dim MyDataWorksheet As Worksheet
Dim MyNewDataWorksheet As Worksheet
Dim CurrentRange As Range
Dim ListRange As Range

Sub AddWSAndGetData()

Set MyTemplateWorkbook = Workbooks.Open("C:\Users\lengkgan\Desktop\Testing\MyTemplate.xlsx")
Set MyTemplateWorksheet = MyTemplateWorkbook.Sheets("Template")
Set MyDataWorkbook = Workbooks.Open("C:\Users\lengkgan\Desktop\Testing\MyData1.xlsx")
Set MyDataWorksheet = MyDataWorkbook.Sheets("PPE 05-17-15")
Set ListRange = MyDataWorksheet.Range("B4:B" & MyDataWorksheet.Cells(Rows.Count, "B").End(xlUp).Row)
Application.ScreenUpdating = False

On Error Resume Next
For Each CurrentRange In ListRange
If Len(Trim(CurrentRange.Value)) > 0 Then
    If Len(MyDataWorksheet(CurrentRange.Value).Name) = 0 Then

    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = CurrentRange.Value
    Set MyNewDataWorksheet = MyDataWorkbook.Sheets(ActiveSheet.Name)
    MyNewDataWorksheet.Range("A1:L31").Value = MyTemplateWorksheet.Range("A1:L31").Value

    If MyDataWorkbook.Saved = False Then
        MyDataWorkbook.Save
    End If

    End If
End If
Next CurrentRange
MyTemplateWorkbook.Close (False) 'Close the template without saving
End Sub

Excel VBA activate worksheet

2 Answers2