Spreadsheet help moving data Closed

Question

So I'm trying to make a macro that moves data from an input sheet to the appropriate sheet based on information in a given cell that is being entered. I will have over 150 sheets so I'm not wanting to have to look through all the sheets to enter the data. I've created a macro that moves the information and then sorts, totals, etc... once it's on the appropriate sheet, but I was having to make one for each individual sheet meaning 150 + macros which I was spreading throughout multiple user forms so I wouldn't have to look through all the macros to pick the right one. Here is the code I have so far I want to replace the section that names the sheet with something that will look at cell "C11" and move to the sheet with the same name. Any help is appreciated and thanks in advance.

Sub update_10_00()
'
' update_10_00 Macro
' Updates 10.00 mm
'

'
    Sheets("Check out Sheet").Select
    Range("A11:h11").Select
    Selection.Copy


    Sheets("10.00").Select          'I want to replace this line with the new macro to look at cell "C11" on the 
                                                      'current sheet


    Range("A6").Select
    Selection.Insert Shift:=xlDown
    Range("A6:H6").Select
    Application.CutCopyMode = False
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
        :=xlBetween
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .InputMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    Range("A6").Select
    clear_conditional_format_mic_readings
    sort_date
    total_of_dies_ran
    total_coils_produced
    Sheets("Check out Sheet").Select
    Range("A11:g11").Select
    Selection.ClearContents
    Range("A11").Select
    Save
End Sub

ac3mark · Answer

If you wish to use the cell value, this method will work:

sheetname = ThisWorkbook.Worksheets("Sheet1").Range("A1").ValueThisWorkbook.Worksheets(sheetname).Activate 

LEts say that you have a new value in the column....this will error out, because the sheet does not exist.  So to fix this, we can see if it exists first, as in:

Function sheetexist(whatsheet)On Error GoTo NotExistsThisWorkbook.Worksheets(whatsheet).Selectsheetexist = TrueExit FunctionNotExists:sheetexist = FalseEnd Function

To use it, we would go:

sheetname = ThisWorkbook.Worksheets("Sheet1").Range("A1").ValuedoesitExist = sheetexist(sheetname)if doesitexist thenThisWorkbook.Worksheets(sheetname).Activateend if

Then we can take it one step further, to create the sheet, as in:

Sub makesheet(whatsheet)On Error GoTo ExitSub    With ThisWorkbook        .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = whatsheet    End WithExitSub:End Sub

All together, it would be like this:

sheetname = ThisWorkbook.Worksheets("Sheet1").Range("A1").Valuedoesitexist = sheetexist(sheetname)If doesitexist ThenThisWorkbook.Worksheets(sheetname).ActivateElsemakesheet (sheetname)End If

I hope this helps out, and you have learned something...  Let us know what is next.

Have FUN!

Tbonekiller · Answer

So I'm running into a few errors. I'm using a numeric sheet name (ex.. 7.80 or 15.25) and the cell value is activating by the "code name" instead of the "tab name" so the information is going to the wrong sheet. I have over 150+ sheets and I'm organizing information on specific sizes into their own sheets named after their sizes. The formula you provided is what I found before actually and couldn't get it to work which is why I finally posted my own question. I've just now realized from all the searching that's what's happening with the information though. So I'm needing to change your formula to activate the sheets "tab name" or go back to writing 150+ macros and creating user forms for 150+ macros so that people entering the info don't mess something up..... Please help.

Tbonekiller · Answer

ac3mark I changed the last part of 

sheetname = ThisWorkbook.Worksheets("Sheet1").Range("A1").Value <<<<here to "text" and it works now
ThisWorkbook.Worksheets(sheetname).Activate


Thank you!!!!

ac3mark · Answer

So, the data and tab name are different?  Are you going to build a crosswalk? 

I had the impression that the DATA in the cell, and NAME of the sheet was the same, because of this  requirement; "Here is the code I have so far I want to replace the section that names the sheet with something that will look at cell "C11" and move to the sheet with the same name."

I guess I missed the scope?

If your sheet name is in cell "C11", change the lookup value from "A1" to "C11".

What is next?

Spreadsheet help moving data

4 responses

Similar discussions

Subscribe To Our Newsletter!