Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Spreadsheet help moving data [Closed]

Report
Posts
5
Registration date
Wednesday August 21, 2019
Status
Member
Last seen
February 10, 2020
 -
 Blocked Profile -
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
See more

4 replies

Answer 1 / 4

If you wish to use the cell value, this method will work: 


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

Have FUN!
Answer 2 / 4

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.
Answer 3 / 4

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!!!!
Answer 4 / 4

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?

Ask a question