Spreadsheet help moving data

Tbonekiller Posts 16 Registration date Wednesday August 21, 2019 Status Member Last seen July 20, 2022 - Aug 22, 2019 at 02:08 PM
 Blocked Profile - Aug 22, 2019 at 06:24 PM
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

Sheets("10.00").Select 'I want to replace this line with the new macro to look at cell "C11" on the
'current sheet

Selection.Insert Shift:=xlDown
Application.CutCopyMode = False
With Selection.Validation
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.InputMessage = ""
.ShowInput = True
.ShowError = True
End With
Sheets("Check out Sheet").Select
End Sub

4 responses

Blocked Profile
Aug 22, 2019 at 04:01 PM
If you wish to use the cell value, this method will work:

sheetname = ThisWorkbook.Worksheets("Sheet1").Range("A1").Value

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

sheetexist = True
Exit Function

sheetexist = False

End Function

To use it, we would go:

sheetname = ThisWorkbook.Worksheets("Sheet1").Range("A1").Value
doesitExist = sheetexist(sheetname)
if doesitexist then
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


End Sub

All together, it would be like this:

sheetname = ThisWorkbook.Worksheets("Sheet1").Range("A1").Value
doesitexist = sheetexist(sheetname)
If doesitexist Then
makesheet (sheetname)
End If

I hope this helps out, and you have learned something... Let us know what is next.

Have FUN!
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.
ac3mark I changed the last part of

sheetname = ThisWorkbook.Worksheets("Sheet1").Range("A1").Value <<<<here to "text" and it works now

Thank you!!!!
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?