Assign ID number to cell based on the max value of a column located in other sheet

[vitorpadovam]

Hi guys,

I've been trying to do this by myself but no luck so far.
I have two sheets one that works like a database and the other that is used to "feed" this database.
On the feed sheet I want to add to the "title" of the data beeing fed it's ID number. This ID number needs to be defined based on the max value of a column located in the database sheet (so it can remain in sequential order).

Below is the code that I am trying to use:

Sub Num_MTR()


NUMB = Application.WorksheetFunction.Max(Worksheets("CONTROLE").Columns("0")) 'Sheet "CONTROLE"is the database one.


Sheets("MTR").Select
Range("C1").Select = "MANIFESTO DE TRANSPORTE DE RESÍDUOS Nº " & NUMB 'Sheet "MTR" the one being fed.


End Sub


Trying to elaborate a bit more. I want cell C1 on MTR sheet to display the text "MANIFESTO DE TRANSPORTE DE RESÍDUOS Nº " along with the value of the variable NUMB.


Thanks for helping!! Sorry about the portuguese on the sheet names and etc.

 

[Sixthsense]

Replace .select

Range("C1").Select

With

.Value

 

[Sixthsense]

Wrong number specified in columns property. Since there is no Zero column available in excel

Columns("0")

 

[vitorpadovam]

Thanks Sixthsense!

Now the code is working:

Sub Limpar_MTR()


' limpacampos Macro


Range("C3").Select
Selection.ClearContents
Range("C5").Select
Selection.ClearContents
Range("C7").Select
Selection.ClearContents
Range("H3").Select
Selection.ClearContents
Range("J3").Select
Selection.ClearContents
Range("H5").Select
Selection.ClearContents
Range("H7").Select
Selection.ClearContents

' Dados do Transportador

If MsgBox("Manter os dados do transportador?", vbYesNo, "Responda antes de prosseguir") = vbNo Then

Range("B17").Select
Selection.ClearContents
Range("B19").Select
Selection.ClearContents
Range("B21").Select
Selection.ClearContents
Range("B23").Select
Selection.ClearContents
Range("G17").Select
Selection.ClearContents
Range("G19").Select
Selection.ClearContents
Range("G21").Select
Selection.ClearContents
Range("G23").Select
Selection.ClearContents
End If

' Dados do Receptor
If MsgBox("Manter os dados do receptor?", vbYesNo, "Responda antes de prosseguir") = vbNo Then

Range("B26").Select
Selection.ClearContents
Range("G26").Select
Selection.ClearContents
Range("B28").Select
Selection.ClearContents
Range("G28").Select
Selection.ClearContents
Range("B30").Select
Selection.ClearContents
Range("G30").Select
Selection.ClearContents
Range("B32").Select
Selection.ClearContents
Range("G32").Select
Selection.ClearContents
End If

End Sub


Sub Enviar_CONTROLE()


'verificar se todos os valores foram inseridos
If Range("C3").Value = "" Or Range("C5").Value = "" Or Range("C7").Value = "" Or Range("H3").Value = "" Or Range("J3").Value = "" Or Range("H5").Value = "" Or Range("H7").Value = "" Or Range("I10").Value = "" Or Range("B17").Value = "" Or Range("B19").Value = "" Or Range("B21").Value = "" Or Range("B23").Value = "" Or Range("G17").Value = "" Or Range("G19").Value = "" Or Range("G21").Value = "" Or Range("G23").Value = "" Or Range("B26").Value = "" Or Range("B28").Value = "" Or Range("B30").Value = "" Or Range("B32").Value = "" Or Range("G26").Value = "" Or Range("G28").Value = "" Or Range("G30").Value = "" Or Range("G32").Value = "" Then


MsgBox "Preencha os campos em cinza antes de concluir a operação ou selecione a opção de preenchimento manual"
Range("C3").Select


Exit Sub


Else


'Caso as células com informações não estejam em branco, executar a macro normalmente
residuo = Cells(3, 3)
quantidade = Cells(3, 8)
unidade = Cells(3, 10)
etapa = Cells(5, 3)
Data = Cells(10, 9)
estado = Cells(5, 8)
acondicionamento = Cells(7, 3)
disposicao = Cells(7, 8)
transprazao = Cells(17, 2)
veiculo = Cells(21, 2)
placa = Cells(21, 7)
motorista = Cells(23, 2)
cinmetro = Cells(23, 7)
receptor = Cells(26, 2)


Sheets("CONTROLE").Select
Range("B4").End(xlDown).Offset(1, 0) = Data
Range("C4").End(xlDown).Offset(1, 0) = residuo
Range("F4").End(xlDown).Offset(1, 0) = quantidade
Range("G4").End(xlDown).Offset(1, 0) = unidade
Range("N4").End(xlDown).Offset(1, 0) = etapa
Range("O4").End(xlDown).Offset(1, 0) = estado
Range("E4").End(xlDown).Offset(1, 0) = acondicionamento
Range("M4").End(xlDown).Offset(1, 0) = disposicao
Range("H4").End(xlDown).Offset(1, 0) = transprazao
Range("I4").End(xlDown).Offset(1, 0) = veiculo
Range("J4").End(xlDown).Offset(1, 0) = placa
Range("Q4").End(xlDown).Offset(1, 0) = motorista
Range("P4").End(xlDown).Offset(1, 0) = cinmetro
Range("K4").End(xlDown).Offset(1, 0) = receptor
Range("R4").End(xlDown).Offset(1, 0) = "A"


End If


Sheets("MTR").Activate


Range("c4").Select


Limpar_MTR


End Sub