Put sheet name in column

[thomasks]

I need to take a sheet name that is created from the input of an input box, and display it in a column for each row that was selected on a previous sheet.
What the user does is select multiple rows on a worksheet, then selects an option from a menu to assign these rows to a new worksheet, when the user inputs the name to be used for the new worksheet, it is created and the data that was selected is copied to the new sheet.
I would like to display in column "A" on each row that was selected on the first sheet, the name of the newly created worksheet used to copy the data to.
This way you can see where all the data was placed as your working down the first sheet. As you will return to make other sheets.
Thanks for your help.

 

[SkipVought]

Hi,

So what code do you have so far? I really could not follow your explanation.

What exactly do you need help with?

Skip,

_[red][/red]

 

[stevexff]

thomas

If you put the name of the target worksheet into the first row on the 'from' sheet, you will overwrite the data. It would be safer to programmatically add a comment to the cell - you'd still have the same information available, but it wouldn't destroy the contents of the cell.

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[thomasks]

Here is the code I have to make the new area sheet and put it's name in:

Public Sub cmdArea_Click()
Dim sSheetName As String
Dim MyRange As Range

Set MyRange = Range("A1", "J1")
MyRange.Copy

sSheetName = InputBox("Enter name for new sheet")
'If user does not name the sheet then exit the sub
If sSheetName = "" Then GoTo errhandler
'Create the new sheet with the area name
Sheets.Add
ActiveSheet.Name = sSheetName
On Error GoTo errhandler

'Paste the data that was selected on the formatted data sheet into the new sheet
ActiveSheet.Paste
'Paste the header row above the inserted data
Rng.Copy
ActiveSheet.Cells(2, 1).Select
ActiveSheet.Paste

'Move the new sheet to the end
Sheets(sSheetName).Select
ActiveWorkbook.Sheets(sSheetName).Select
ActiveWorkbook.Sheets(sSheetName).Move After:=Sheets(Sheets.Count)

'Autofit the columns of the new sheet
ActiveSheet.Cells.Select
Selection.Columns.AutoFit
ActiveSheet.Rows("1:1").Select
Selection.Insert Shift:=xlDown
ActiveSheet.Range("b1:j1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With

'Change the text on the formatted data sheet to red to show that it has already been done
ActiveSheet.Range("b2").Select
ActiveSheet.Range("A2").cell.Text = sSheetName
Rng.Font.Color = vbRed

'Select row 3 and freeze panes for scrolling on the new sheet
ActiveSheet.Rows("3:3").Select
ActiveWindow.FreezePanes = True
ActiveSheet.Range("b3").Select
ActiveSheet.Cells(1, 2) = sSheetName

'This code is to let the progam know that it is ok to run the branches command and handle any
'errors
SheetHasRun = False
errhandler:
Exit Sub

End Sub

What I want to happen is for the name of the new sheet that was created in this sub to be displayed in the first column (that is empty now) of the sheet that the rows were selected from (the target) only on the rows that were selected.
Thanks,

Kirk

 

[PHV]

You wanted this ?
MyRange.Columns(1).Value = sSheetName

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886