Copy data from sheet to another sheet with long formula

Y

yayakele

New Member
Joined
Sep 17, 2023
Messages
3
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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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

P
  • Solved
Excel VBA, Vlookup to another sheet in same work book
Replies
6
Views
180
StephenCrump
S
K
  • Solved
How to copy data from single cells to merge cells and vice versa
2 3
Replies
24
Views
447
Alex Blakenburg
A
K
  • Solved
Jump to cell after clicking message box ok
Replies
3
Views
100
dreid1011
dreid1011
imback2nite
  • Solved
VBA help.
2
Replies
10
Views
366
Peter_SSs
Peter_SSs
A
  • Question
Code to Add Data From UserForm In Row Above or Below Based on Criteria
Replies
1
Views
221
amoverton2
A

Forum statistics

Threads
1,215,069
Messages
6,122,954
Members
449,095
Latest member
nmaske

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