Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto Refresh data from original worksheet to newly created copy | Excel Discussion (Misc queries) | |||
using a cell value to name a newly created workbook | Excel Discussion (Misc queries) | |||
Referencing a newly created worksheet | Excel Worksheet Functions | |||
Add list to newly created menu | Excel Discussion (Misc queries) | |||
add list to newly created menu | Excel Worksheet Functions |