Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3
Default How do I return to newly created worksheet?

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

  #2   Report Post  
Posted to microsoft.public.excel.programming
No Name
 
Posts: n/a
Default How do I return to newly created worksheet?

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

-----Original Message-----
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

.

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,824
Default How do I return to newly created worksheet?

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

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 172
Default How do I return to newly created worksheet?

Dim wSht as WorkSheet

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

HTH
Paul
--------------------------------------------------------------------------------------------------------------
Be advised to back up your WorkBook before attempting to make changes.
--------------------------------------------------------------------------------------------------------------
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:


Thanks


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Auto Refresh data from original worksheet to newly created copy wolverine Excel Discussion (Misc queries) 0 April 11th 10 04:10 PM
using a cell value to name a newly created workbook Guerilla Excel Discussion (Misc queries) 1 December 18th 06 02:45 AM
Referencing a newly created worksheet Charyn Excel Worksheet Functions 2 May 2nd 05 04:13 AM
Add list to newly created menu Anthony Excel Discussion (Misc queries) 4 February 25th 05 02:53 AM
add list to newly created menu Anthony Excel Worksheet Functions 1 February 25th 05 01:57 AM


All times are GMT +1. The time now is 06:43 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"