Hello first time poster here, I would like to seek help on how can I simplify or shorten the macro formula on excel, the process of the formula is when a data on cell c4 or cell c10 is equals to a specific text, the data will transfer to another specific sheet,
For sample is if “cutting disk 4 dia” is in cell c4 all data in cell c3 to cell c5 will transfer to sheet "3".
And if “flap wheel 6 dia” is in cell c4 all the data in cell c3 to cell c5 will transfer to sheet 2
here is the sample of a formula i copied here in mrexcel, i edited it and it works, but i have more than 100 specific text to input. is there any work around to make the formula simple?
thank you in advance,
For sample is if “cutting disk 4 dia” is in cell c4 all data in cell c3 to cell c5 will transfer to sheet "3".
And if “flap wheel 6 dia” is in cell c4 all the data in cell c3 to cell c5 will transfer to sheet 2
here is the sample of a formula i copied here in mrexcel, i edited it and it works, but i have more than 100 specific text to input. is there any work around to make the formula simple?
thank you in advance,
Code:
Sub CONSUMABLES1_CLICK()
SHT1RW = Range("Z2")
SHT2RW = Range("Z3")
SHT3RW = Range("Z4")
SHT4RW = Range("Z5")
DATDATI = Range("C3")
ITMNMEI = Range("C4")
QTYQTYI = Range("C5")
DATDATO = Range("C9")
ITMNMEO = Range("C10")
QTYQTYO = Range("C11")
Dim LR As Long, i As Long
With Sheets("Sheet2")
LR = .Range("C" & Rows.Count).End(xlUp).Row
For i = 1 To LR
With .Range("C" & i)
If .Value = "FLAP DISK 4 DIA" Then
Sheets("1").Select
Cells(SHT1RW, 1) = DATDATI
Cells(SHT1RW, 2) = ITMNMEI
Cells(SHT1RW, 3) = QTYQTYI
Cells(SHT1RW, 5) = DATDATO
Cells(SHT1RW, 6) = ITEMNMEO
Cells(SHT1RW, 7) = QTYQTYO
ElseIf .Value = "FLAP WHEEL 6 DIA" Then
Sheets("2").Select
Cells(SHT2RW, 1) = DATDATI
Cells(SHT2RW, 2) = ITMNMEI
Cells(SHT2RW, 3) = QTYQTYI
Cells(SHT2RW, 5) = DATDATO
Cells(SHT2RW, 6) = ITEMNMEO
Cells(SHT2RW, 7) = QTYQTYO
ElseIf .Value = "CUTTING DISK 4 DIA" Then
Sheets("3").Select
Cells(SHT3RW, 1) = DATDATI
Cells(SHT3RW, 2) = ITMNMEI
Cells(SHT3RW, 3) = QTYQTYI
Cells(SHT3RW, 5) = DATDATO
Cells(SHT3RW, 6) = ITEMNMEO
Cells(SHT3RW, 7) = QTYQTYO
ElseIf .Value = "CUTTING DISK 7 DIA" Then
Sheets("4").Select
Cells(SHT4RW, 1) = DATDATI
Cells(SHT4RW, 2) = ITMNMEI
Cells(SHT4RW, 3) = QTYQTYI
Cells(SHT4RW, 5) = DATDATO
Cells(SHT4RW, 6) = ITEMNMEO
Cells(SHT4RW, 7) = QTYQTYO
End If
End With
Next i
End With
Sheets("Sheet2").Select
Range("C3:C35").ClearContents
Range("C5").Select
End Sub