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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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
205
RoryA
RoryA
youbitto
  • Solved
Index match VBA taking too long
2 3
Replies
27
Views
556
Alex Blakenburg
A
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
143
StephenCrump
S
I
  • Question Question
Select Listbox value to be then taken to customer on worksheet
Replies
1
Views
103
ipbr21054
I
B
  • Solved
VBA to check if worksheet exists
Replies
8
Views
314
bdt
B

Forum statistics

Threads
1,223,882
Messages
6,175,165
Members
452,615
Latest member
bogeys2birdies

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