Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel marco
I need to be able to enter data in one cell and run a macro where
excel would move that data to a worksheet of the same name. Example: enter 1234 into cell A1. run macro. Cell A1 contents are transferred to worksheet 1234 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel marco
so sheet 1234 would eventually end up with a bunch of cells containing 1234
Right click on the sheet tab where you will enter data in cell A1 and select view code. Paste in code like this: Private Sub Worksheet_Change(ByVal Target As Range) Dim sh as Worksheet, rng as Range If Target.Address = "$A$1" then on Error resume Next set sh = worksheets(Target.value) On error goto 0 if not sh is nothing then if sh.Name = me.Name then exit sub set rng = sh.Cells(rows.count,1).End(xlup)(2) rng.value = target.value on Error goto ErrHandler Application.EnableEvents = False Target.ClearContents end if end if ErrHandler: Application.EnableEvents = True End Sub Adapt to suit your real needs. -- Regards, Tom Ogilvy " wrote: I need to be able to enter data in one cell and run a macro where excel would move that data to a worksheet of the same name. Example: enter 1234 into cell A1. run macro. Cell A1 contents are transferred to worksheet 1234 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel marco
Copy these two code blocks into a standard module:
Sub Foo() Dim Temp As String If ActiveCell = Range("a1") Then Temp = Range("A1").Value If SheetExists(Temp) Then Range("A1").Copy Destination:=Sheets(Temp).Range("A1") Else Worksheets.Add(After:=Sheets(Sheets.Count)).Name = Temp Range("A1").Value = Temp End If End If End Sub Private Function SheetExists(sname) As Boolean ' Returns TRUE if sheet exists in the active workbook Dim x As Object On Error Resume Next Set x = ActiveWorkbook.Sheets(sname) If Err = 0 Then SheetExists = True _ Else SheetExists = False End Function In sheet1 - cell A1 enter jkuck Run macro Foo Hope this helps, Jim May " wrote in message ups.com: I need to be able to enter data in one cell and run a macro where excel would move that data to a worksheet of the same name. Example: enter 1234 into cell A1. run macro. Cell A1 contents are transferred to worksheet 1234 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Marco for IP Address | Excel Worksheet Functions | |||
marco problem in excel 97 | Excel Programming | |||
how to use marco in excel | New Users to Excel | |||
excel Marco question ... need help please | Excel Discussion (Misc queries) | |||
Excel marco upgrade | Excel Programming |