Copy data from sheet to another sheet with long formula

Y

yayakele

New Member
Joined
Sep 17, 2023
Messages
4
Office Version
  1. 2021
Platform
  1. Windows
Hi all, i am trying to use formula to copy data from sheet to another, it works, but i think the formula is too long. please make it simple and short

VBA Code: 
Sub simpanresep()
'supaya macro tetap jalan walaupun sheet difilter
ThisWorkbook.Sheets("inputresep").Protect UserInterfaceOnly:=True, AllowFiltering:=True

If IsEmpty(Sheets("inputresep").Range("H10")) = True Then
MsgBox "Nama Ruangan Kosong!!! Resep TIDAK TERSIMPAN!!!", vbInformation, "Perhatian"
End If

If IsEmpty(Sheets("inputresep").Range("H11")) = True Then
MsgBox "Nama Dokter Kosong!!! Resep TIDAK TERSIMPAN!!!", vbInformation, "Perhatian"
End If

If IsEmpty(Sheets("inputresep").Range("H10")) = False Then
If IsEmpty(Sheets("inputresep").Range("H11")) = False Then
Set yaya = Sheets("reseprm")
dataakhir = yaya.Cells(yaya.Rows.Count, "B"). _
    End(xlUp).Offset(0, 0).Row

With yaya
    yaya.Cells(dataakhir + 1, 1).value = Sheets("inputresep").Range("Q6").value
    yaya.Cells(dataakhir + 1, 2).value = Sheets("inputresep").Range("R6").value
    yaya.Cells(dataakhir + 1, 3).value = Sheets("inputresep").Range("S6").value
    yaya.Cells(dataakhir + 1, 4).value = Sheets("inputresep").Range("T6").value
    yaya.Cells(dataakhir + 1, 5).value = Sheets("inputresep").Range("L6").value
    yaya.Cells(dataakhir + 1, 6).value = Sheets("inputresep").Range("M6").value
    yaya.Cells(dataakhir + 1, 7).value = Sheets("inputresep").Range("N6").value
    yaya.Cells(dataakhir + 1, 8).value = Sheets("inputresep").Range("O6").value
    yaya.Cells(dataakhir + 1, 9).value = Sheets("inputresep").Range("H14").value
    yaya.Cells(dataakhir + 1, 10).value = Sheets("inputresep").Range("H15").value
    yaya.Cells(dataakhir + 1, 11).value = Sheets("inputresep").Range("H16").value
    yaya.Cells(dataakhir + 1, 12).value = Sheets("inputresep").Range("U6").value
    yaya.Cells(dataakhir + 1, 13).value = Sheets("inputresep").Range("V6").value
   
    yaya.Cells(dataakhir + 2, 1).value = Sheets("inputresep").Range("Q7").value
    yaya.Cells(dataakhir + 2, 2).value = Sheets("inputresep").Range("R7").value
    yaya.Cells(dataakhir + 2, 3).value = Sheets("inputresep").Range("S7").value
    yaya.Cells(dataakhir + 2, 4).value = Sheets("inputresep").Range("T7").value
    yaya.Cells(dataakhir + 2, 5).value = Sheets("inputresep").Range("L7").value
    yaya.Cells(dataakhir + 2, 6).value = Sheets("inputresep").Range("M7").value
    yaya.Cells(dataakhir + 2, 7).value = Sheets("inputresep").Range("N7").value
    yaya.Cells(dataakhir + 2, 8).value = Sheets("inputresep").Range("O7").value
    yaya.Cells(dataakhir + 2, 9).value = "0"
    yaya.Cells(dataakhir + 2, 10).value = "0"
    yaya.Cells(dataakhir + 2, 11).value = "0"
    yaya.Cells(dataakhir + 2, 12).value = Sheets("inputresep").Range("U7").value
    yaya.Cells(dataakhir + 2, 13).value = Sheets("inputresep").Range("V7").value
  
    yaya.Cells(dataakhir + 3, 1).value = Sheets("inputresep").Range("Q8").value
    yaya.Cells(dataakhir + 3, 2).value = Sheets("inputresep").Range("R8").value
    yaya.Cells(dataakhir + 3, 3).value = Sheets("inputresep").Range("S8").value
    yaya.Cells(dataakhir + 3, 4).value = Sheets("inputresep").Range("T8").value
    yaya.Cells(dataakhir + 3, 5).value = Sheets("inputresep").Range("L8").value
    yaya.Cells(dataakhir + 3, 6).value = Sheets("inputresep").Range("M8").value
    yaya.Cells(dataakhir + 3, 7).value = Sheets("inputresep").Range("N8").value
    yaya.Cells(dataakhir + 3, 8).value = Sheets("inputresep").Range("O8").value
    yaya.Cells(dataakhir + 3, 12).value = Sheets("inputresep").Range("U8").value
    yaya.Cells(dataakhir + 3, 13).value = Sheets("inputresep").Range("V8").value
   
    yaya.Cells(dataakhir + 4, 1).value = Sheets("inputresep").Range("Q9").value
    yaya.Cells(dataakhir + 4, 2).value = Sheets("inputresep").Range("R9").value
    yaya.Cells(dataakhir + 4, 3).value = Sheets("inputresep").Range("S9").value
    yaya.Cells(dataakhir + 4, 4).value = Sheets("inputresep").Range("T9").value
    yaya.Cells(dataakhir + 4, 5).value = Sheets("inputresep").Range("L9").value
    yaya.Cells(dataakhir + 4, 6).value = Sheets("inputresep").Range("M9").value
    yaya.Cells(dataakhir + 4, 7).value = Sheets("inputresep").Range("N9").value
    yaya.Cells(dataakhir + 4, 8).value = Sheets("inputresep").Range("O9").value
    yaya.Cells(dataakhir + 4, 12).value = Sheets("inputresep").Range("U9").value
    yaya.Cells(dataakhir + 4, 13).value = Sheets("inputresep").Range("V9").value
  
    yaya.Cells(dataakhir + 5, 1).value = Sheets("inputresep").Range("Q10").value
    yaya.Cells(dataakhir + 5, 2).value = Sheets("inputresep").Range("R10").value
    yaya.Cells(dataakhir + 5, 3).value = Sheets("inputresep").Range("S10").value
    yaya.Cells(dataakhir + 5, 4).value = Sheets("inputresep").Range("T10").value
    yaya.Cells(dataakhir + 5, 5).value = Sheets("inputresep").Range("L10").value
    yaya.Cells(dataakhir + 5, 6).value = Sheets("inputresep").Range("M10").value
    yaya.Cells(dataakhir + 5, 7).value = Sheets("inputresep").Range("N10").value
    yaya.Cells(dataakhir + 5, 8).value = Sheets("inputresep").Range("O10").value
    yaya.Cells(dataakhir + 5, 12).value = Sheets("inputresep").Range("U10").value
    yaya.Cells(dataakhir + 5, 13).value = Sheets("inputresep").Range("V10").value
   
    yaya.Cells(dataakhir + 6, 1).value = Sheets("inputresep").Range("Q11").value
    yaya.Cells(dataakhir + 6, 2).value = Sheets("inputresep").Range("R11").value
    yaya.Cells(dataakhir + 6, 3).value = Sheets("inputresep").Range("S11").value
    yaya.Cells(dataakhir + 6, 4).value = Sheets("inputresep").Range("T11").value
    yaya.Cells(dataakhir + 6, 5).value = Sheets("inputresep").Range("L11").value
    yaya.Cells(dataakhir + 6, 6).value = Sheets("inputresep").Range("M11").value
    yaya.Cells(dataakhir + 6, 7).value = Sheets("inputresep").Range("N11").value
    yaya.Cells(dataakhir + 6, 8).value = Sheets("inputresep").Range("O11").value
    yaya.Cells(dataakhir + 6, 12).value = Sheets("inputresep").Range("U11").value
    yaya.Cells(dataakhir + 6, 13).value = Sheets("inputresep").Range("V11").value
  
    yaya.Cells(dataakhir + 7, 1).value = Sheets("inputresep").Range("Q12").value
    yaya.Cells(dataakhir + 7, 2).value = Sheets("inputresep").Range("R12").value
    yaya.Cells(dataakhir + 7, 3).value = Sheets("inputresep").Range("S12").value
    yaya.Cells(dataakhir + 7, 4).value = Sheets("inputresep").Range("T12").value
    yaya.Cells(dataakhir + 7, 5).value = Sheets("inputresep").Range("L12").value
    yaya.Cells(dataakhir + 7, 6).value = Sheets("inputresep").Range("M12").value
    yaya.Cells(dataakhir + 7, 7).value = Sheets("inputresep").Range("N12").value
    yaya.Cells(dataakhir + 7, 8).value = Sheets("inputresep").Range("O12").value
    yaya.Cells(dataakhir + 7, 12).value = Sheets("inputresep").Range("U12").value
    yaya.Cells(dataakhir + 7, 13).value = Sheets("inputresep").Range("V12").value
                
    yaya.Cells(dataakhir + 8, 1).value = Sheets("inputresep").Range("Q13").value
    yaya.Cells(dataakhir + 8, 2).value = Sheets("inputresep").Range("R13").value
    yaya.Cells(dataakhir + 8, 3).value = Sheets("inputresep").Range("S13").value
    yaya.Cells(dataakhir + 8, 4).value = Sheets("inputresep").Range("T13").value
    yaya.Cells(dataakhir + 8, 5).value = Sheets("inputresep").Range("L13").value
    yaya.Cells(dataakhir + 8, 6).value = Sheets("inputresep").Range("M13").value
    yaya.Cells(dataakhir + 8, 7).value = Sheets("inputresep").Range("N13").value
    yaya.Cells(dataakhir + 8, 8).value = Sheets("inputresep").Range("O13").value
    yaya.Cells(dataakhir + 8, 12).value = Sheets("inputresep").Range("U13").value
    yaya.Cells(dataakhir + 8, 13).value = Sheets("inputresep").Range("V13").value
  
    yaya.Cells(dataakhir + 9, 1).value = Sheets("inputresep").Range("Q14").value
    yaya.Cells(dataakhir + 9, 2).value = Sheets("inputresep").Range("R14").value
    yaya.Cells(dataakhir + 9, 3).value = Sheets("inputresep").Range("S14").value
    yaya.Cells(dataakhir + 9, 4).value = Sheets("inputresep").Range("T14").value
    yaya.Cells(dataakhir + 9, 5).value = Sheets("inputresep").Range("L14").value
    yaya.Cells(dataakhir + 9, 6).value = Sheets("inputresep").Range("M14").value
    yaya.Cells(dataakhir + 9, 7).value = Sheets("inputresep").Range("N14").value
    yaya.Cells(dataakhir + 9, 8).value = Sheets("inputresep").Range("O14").value
    yaya.Cells(dataakhir + 9, 12).value = Sheets("inputresep").Range("U14").value
    yaya.Cells(dataakhir + 9, 13).value = Sheets("inputresep").Range("V14").value
   
    yaya.Cells(dataakhir + 10, 1).value = Sheets("inputresep").Range("Q15").value
    yaya.Cells(dataakhir + 10, 2).value = Sheets("inputresep").Range("R15").value
    yaya.Cells(dataakhir + 10, 3).value = Sheets("inputresep").Range("S15").value
    yaya.Cells(dataakhir + 10, 4).value = Sheets("inputresep").Range("T15").value
    yaya.Cells(dataakhir + 10, 5).value = Sheets("inputresep").Range("L15").value
    yaya.Cells(dataakhir + 10, 6).value = Sheets("inputresep").Range("M15").value
    yaya.Cells(dataakhir + 10, 7).value = Sheets("inputresep").Range("N15").value
    yaya.Cells(dataakhir + 10, 8).value = Sheets("inputresep").Range("O15").value
    yaya.Cells(dataakhir + 10, 12).value = Sheets("inputresep").Range("U15").value
    yaya.Cells(dataakhir + 10, 13).value = Sheets("inputresep").Range("V15").value
  
    yaya.Cells(dataakhir + 11, 1).value = Sheets("inputresep").Range("Q16").value
    yaya.Cells(dataakhir + 11, 2).value = Sheets("inputresep").Range("R16").value
    yaya.Cells(dataakhir + 11, 3).value = Sheets("inputresep").Range("S16").value
    yaya.Cells(dataakhir + 11, 4).value = Sheets("inputresep").Range("T16").value
    yaya.Cells(dataakhir + 11, 5).value = Sheets("inputresep").Range("L16").value
    yaya.Cells(dataakhir + 11, 6).value = Sheets("inputresep").Range("M16").value
    yaya.Cells(dataakhir + 11, 7).value = Sheets("inputresep").Range("N16").value
    yaya.Cells(dataakhir + 11, 8).value = Sheets("inputresep").Range("O16").value
    yaya.Cells(dataakhir + 11, 12).value = Sheets("inputresep").Range("U16").value
    yaya.Cells(dataakhir + 11, 13).value = Sheets("inputresep").Range("V16").value
   
    yaya.Cells(dataakhir + 12, 1).value = Sheets("inputresep").Range("Q17").value
    yaya.Cells(dataakhir + 12, 2).value = Sheets("inputresep").Range("R17").value
    yaya.Cells(dataakhir + 12, 3).value = Sheets("inputresep").Range("S17").value
    yaya.Cells(dataakhir + 12, 4).value = Sheets("inputresep").Range("T17").value
    yaya.Cells(dataakhir + 12, 5).value = Sheets("inputresep").Range("L17").value
    yaya.Cells(dataakhir + 12, 6).value = Sheets("inputresep").Range("M17").value
    yaya.Cells(dataakhir + 12, 7).value = Sheets("inputresep").Range("N17").value
    yaya.Cells(dataakhir + 12, 8).value = Sheets("inputresep").Range("O17").value
    yaya.Cells(dataakhir + 12, 12).value = Sheets("inputresep").Range("U17").value
    yaya.Cells(dataakhir + 12, 13).value = Sheets("inputresep").Range("V17").value
  
    yaya.Cells(dataakhir + 13, 1).value = Sheets("inputresep").Range("Q18").value
    yaya.Cells(dataakhir + 13, 2).value = Sheets("inputresep").Range("R18").value
    yaya.Cells(dataakhir + 13, 3).value = Sheets("inputresep").Range("S18").value
    yaya.Cells(dataakhir + 13, 4).value = Sheets("inputresep").Range("T18").value
    yaya.Cells(dataakhir + 13, 5).value = Sheets("inputresep").Range("L18").value
    yaya.Cells(dataakhir + 13, 6).value = Sheets("inputresep").Range("M18").value
    yaya.Cells(dataakhir + 13, 7).value = Sheets("inputresep").Range("N18").value
    yaya.Cells(dataakhir + 13, 8).value = Sheets("inputresep").Range("O18").value
    yaya.Cells(dataakhir + 13, 12).value = Sheets("inputresep").Range("U18").value
    yaya.Cells(dataakhir + 13, 13).value = Sheets("inputresep").Range("V18").value
                
    yaya.Cells(dataakhir + 14, 1).value = Sheets("inputresep").Range("Q19").value
    yaya.Cells(dataakhir + 14, 2).value = Sheets("inputresep").Range("R19").value
    yaya.Cells(dataakhir + 14, 3).value = Sheets("inputresep").Range("S19").value
    yaya.Cells(dataakhir + 14, 4).value = Sheets("inputresep").Range("T19").value
    yaya.Cells(dataakhir + 14, 5).value = Sheets("inputresep").Range("L19").value
    yaya.Cells(dataakhir + 14, 6).value = Sheets("inputresep").Range("M19").value
    yaya.Cells(dataakhir + 14, 7).value = Sheets("inputresep").Range("N19").value
    yaya.Cells(dataakhir + 14, 8).value = Sheets("inputresep").Range("O19").value
    yaya.Cells(dataakhir + 14, 12).value = Sheets("inputresep").Range("U19").value
    yaya.Cells(dataakhir + 14, 13).value = Sheets("inputresep").Range("V19").value
  
    yaya.Cells(dataakhir + 15, 1).value = Sheets("inputresep").Range("Q20").value
    yaya.Cells(dataakhir + 15, 2).value = Sheets("inputresep").Range("R20").value
    yaya.Cells(dataakhir + 15, 3).value = Sheets("inputresep").Range("S20").value
    yaya.Cells(dataakhir + 15, 4).value = Sheets("inputresep").Range("T20").value
    yaya.Cells(dataakhir + 15, 5).value = Sheets("inputresep").Range("L20").value
    yaya.Cells(dataakhir + 15, 6).value = Sheets("inputresep").Range("M20").value
    yaya.Cells(dataakhir + 15, 7).value = Sheets("inputresep").Range("N20").value
    yaya.Cells(dataakhir + 15, 8).value = Sheets("inputresep").Range("O20").value
    yaya.Cells(dataakhir + 15, 12).value = Sheets("inputresep").Range("U20").value
    yaya.Cells(dataakhir + 15, 13).value = Sheets("inputresep").Range("V20").value
   
    yaya.Cells(dataakhir + 16, 1).value = Sheets("inputresep").Range("Q21").value
    yaya.Cells(dataakhir + 16, 2).value = Sheets("inputresep").Range("R21").value
    yaya.Cells(dataakhir + 16, 3).value = Sheets("inputresep").Range("S21").value
    yaya.Cells(dataakhir + 16, 4).value = Sheets("inputresep").Range("T21").value
    yaya.Cells(dataakhir + 16, 5).value = Sheets("inputresep").Range("L21").value
    yaya.Cells(dataakhir + 16, 6).value = Sheets("inputresep").Range("M21").value
    yaya.Cells(dataakhir + 16, 7).value = Sheets("inputresep").Range("N21").value
    yaya.Cells(dataakhir + 16, 8).value = Sheets("inputresep").Range("O21").value
    yaya.Cells(dataakhir + 16, 12).value = Sheets("inputresep").Range("U21").value
    yaya.Cells(dataakhir + 16, 13).value = Sheets("inputresep").Range("V21").value
  
    yaya.Cells(dataakhir + 17, 1).value = Sheets("inputresep").Range("Q22").value
    yaya.Cells(dataakhir + 17, 2).value = Sheets("inputresep").Range("R22").value
    yaya.Cells(dataakhir + 17, 3).value = Sheets("inputresep").Range("S22").value
    yaya.Cells(dataakhir + 17, 4).value = Sheets("inputresep").Range("T22").value
    yaya.Cells(dataakhir + 17, 5).value = Sheets("inputresep").Range("L22").value
    yaya.Cells(dataakhir + 17, 6).value = Sheets("inputresep").Range("M22").value
    yaya.Cells(dataakhir + 17, 7).value = Sheets("inputresep").Range("N22").value
    yaya.Cells(dataakhir + 17, 8).value = Sheets("inputresep").Range("O22").value
    yaya.Cells(dataakhir + 17, 12).value = Sheets("inputresep").Range("U22").value
    yaya.Cells(dataakhir + 17, 13).value = Sheets("inputresep").Range("V22").value
   
    yaya.Cells(dataakhir + 18, 1).value = Sheets("inputresep").Range("Q23").value
    yaya.Cells(dataakhir + 18, 2).value = Sheets("inputresep").Range("R23").value
    yaya.Cells(dataakhir + 18, 3).value = Sheets("inputresep").Range("S23").value
    yaya.Cells(dataakhir + 18, 4).value = Sheets("inputresep").Range("T23").value
    yaya.Cells(dataakhir + 18, 5).value = Sheets("inputresep").Range("L23").value
    yaya.Cells(dataakhir + 18, 6).value = Sheets("inputresep").Range("M23").value
    yaya.Cells(dataakhir + 18, 7).value = Sheets("inputresep").Range("N23").value
    yaya.Cells(dataakhir + 18, 8).value = Sheets("inputresep").Range("O23").value
    yaya.Cells(dataakhir + 18, 12).value = Sheets("inputresep").Range("U23").value
    yaya.Cells(dataakhir + 18, 13).value = Sheets("inputresep").Range("V23").value
  
    yaya.Cells(dataakhir + 19, 1).value = Sheets("inputresep").Range("Q24").value
    yaya.Cells(dataakhir + 19, 2).value = Sheets("inputresep").Range("R24").value
    yaya.Cells(dataakhir + 19, 3).value = Sheets("inputresep").Range("S24").value
    yaya.Cells(dataakhir + 19, 4).value = Sheets("inputresep").Range("T24").value
    yaya.Cells(dataakhir + 19, 5).value = Sheets("inputresep").Range("L24").value
    yaya.Cells(dataakhir + 19, 6).value = Sheets("inputresep").Range("M24").value
    yaya.Cells(dataakhir + 19, 7).value = Sheets("inputresep").Range("N24").value
    yaya.Cells(dataakhir + 19, 8).value = Sheets("inputresep").Range("O24").value
    yaya.Cells(dataakhir + 19, 12).value = Sheets("inputresep").Range("U24").value
    yaya.Cells(dataakhir + 19, 13).value = Sheets("inputresep").Range("V24").value
                
    yaya.Cells(dataakhir + 20, 1).value = Sheets("inputresep").Range("Q25").value
    yaya.Cells(dataakhir + 20, 2).value = Sheets("inputresep").Range("R25").value
    yaya.Cells(dataakhir + 20, 3).value = Sheets("inputresep").Range("S25").value
    yaya.Cells(dataakhir + 20, 4).value = Sheets("inputresep").Range("T25").value
    yaya.Cells(dataakhir + 20, 5).value = Sheets("inputresep").Range("L25").value
    yaya.Cells(dataakhir + 20, 6).value = Sheets("inputresep").Range("M25").value
    yaya.Cells(dataakhir + 20, 7).value = Sheets("inputresep").Range("N25").value
    yaya.Cells(dataakhir + 20, 8).value = Sheets("inputresep").Range("O25").value
    yaya.Cells(dataakhir + 20, 12).value = Sheets("inputresep").Range("U25").value
    yaya.Cells(dataakhir + 20, 13).value = Sheets("inputresep").Range("V25").value
    
    yaya.Cells(dataakhir + 21, 1).value = Sheets("inputresep").Range("Q26").value
    yaya.Cells(dataakhir + 21, 2).value = Sheets("inputresep").Range("R26").value
    yaya.Cells(dataakhir + 21, 3).value = Sheets("inputresep").Range("S26").value
    yaya.Cells(dataakhir + 21, 4).value = Sheets("inputresep").Range("T26").value
    yaya.Cells(dataakhir + 21, 5).value = Sheets("inputresep").Range("L26").value
    yaya.Cells(dataakhir + 21, 6).value = Sheets("inputresep").Range("M26").value
    yaya.Cells(dataakhir + 21, 7).value = Sheets("inputresep").Range("N26").value
    yaya.Cells(dataakhir + 21, 8).value = Sheets("inputresep").Range("O26").value
    yaya.Cells(dataakhir + 21, 12).value = Sheets("inputresep").Range("U26").value
    yaya.Cells(dataakhir + 21, 13).value = Sheets("inputresep").Range("V26").value
   
    yaya.Cells(dataakhir + 22, 1).value = Sheets("inputresep").Range("Q27").value
    yaya.Cells(dataakhir + 22, 2).value = Sheets("inputresep").Range("R27").value
    yaya.Cells(dataakhir + 22, 3).value = Sheets("inputresep").Range("S27").value
    yaya.Cells(dataakhir + 22, 4).value = Sheets("inputresep").Range("T27").value
    yaya.Cells(dataakhir + 22, 5).value = Sheets("inputresep").Range("L27").value
    yaya.Cells(dataakhir + 22, 6).value = Sheets("inputresep").Range("M27").value
    yaya.Cells(dataakhir + 22, 7).value = Sheets("inputresep").Range("N27").value
    yaya.Cells(dataakhir + 22, 8).value = Sheets("inputresep").Range("O27").value
    yaya.Cells(dataakhir + 22, 12).value = Sheets("inputresep").Range("U27").value
    yaya.Cells(dataakhir + 22, 13).value = Sheets("inputresep").Range("V27").value
  
    yaya.Cells(dataakhir + 23, 1).value = Sheets("inputresep").Range("Q28").value
    yaya.Cells(dataakhir + 23, 2).value = Sheets("inputresep").Range("R28").value
    yaya.Cells(dataakhir + 23, 3).value = Sheets("inputresep").Range("S28").value
    yaya.Cells(dataakhir + 23, 4).value = Sheets("inputresep").Range("T28").value
    yaya.Cells(dataakhir + 23, 5).value = Sheets("inputresep").Range("L28").value
    yaya.Cells(dataakhir + 23, 6).value = Sheets("inputresep").Range("M28").value
    yaya.Cells(dataakhir + 23, 7).value = Sheets("inputresep").Range("N28").value
    yaya.Cells(dataakhir + 23, 8).value = Sheets("inputresep").Range("O28").value
    yaya.Cells(dataakhir + 23, 12).value = Sheets("inputresep").Range("U28").value
    yaya.Cells(dataakhir + 23, 13).value = Sheets("inputresep").Range("V28").value
   
    yaya.Cells(dataakhir + 24, 1).value = Sheets("inputresep").Range("Q29").value
    yaya.Cells(dataakhir + 24, 2).value = Sheets("inputresep").Range("R29").value
    yaya.Cells(dataakhir + 24, 3).value = Sheets("inputresep").Range("S29").value
    yaya.Cells(dataakhir + 24, 4).value = Sheets("inputresep").Range("T29").value
    yaya.Cells(dataakhir + 24, 5).value = Sheets("inputresep").Range("L29").value
    yaya.Cells(dataakhir + 24, 6).value = Sheets("inputresep").Range("M29").value
    yaya.Cells(dataakhir + 24, 7).value = Sheets("inputresep").Range("N29").value
    yaya.Cells(dataakhir + 24, 8).value = Sheets("inputresep").Range("O29").value
    yaya.Cells(dataakhir + 24, 12).value = Sheets("inputresep").Range("U29").value
    yaya.Cells(dataakhir + 24, 13).value = Sheets("inputresep").Range("V29").value
  
    yaya.Cells(dataakhir + 25, 1).value = Sheets("inputresep").Range("Q30").value
    yaya.Cells(dataakhir + 25, 2).value = Sheets("inputresep").Range("R30").value
    yaya.Cells(dataakhir + 25, 3).value = Sheets("inputresep").Range("S30").value
    yaya.Cells(dataakhir + 25, 4).value = Sheets("inputresep").Range("T30").value
    yaya.Cells(dataakhir + 25, 5).value = Sheets("inputresep").Range("L30").value
    yaya.Cells(dataakhir + 25, 6).value = Sheets("inputresep").Range("M30").value
    yaya.Cells(dataakhir + 25, 7).value = Sheets("inputresep").Range("N30").value
    yaya.Cells(dataakhir + 25, 8).value = Sheets("inputresep").Range("O30").value
    yaya.Cells(dataakhir + 25, 12).value = Sheets("inputresep").Range("U30").value
    yaya.Cells(dataakhir + 25, 13).value = Sheets("inputresep").Range("V30").value
   
    yaya.Cells(dataakhir + 26, 1).value = Sheets("inputresep").Range("Q31").value
    yaya.Cells(dataakhir + 26, 2).value = Sheets("inputresep").Range("R31").value
    yaya.Cells(dataakhir + 26, 3).value = Sheets("inputresep").Range("S31").value
    yaya.Cells(dataakhir + 26, 4).value = Sheets("inputresep").Range("T31").value
    yaya.Cells(dataakhir + 26, 5).value = Sheets("inputresep").Range("L31").value
    yaya.Cells(dataakhir + 26, 6).value = Sheets("inputresep").Range("M31").value
    yaya.Cells(dataakhir + 26, 7).value = Sheets("inputresep").Range("N31").value
    yaya.Cells(dataakhir + 26, 8).value = Sheets("inputresep").Range("O31").value
    yaya.Cells(dataakhir + 26, 12).value = Sheets("inputresep").Range("U31").value
    yaya.Cells(dataakhir + 26, 13).value = Sheets("inputresep").Range("V31").value
  
    yaya.Cells(dataakhir + 27, 1).value = Sheets("inputresep").Range("Q32").value
    yaya.Cells(dataakhir + 27, 2).value = Sheets("inputresep").Range("R32").value
    yaya.Cells(dataakhir + 27, 3).value = Sheets("inputresep").Range("S32").value
    yaya.Cells(dataakhir + 27, 4).value = Sheets("inputresep").Range("T32").value
    yaya.Cells(dataakhir + 27, 5).value = Sheets("inputresep").Range("L32").value
    yaya.Cells(dataakhir + 27, 6).value = Sheets("inputresep").Range("M32").value
    yaya.Cells(dataakhir + 27, 7).value = Sheets("inputresep").Range("N32").value
    yaya.Cells(dataakhir + 27, 8).value = Sheets("inputresep").Range("O32").value
    yaya.Cells(dataakhir + 27, 12).value = Sheets("inputresep").Range("U32").value
    yaya.Cells(dataakhir + 27, 13).value = Sheets("inputresep").Range("V32").value
   
    yaya.Cells(dataakhir + 28, 1).value = Sheets("inputresep").Range("Q33").value
    yaya.Cells(dataakhir + 28, 2).value = Sheets("inputresep").Range("R33").value
    yaya.Cells(dataakhir + 28, 3).value = Sheets("inputresep").Range("S33").value
    yaya.Cells(dataakhir + 28, 4).value = Sheets("inputresep").Range("T33").value
    yaya.Cells(dataakhir + 28, 5).value = Sheets("inputresep").Range("L33").value
    yaya.Cells(dataakhir + 28, 6).value = Sheets("inputresep").Range("M33").value
    yaya.Cells(dataakhir + 28, 7).value = Sheets("inputresep").Range("N33").value
    yaya.Cells(dataakhir + 28, 8).value = Sheets("inputresep").Range("O33").value
    yaya.Cells(dataakhir + 28, 12).value = Sheets("inputresep").Range("U33").value
    yaya.Cells(dataakhir + 28, 13).value = Sheets("inputresep").Range("V33").value
  
    yaya.Cells(dataakhir + 29, 1).value = Sheets("inputresep").Range("Q34").value
    yaya.Cells(dataakhir + 29, 2).value = Sheets("inputresep").Range("R34").value
    yaya.Cells(dataakhir + 29, 3).value = Sheets("inputresep").Range("S34").value
    yaya.Cells(dataakhir + 29, 4).value = Sheets("inputresep").Range("T34").value
    yaya.Cells(dataakhir + 29, 5).value = Sheets("inputresep").Range("L34").value
    yaya.Cells(dataakhir + 29, 6).value = Sheets("inputresep").Range("M34").value
    yaya.Cells(dataakhir + 29, 7).value = Sheets("inputresep").Range("N34").value
    yaya.Cells(dataakhir + 29, 8).value = Sheets("inputresep").Range("O34").value
    yaya.Cells(dataakhir + 29, 12).value = Sheets("inputresep").Range("U34").value
    yaya.Cells(dataakhir + 29, 13).value = Sheets("inputresep").Range("V34").value
      
    yaya.Cells(dataakhir + 30, 1).value = Sheets("inputresep").Range("Q35").value
    yaya.Cells(dataakhir + 30, 2).value = Sheets("inputresep").Range("R35").value
    yaya.Cells(dataakhir + 30, 3).value = Sheets("inputresep").Range("S35").value
    yaya.Cells(dataakhir + 30, 4).value = Sheets("inputresep").Range("T35").value
    yaya.Cells(dataakhir + 30, 5).value = Sheets("inputresep").Range("L35").value
    yaya.Cells(dataakhir + 30, 6).value = Sheets("inputresep").Range("M35").value
    yaya.Cells(dataakhir + 30, 7).value = Sheets("inputresep").Range("N35").value
    yaya.Cells(dataakhir + 30, 8).value = Sheets("inputresep").Range("O35").value
    yaya.Cells(dataakhir + 30, 12).value = Sheets("inputresep").Range("U35").value
    yaya.Cells(dataakhir + 30, 13).value = Sheets("inputresep").Range("V35").value
                   
    yaya.Cells(dataakhir + 31, 1).value = Sheets("inputresep").Range("Q36").value
    yaya.Cells(dataakhir + 31, 2).value = Sheets("inputresep").Range("R36").value
    yaya.Cells(dataakhir + 31, 3).value = Sheets("inputresep").Range("S36").value
    yaya.Cells(dataakhir + 31, 4).value = Sheets("inputresep").Range("T36").value
    yaya.Cells(dataakhir + 31, 5).value = Sheets("inputresep").Range("L36").value
    yaya.Cells(dataakhir + 31, 6).value = Sheets("inputresep").Range("M36").value
    yaya.Cells(dataakhir + 31, 7).value = Sheets("inputresep").Range("N36").value
    yaya.Cells(dataakhir + 31, 8).value = Sheets("inputresep").Range("O36").value
    yaya.Cells(dataakhir + 31, 12).value = Sheets("inputresep").Range("U36").value
    yaya.Cells(dataakhir + 31, 13).value = Sheets("inputresep").Range("V36").value
  
    yaya.Cells(dataakhir + 32, 1).value = Sheets("inputresep").Range("Q37").value
    yaya.Cells(dataakhir + 32, 2).value = Sheets("inputresep").Range("R37").value
    yaya.Cells(dataakhir + 32, 3).value = Sheets("inputresep").Range("S37").value
    yaya.Cells(dataakhir + 32, 4).value = Sheets("inputresep").Range("T37").value
    yaya.Cells(dataakhir + 32, 5).value = Sheets("inputresep").Range("L37").value
    yaya.Cells(dataakhir + 32, 6).value = Sheets("inputresep").Range("M37").value
    yaya.Cells(dataakhir + 32, 7).value = Sheets("inputresep").Range("N37").value
    yaya.Cells(dataakhir + 32, 8).value = Sheets("inputresep").Range("O37").value
    yaya.Cells(dataakhir + 32, 12).value = Sheets("inputresep").Range("U37").value
    yaya.Cells(dataakhir + 32, 13).value = Sheets("inputresep").Range("V37").value
   
    yaya.Cells(dataakhir + 33, 1).value = Sheets("inputresep").Range("Q38").value
    yaya.Cells(dataakhir + 33, 2).value = Sheets("inputresep").Range("R38").value
    yaya.Cells(dataakhir + 33, 3).value = Sheets("inputresep").Range("S38").value
    yaya.Cells(dataakhir + 33, 4).value = Sheets("inputresep").Range("T38").value
    yaya.Cells(dataakhir + 33, 5).value = Sheets("inputresep").Range("L38").value
    yaya.Cells(dataakhir + 33, 6).value = Sheets("inputresep").Range("M38").value
    yaya.Cells(dataakhir + 33, 7).value = Sheets("inputresep").Range("N38").value
    yaya.Cells(dataakhir + 33, 8).value = Sheets("inputresep").Range("O38").value
    yaya.Cells(dataakhir + 33, 12).value = Sheets("inputresep").Range("U38").value
    yaya.Cells(dataakhir + 33, 13).value = Sheets("inputresep").Range("V38").value
  
    yaya.Cells(dataakhir + 34, 1).value = Sheets("inputresep").Range("Q39").value
    yaya.Cells(dataakhir + 34, 2).value = Sheets("inputresep").Range("R39").value
    yaya.Cells(dataakhir + 34, 3).value = Sheets("inputresep").Range("S39").value
    yaya.Cells(dataakhir + 34, 4).value = Sheets("inputresep").Range("T39").value
    yaya.Cells(dataakhir + 34, 5).value = Sheets("inputresep").Range("L39").value
    yaya.Cells(dataakhir + 34, 6).value = Sheets("inputresep").Range("M39").value
    yaya.Cells(dataakhir + 34, 7).value = Sheets("inputresep").Range("N39").value
    yaya.Cells(dataakhir + 34, 8).value = Sheets("inputresep").Range("O39").value
    yaya.Cells(dataakhir + 34, 12).value = Sheets("inputresep").Range("U39").value
    yaya.Cells(dataakhir + 34, 13).value = Sheets("inputresep").Range("V39").value
   
    yaya.Cells(dataakhir + 35, 1).value = Sheets("inputresep").Range("Q40").value
    yaya.Cells(dataakhir + 35, 2).value = Sheets("inputresep").Range("R40").value
    yaya.Cells(dataakhir + 35, 3).value = Sheets("inputresep").Range("S40").value
    yaya.Cells(dataakhir + 35, 4).value = Sheets("inputresep").Range("T40").value
    yaya.Cells(dataakhir + 35, 5).value = Sheets("inputresep").Range("L40").value
    yaya.Cells(dataakhir + 35, 6).value = Sheets("inputresep").Range("M40").value
    yaya.Cells(dataakhir + 35, 7).value = Sheets("inputresep").Range("N40").value
    yaya.Cells(dataakhir + 35, 8).value = Sheets("inputresep").Range("O40").value
    yaya.Cells(dataakhir + 35, 12).value = Sheets("inputresep").Range("U40").value
    yaya.Cells(dataakhir + 35, 13).value = Sheets("inputresep").Range("V40").value
  
    yaya.Cells(dataakhir + 36, 1).value = Sheets("inputresep").Range("Q41").value
    yaya.Cells(dataakhir + 36, 2).value = Sheets("inputresep").Range("R41").value
    yaya.Cells(dataakhir + 36, 3).value = Sheets("inputresep").Range("S41").value
    yaya.Cells(dataakhir + 36, 4).value = Sheets("inputresep").Range("T41").value
    yaya.Cells(dataakhir + 36, 5).value = Sheets("inputresep").Range("L41").value
    yaya.Cells(dataakhir + 36, 6).value = Sheets("inputresep").Range("M41").value
    yaya.Cells(dataakhir + 36, 7).value = Sheets("inputresep").Range("N41").value
    yaya.Cells(dataakhir + 36, 8).value = Sheets("inputresep").Range("O41").value
    yaya.Cells(dataakhir + 36, 12).value = Sheets("inputresep").Range("U41").value
    yaya.Cells(dataakhir + 36, 13).value = Sheets("inputresep").Range("V41").value
       
    End With
    Dim strAnswer As VbMsgBoxResult
    ThisWorkbook.Save
    strAnswer = MsgBox("Resep Telah Ditambahkan ke Database. Apakah Anda ingin Membersihkan Form??", vbQuestion + vbYesNo, "Peringatan!!")
    If strAnswer = vbYes Then
    Call PrintPDF
      Sheets("inputresep").Range("H6").ClearContents
      Sheets("inputresep").Range("H10:H11").ClearContents
      Sheets("inputresep").Range("K4").ClearContents
      Sheets("inputresep").Range("L6:M41").ClearContents

End If
End If

End If
End Sub
 
Last edited by a moderator:

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
Rather than trying to decipher your code, it would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your two sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).
 
Upvote 0
I believe your code could be simplified to:

VBA Code: 
Sub simpanresep()
'
'supaya macro tetap jalan walaupun sheet difilter
'
    ThisWorkbook.Sheets("inputresep").Protect UserInterfaceOnly:=True, AllowFiltering:=True
'
    If IsEmpty(Sheets("inputresep").Range("H10")) = True Then
        MsgBox "Nama Ruangan Kosong!!! Resep TIDAK TERSIMPAN!!!", vbInformation, "Perhatian"
    End If
'
    If IsEmpty(Sheets("inputresep").Range("H11")) = True Then
        MsgBox "Nama Dokter Kosong!!! Resep TIDAK TERSIMPAN!!!", vbInformation, "Perhatian"
    End If
'
    If IsEmpty(Sheets("inputresep").Range("H10")) = False Then
        If IsEmpty(Sheets("inputresep").Range("H11")) = False Then
            Set yaya = Sheets("reseprm")
'
            With yaya
                dataakhir = .Cells(.Rows.Count, "B").End(xlUp).Offset(0, 0).Row
'
                .Cells(dataakhir + 1, 1).Resize(36, 4).Value = .Range("Q6:T41").Value
                .Cells(dataakhir + 1, 5).Resize(36, 4).Value = .Range("L6:O41").Value
                .Cells(dataakhir + 1, 12).Resize(36, 2).Value = .Range("U6:V41").Value
                .Cells(dataakhir + 1, 9).Resize(2, 3).Value = Array(.Range("H14").Value, "0", .Range("H15").Value, "0", .Range("H16").Value, "0")
            End With
'
'---------------------------------------------------------------------------------------------
'
            Dim strAnswer As VbMsgBoxResult
'
            ThisWorkbook.Save
'
            strAnswer = MsgBox("Resep Telah Ditambahkan ke Database. Apakah Anda ingin Membersihkan Form??", vbQuestion + vbYesNo, "Peringatan!!")
'
            If strAnswer = vbYes Then
                Call PrintPDF
'
                Sheets("inputresep").Range("H6").ClearContents
                Sheets("inputresep").Range("H10:H11").ClearContents
                Sheets("inputresep").Range("K4").ClearContents
                Sheets("inputresep").Range("L6:M41").ClearContents
            End If
        End If
    End If
End Sub

You mentioned a 'formula', I don't see one, but the 'Approach' I just provided should perform much faster than your current code, if that is what you are asking for.
 
Upvote 0
mumps said:
Rather than trying to decipher your code, it would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your two sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).
Click to expand...
thank you for the suggestion, next time i'll do better
 
Upvote 0
johnnyL said:
I believe your code could be simplified to:

VBA Code: 
Sub simpanresep()
'
'supaya macro tetap jalan walaupun sheet difilter
'
    ThisWorkbook.Sheets("inputresep").Protect UserInterfaceOnly:=True, AllowFiltering:=True
'
    If IsEmpty(Sheets("inputresep").Range("H10")) = True Then
        MsgBox "Nama Ruangan Kosong!!! Resep TIDAK TERSIMPAN!!!", vbInformation, "Perhatian"
    End If
'
    If IsEmpty(Sheets("inputresep").Range("H11")) = True Then
        MsgBox "Nama Dokter Kosong!!! Resep TIDAK TERSIMPAN!!!", vbInformation, "Perhatian"
    End If
'
    If IsEmpty(Sheets("inputresep").Range("H10")) = False Then
        If IsEmpty(Sheets("inputresep").Range("H11")) = False Then
            Set yaya = Sheets("reseprm")
'
            With yaya
                dataakhir = .Cells(.Rows.Count, "B").End(xlUp).Offset(0, 0).Row
'
                .Cells(dataakhir + 1, 1).Resize(36, 4).Value = .Range("Q6:T41").Value
                .Cells(dataakhir + 1, 5).Resize(36, 4).Value = .Range("L6:O41").Value
                .Cells(dataakhir + 1, 12).Resize(36, 2).Value = .Range("U6:V41").Value
                .Cells(dataakhir + 1, 9).Resize(2, 3).Value = Array(.Range("H14").Value, "0", .Range("H15").Value, "0", .Range("H16").Value, "0")
            End With
'
'---------------------------------------------------------------------------------------------
'
            Dim strAnswer As VbMsgBoxResult
'
            ThisWorkbook.Save
'
            strAnswer = MsgBox("Resep Telah Ditambahkan ke Database. Apakah Anda ingin Membersihkan Form??", vbQuestion + vbYesNo, "Peringatan!!")
'
            If strAnswer = vbYes Then
                Call PrintPDF
'
                Sheets("inputresep").Range("H6").ClearContents
                Sheets("inputresep").Range("H10:H11").ClearContents
                Sheets("inputresep").Range("K4").ClearContents
                Sheets("inputresep").Range("L6:M41").ClearContents
            End If
        End If
    End If
End Sub

You mentioned a 'formula', I don't see one, but the 'Approach' I just provided should perform much faster than your current code, if that is what you are asking for.
Click to expand...
thank you, i tried the code, but it's not what i want. the result is different..i imagine something like do while until etc. to reduce repeated code
 
Upvote 0
@yayakele, I hope you realize that I did not attempt to change the results of the code that you originally posted, are you saying that the results are not the same as your original code that you posted? If it was different, please let us know how the results were different.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Could not set the RowSource property. Invalid property value. Error 380
2
Replies
16
Views
267
RoryA
RoryA
youbitto
  • Solved
Index match VBA taking too long
2 3
Replies
27
Views
616
Alex Blakenburg
A
I
  • Solved
Date formula not applied the same to all Listbox entries
Replies
2
Views
47
ipbr21054
I
I
  • Solved
Listbox on userform to show results in reverse order
2
Replies
16
Views
351
Fuji
F
S
  • Question Question
Input Data Macro but I can't sum 3 cells from Sheet 1 before submitting to a cell in Sheet 2
Replies
3
Views
148
StephenCrump
S

Forum statistics

Threads
1,224,817
Messages
6,181,149
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top