How do I return to newly created worksheet?

[JJ]

Hello,

I have a workbook with a number of predefined worksheets.
I have a VBA code that formats, manipulates and analyses
data in these worksheets. I create a new worksheet and
copy raw data to it then name the worksheet based on a
value in cell B1. The data is then manipulated. I then
copy a data block from this newly created worksheet to
another prenamed worksheet. My question is how do I get
back to the new worksheet since the name varies. Here is
what I have to create the new worksheet:

'insert new worksheet and copies data to it

Range("A1:K24").Select
Selection.Cut
Sheets.Add
ActiveSheet.Paste
ActiveSheet.move After:=Sheets("Database")

'column width for data block
Columns("A:K").Select
Selection.Columns.AutoFit

'name worksheet
Dim LkName

LkName = Range("B1")

ActiveSheet.Name = LkName

End Sub


Thanks

 

[Guest]

May be not the best solution but it should work.
When you add a new sheet, create an input box where you
will specify the name of newly created sheet.
and then proceed with your code.

good luck

 

[Dave Peterson]

I like to use object variables that represent the current sheet and the new
sheet. Set them to what you want and refer to them when you need to:

Option Explicit
Sub testme01()

Dim curWks As Worksheet
Dim newWks As Worksheet

Set curWks = ActiveSheet
Set newWks = Worksheets.Add

newWks.Move after:=Sheets("database")

curWks.Range("A1:K24").Cut _
Destination:=newWks.Range("a1")

With newWks
.UsedRange.Columns.AutoFit
.Name = .Range("B1").Value
End With

'do your other copying here

curWks.Select
'or
newWks.Select

End Sub

 

[pfsardella]

Dim wSht as WorkSheet

Set wSht = Sheets.Add(After:=Sheets("Database"))
' Do your manipulation and return to sheet.
wSht .Activate

HTH
Paul