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
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 NotExists
ThisWorkbook.Worksheets(whatsheet).Select sheetexist = True Exit Function
NotExists: sheetexist = False
End Function
To use it, we would go:
sheetname = ThisWorkbook.Worksheets("Sheet1").Range("A1").Value doesitExist = sheetexist(sheetname) if doesitexist then ThisWorkbook.Worksheets(sheetname).Activate end 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 With
ExitSub:
End Sub
All together, it would be like this:
sheetname = ThisWorkbook.Worksheets("Sheet1").Range("A1").Value doesitexist = sheetexist(sheetname) If doesitexist Then ThisWorkbook.Worksheets(sheetname).Activate Else makesheet (sheetname) End If
I hope this helps out, and you have learned something... Let us know what is next.
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.
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".
