Activate New worksheet based on cell text

[KadenFuhriman]

Hey Guys,

I've figured out how to create a bunch of sheets based on some source cells. Now I'm trying to copy the data in the column from each source cell to the page that was created. This is what I've got so far.

Sub AddWorksheetsFromSelection()
Dim CurSheet As Worksheet
Dim Source As Range
Dim c As Range

Set CurSheet = ActiveSheet
Set Source = Selection.Cells
Application.ScreenUpdating = False

For Each c In Source
sName = Trim(c.Text)
If Len(sName) > 0 Then
Worksheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Trim(c.Text)
CurSheet.Activate
c.Select
ActiveCell.EntireColumn.Copy
Worksheets(Worksheets("Front").c.Text).Activate
Range("A1").PasteSpecial
End If
Next c

CurSheet.Activate
Application.ScreenUpdating = True
End Sub

The line in red is where I've been running into problems. What is the best way to activate the sheet that was just created?

Thanks for the help,

Kaden

 

[Yongle]

VBA does not require sheets to be activated or cells selected to copy and paste
This may not be copying what you want, but hopefully you can adapt it to suit your needs

Sub AddWorksheetsFromSelection()
    Dim CurSheet As Worksheet
    Dim Source As Range
    Dim c As Range
    Dim sName As String, ws As Worksheet
    
    Set CurSheet = ActiveSheet
    Set Source = Selection.Cells
    Application.ScreenUpdating = False
    
    For Each c In Source
    sName = Trim(c.Text)
        If Len(sName) > 0 Then
            Set ws = Worksheets.Add(After:=Worksheets(Worksheets.Count))
            ws.Name = sName
            c.EntireColumn.Copy
            ws.Range("A1").PasteSpecial
        End If
    Next c
    
    CurSheet.Activate
    Application.ScreenUpdating = True
End Sub

 

[KadenFuhriman]

Yongle,

That worked perfect! Thanks for the help!

Kaden

 

[Yongle]

thanks for your feedback