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
JJ wrote:
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
--
Dave Peterson