Excel Forum

ฟอรัม Excel, VBA และอื่นๆ ของคนไทยเพื่อประโยชน์ของทุกคนในจักรวาล (Forum Excel, VBA and others of Thai people for everyone in the universe.)
http://snasui.com/

ส่งข้อมูลข้าม Sheet

http://snasui.com/viewtopic.php?t=19920

Page 1 of 1

ส่งข้อมูลข้าม Sheet

Posted: Wed Apr 26, 2023 3:20 pm
by chanakan Cherdchoo
สวัสดีค่ะ อยากปรึกษาค่ะพอดีเขียนโค้ดขึ้นมาแล้ว เป็นการเขียนให้ส่งข้อมูลข้าม Sheet ค่ะ แต่ข้อมูลบางครั้งก็ส่งไปบางครั้งก็ส่งไม่ไปค่ะ ทำให้ข้อมูลมาไม่ครบค่ะ Sheet ใน excel มีอยู่ทั้งหมด 32 sheet ค่ะ

Re: ส่งข้อมูลข้าม Sheet

Posted: Thu Apr 27, 2023 7:05 am
by snasui
:D เขียน Code ไว้แล้วอย่างไรกรุณาแนบไฟล์พร้อม Code ระบุปัญหาที่เป็น ระบุคำตอบที่ต้องการมาด้วยจะได้สะดกต่อการตอบของเพื่อนสมาชิกครับ

Re: ส่งข้อมูลข้าม Sheet

Posted: Thu May 04, 2023 3:25 pm
by chanakan Cherdchoo

Code: Select all

 
Sub SavehatchND3_Click()
Dim answer As VbMsgBoxResult
answer = MsgBox("¤Ø³µéͧ¡Òúѹ·Ö¡¢éÍÁÙÅãªèËÃ×ÍäÁè", vbYesNo, "ºÑ¹·Ö¡¢éÍÁÙÅ")
If answer = vbYes Then
If Range("E9") = "" Or Range("F9") = "" Or Range("G9") = "" Or Range("H9") = "" _
Or Range("I9") = "" Or Range("J9") = "" Or Range("K9") = "" Or Range("T8") = "" Or Range("T9") = "" Or Range("T10") = "" Or Range("T11") = "" Or Range("T12") = "" Or Range("T13") = "" Or Range("T14") = "" Or Range("T15") = "" Or Range("T16") = "" Or Range("T17") = "" Or Range("T18") = "" Or Range("T19") = "" _
Or Range("U8") = "" Or Range("U9") = "" Or Range("U10") = "" Or Range("U11") = "" Or Range("U12") = "" Or Range("U13") = "" Or Range("U14") = "" Or Range("U15") = "" Or Range("U16") = "" Or Range("U17") = "" Or Range("U18") = "" Or Range("U19") = "" Or Range("C6") = "" Or Range("D13") = "" Or Range("L9") = "" Or Range("M9") = "" Or Range("N9") = "" Or Range("O9") = "" Then
MsgBox "â»Ã´ÃкآéÍÁÙÅãËé¤Ãº¶éǹ", vbCritical + vbOKOnly, "á¨é§àµ×͹"
Else
    Dim mylastrow As Long
    mylastrow = Sheets("Hatching").Range("A" & Rows.Count).End(xlUp).Row + 1
    Range("B9").Copy
    Sheets("Hatching").Range("A" & mylastrow).PasteSpecial xlPasteValuesAndNumberFormats, xlPasteSpecialOperationNone, , False
    
    mylastrow = Sheets("Hatching").Range("B" & Rows.Count).End(xlUp).Row + 1
    Range("C9").Copy
    Sheets("Hatching").Range("B" & mylastrow).PasteSpecial xlPasteValuesAndNumberFormats, xlPasteSpecialOperationNone, , False
    
    mylastrow = Sheets("Hatching").Range("C" & Rows.Count).End(xlUp).Row + 1
    Range("D9").Copy
    Sheets("Hatching").Range("C" & mylastrow).PasteSpecial xlPasteValuesAndNumberFormats, xlPasteSpecialOperationNone, , False
     
    mylastrow = Sheets("Hatching").Range("D" & Rows.Count).End(xlUp).Row + 1
    Range("E9").Copy
    Sheets("Hatching").Range("D" & mylastrow).PasteSpecial xlPasteValuesAndNumberFormats, xlPasteSpecialOperationNone, , False
       
    mylastrow = Sheets("Hatching").Range("E" & Rows.Count).End(xlUp).Row + 1
    Range("O9").Copy
    Sheets("Hatching").Range("E" & mylastrow).PasteSpecial xlPasteValuesAndNumberFormats, xlPasteSpecialOperationNone, , False
    
    mylastrow = Sheets("Mortality in Hatch").Range("A" & Rows.Count).End(xlUp).Row + 1
    Range("B9").Copy
    Sheets("Mortality in Hatch").Range("A" & mylastrow).PasteSpecial xlPasteValuesAndNumberFormats, xlPasteSpecialOperationNone, , False
    
    mylastrow = Sheets("Mortality in Hatch").Range("B" & Rows.Count).End(xlUp).Row + 1
    Range("C9:C10").Copy
    Sheets("Mortality in Hatch").Range("B" & mylastrow).PasteSpecial xlPasteValuesAndNumberFormats, xlPasteSpecialOperationNone, , False
    
    mylastrow = Sheets("Mortality in Hatch").Range("C" & Rows.Count).End(xlUp).Row + 1
    Range("D9").Copy
    Sheets("Mortality in Hatch").Range("C" & mylastrow).PasteSpecial xlPasteValuesAndNumberFormats, xlPasteSpecialOperationNone, , False
     
    mylastrow = Sheets("Mortality in Hatch").Range("D" & Rows.Count).End(xlUp).Row + 1
    Range("F9").Copy
    Sheets("Mortality in Hatch").Range("D" & mylastrow).PasteSpecial xlPasteValuesAndNumberFormats, xlPasteSpecialOperationNone, , False
       
    mylastrow = Sheets("Mortality in Hatch").Range("E" & Rows.Count).End(xlUp).Row + 1
    Range("O9").Copy
    Sheets("Mortality in Hatch").Range("E" & mylastrow).PasteSpecial xlPasteValuesAndNumberFormats, xlPasteSpecialOperationNone, , False
    
    mylastrow = Sheets("Nii").Range("A" & Rows.Count).End(xlUp).Row + 1
    Range("B9").Copy
    Sheets("Nii").Range("A" & mylastrow).PasteSpecial xlPasteValuesAndNumberFormats, xlPasteSpecialOperationNone, , False
    
    mylastrow = Sheets("Nii").Range("B" & Rows.Count).End(xlUp).Row + 1
    Range("C9").Copy
    Sheets("Nii").Range("B" & mylastrow).PasteSpecial xlPasteValuesAndNumberFormats, xlPasteSpecialOperationNone, , False
    
    mylastrow = Sheets("Nii").Range("C" & Rows.Count).End(xlUp).Row + 1
    Range("D9").Copy
    Sheets("Nii").Range("C" & mylastrow).PasteSpecial xlPasteValuesAndNumberFormats, xlPasteSpecialOperationNone, , False
    
    mylastrow = Sheets("Nii").Range("D" & Rows.Count).End(xlUp).Row + 1
    Range("I9").Copy
    Sheets("Nii").Range("D" & mylastrow).PasteSpecial xlPasteValuesAndNumberFormats, xlPasteSpecialOperationNone, , False
    
    mylastrow = Sheets("Nii").Range("E" & Rows.Count).End(xlUp).Row + 1
    Range("J9").Copy
    Sheets("Nii").Range("E" & mylastrow).PasteSpecial xlPasteValuesAndNumberFormats, xlPasteSpecialOperationNone, , False
    
    mylastrow = Sheets("Nii").Range("F" & Rows.Count).End(xlUp).Row + 1
    Range("K9").Copy
    Sheets("Nii").Range("F" & mylastrow).PasteSpecial xlPasteValuesAndNumberFormats, xlPasteSpecialOperationNone, , False
    
    mylastrow = Sheets("Nii").Range("G" & Rows.Count).End(xlUp).Row + 1
    Range("L9").Copy
    Sheets("Nii").Range("G" & mylastrow).PasteSpecial xlPasteValuesAndNumberFormats, xlPasteSpecialOperationNone, , False
    
    mylastrow = Sheets("Nii").Range("H" & Rows.Count).End(xlUp).Row + 1
    Range("M9").Copy
    Sheets("Nii").Range("H" & mylastrow).PasteSpecial xlPasteValuesAndNumberFormats, xlPasteSpecialOperationNone, , False
    
    mylastrow = Sheets("Nii").Range("I" & Rows.Count).End(xlUp).Row + 1
    Range("N9").Copy
    Sheets("Nii").Range("I" & mylastrow).PasteSpecial xlPasteValuesAndNumberFormats, xlPasteSpecialOperationNone, , False
    
    mylastrow = Sheets("Nii").Range("J" & Rows.Count).End(xlUp).Row + 1
    Range("O9").Copy
    Sheets("Nii").Range("J" & mylastrow).PasteSpecial xlPasteValuesAndNumberFormats, xlPasteSpecialOperationNone, , False
       
    mylastrow = Sheets("Return in hatch").Range("A" & Rows.Count).End(xlUp).Row + 1
    Range("Q8:Q19").Copy
    Sheets("Return in hatch").Range("A" & mylastrow).PasteSpecial xlPasteValuesAndNumberFormats, xlPasteSpecialOperationNone, , False
    
    mylastrow = Sheets("Return in hatch").Range("B" & Rows.Count).End(xlUp).Row + 1
    Range("R8:R19").Copy
    Sheets("Return in hatch").Range("B" & mylastrow).PasteSpecial xlPasteValuesAndNumberFormats, xlPasteSpecialOperationNone, , False
    
    mylastrow = Sheets("Return in hatch").Range("C" & Rows.Count).End(xlUp).Row + 1
    Range("S8:S19").Copy
    Sheets("Return in hatch").Range("C" & mylastrow).PasteSpecial xlPasteValuesAndNumberFormats, xlPasteSpecialOperationNone, , False
    
    mylastrow = Sheets("Return in hatch").Range("D" & Rows.Count).End(xlUp).Row + 1
    Range("T8:T19").Copy
    Sheets("Return in hatch").Range("D" & mylastrow).PasteSpecial xlPasteValuesAndNumberFormats, xlPasteSpecialOperationNone, , False
    
    mylastrow = Sheets("Return in hatch").Range("E" & Rows.Count).End(xlUp).Row + 1
    Range("U8:U19").Copy
    Sheets("Return in hatch").Range("E" & mylastrow).PasteSpecial xlPasteValuesAndNumberFormats, xlPasteSpecialOperationNone, , False
    
    mylastrow = Sheets("Nii2").Range("A" & Rows.Count).End(xlUp).Row + 1
    Range("B9").Copy
    Sheets("Nii2").Range("A" & mylastrow).PasteSpecial xlPasteValuesAndNumberFormats, xlPasteSpecialOperationNone, , False
    
    mylastrow = Sheets("Nii2").Range("B" & Rows.Count).End(xlUp).Row + 1
    Range("C9").Copy
    Sheets("Nii2").Range("B" & mylastrow).PasteSpecial xlPasteValuesAndNumberFormats, xlPasteSpecialOperationNone, , False
    
    mylastrow = Sheets("Nii2").Range("C" & Rows.Count).End(xlUp).Row + 1
    Range("D9").Copy
    Sheets("Nii2").Range("C" & mylastrow).PasteSpecial xlPasteValuesAndNumberFormats, xlPasteSpecialOperationNone, , False
    
    mylastrow = Sheets("Nii2").Range("D" & Rows.Count).End(xlUp).Row + 1
    Range("G9").Copy
    Sheets("Nii2").Range("D" & mylastrow).PasteSpecial xlPasteValuesAndNumberFormats, xlPasteSpecialOperationNone, , False
    
    mylastrow = Sheets("Nii2").Range("E" & Rows.Count).End(xlUp).Row + 1
    Range("H9").Copy
    Sheets("Nii2").Range("E" & mylastrow).PasteSpecial xlPasteValuesAndNumberFormats, xlPasteSpecialOperationNone, , False
    
    mylastrow = Sheets("Nii2").Range("F" & Rows.Count).End(xlUp).Row + 1
    Range("O9").Copy
    Sheets("Nii2").Range("F" & mylastrow).PasteSpecial xlPasteValuesAndNumberFormats, xlPasteSpecialOperationNone, , False
    
    mylastrow = Sheets("Nonii").Range("A" & Rows.Count).End(xlUp).Row + 1
    Range("B13").Copy
    Sheets("Nonii").Range("A" & mylastrow).PasteSpecial xlPasteValuesAndNumberFormats, xlPasteSpecialOperationNone, , False
    mylastrow = Sheets("Nonii").Range("B" & Rows.Count).End(xlUp).Row + 1
    Range("C13").Copy
    Sheets("Nonii").Range("B" & mylastrow).PasteSpecial xlPasteValuesAndNumberFormats, xlPasteSpecialOperationNone, , False
    mylastrow = Sheets("Nonii").Range("C" & Rows.Count).End(xlUp).Row + 1
    Range("D13").Copy
    Sheets("Nonii").Range("C" & mylastrow).PasteSpecial xlPasteValuesAndNumberFormats, xlPasteSpecialOperationNone, , False
'Other code
Dim lstabc As Range
Dim abc As Range, k As Integer
Dim a As Range
'    mylastrow = Sheets("reportND3").Range("O" & Rows.Count).End(xlUp).Row + 1
With Sheets("HatchND3")
    For Each abc In .Range("T8")
        With Sheets("ReportND3")
            Set lstabc = .Cells(8, .Columns.Count).End(xlToLeft).Offset(0, 1)
        End With
        k = 0
        For Each a In abc.Resize(12)
            lstabc.Offset(k, 0).Value = a.Value
            k = k + 2
        Next a
'            abc.Resize(12).Copy
'            lstabc.PasteSpecial xlPasteValues
'            Application.CutCopyMode = False
    Next abc
End With
'Other code

    
    Sheets("HatchND3").Range("E9").ClearContents
    Sheets("HatchND3").Range("F9").ClearContents
    Sheets("HatchND3").Range("G9").ClearContents
    Sheets("HatchND3").Range("H9").ClearContents
    Sheets("HatchND3").Range("I9").ClearContents
    Sheets("HatchND3").Range("J9").ClearContents
    Sheets("HatchND3").Range("K9").ClearContents
    Sheets("HatchND3").Range("L9").ClearContents
    Sheets("HatchND3").Range("M9").ClearContents
    Sheets("HatchND3").Range("N9").ClearContents
    Sheets("HatchND3").Range("O9").ClearContents
    Sheets("HatchND3").Range("T8:T19").ClearContents
    Sheets("HatchND3").Range("U8:U19").ClearContents
    Sheets("HatchND3").Range("C6").ClearContents
    Sheets("HatchND3").Range("D13").ClearContents
    
    MsgBox "ºÑ¹·Ö¡ÃÒ¡ÒÃàÃÕºÃéÍÂáÅéÇ ", vbInformation + vbOKOnly, "á¨é§ãËé·ÃÒº"
    'Other Code
 ActiveSheet.Visible = False
End If
End If

End Sub
ตรงที่เป็นการส่งข้อมูลไปที่ sheet Nii2 ค่ะ ข้อมูลไม่ค่อยไปค่ะ

Re: ส่งข้อมูลข้าม Sheet

Posted: Thu May 04, 2023 10:13 pm
by snasui
:D ตัวอย่างการปรับ Code สามารถใช้ With เข้ามาช่วยเพื่อให้ Code สั้นลง ช่วยให้ตรวจสอบได้ง่ายขึ้นครับ

Code: Select all

'Other code
Else
    Dim mylastrow As Long
    With Sheets("Hatching")
        mylastrow = .Range("a" & .Rows.Count).End(xlUp).Row + 1
        Range("b9:e9").Copy: .Range("a" & mylastrow).PasteSpecial xlPasteValuesAndNumberFormats
        Range("o9").Copy: .Range("e" & mylastrow).PasteSpecial xlPasteValuesAndNumberFormats
    End With
    
    With Sheets("Mortality in Hatch")
        mylastrow = .Range("a" & .Rows.Count).End(xlUp).Row + 1
        Range("B9").Copy: .Range("A" & mylastrow).PasteSpecial xlPasteValuesAndNumberFormats
        Range("C9:C10").Copy: .Range("B" & mylastrow).PasteSpecial xlPasteValuesAndNumberFormats
        Range("D9").Copy: .Range("C" & mylastrow).PasteSpecial xlPasteValuesAndNumberFormats
        Range("F9").Copy: .Range("D" & mylastrow).PasteSpecial xlPasteValuesAndNumberFormats
        Range("O9").Copy: .Range("E" & mylastrow).PasteSpecial xlPasteValuesAndNumberFormats
    End With
    
    With Sheets("Nii")
        mylastrow = .Range("a" & .Rows.Count).End(xlUp).Row + 1
        Range("b9:d9").Copy: .Range("A" & mylastrow).PasteSpecial xlPasteValuesAndNumberFormats
        Range("i9:o9").Copy: .Range("D" & mylastrow).PasteSpecial xlPasteValuesAndNumberFormats
    End With
'Other code
All times are UTC+07:00
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited