เรียนถามเรื่อง Code ค่ะ
Code ที่เป็นปัญหา
.Range("AA2:BB2").Resize(.Range("BC1")).Copy ไม่สามารถใช้ร่วมกับโค๊ดทั้งชุดได้ค่ะ
ความตัองการให้โค๊ดนำข้อมูลไปวางที่ชีท Sheet1 ไฟล์ Paid_BookShare ฟ้องตามรูปแนบค่ะ แต่ใช้โค๊ดเดิม
.Range("A2:J2").Copy นี้สามารถวางข้อมูลได้ ต้องการปรับใช้
.Range("AA2:BB2").Resize(.Range("BC1")).Copy นี้ค่ะ
Code: Select all
With formBook.Sheets("TemBilling")
'.Range("A2:J2").Copy
.Range("AA2:BB2").Resize(.Range("BC1")).Copy
wbShare.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp) _
.Offset(1, 0).PasteSpecial xlPasteValues
End With
Code ทั้งชุดค่ะ
Code: Select all
Sub BeenArPay()
Dim wbShare As Workbook
Dim wb As Workbook ' declare wb as workbook
Dim wdShare As Workbook
Dim wkShare As Workbook
Dim formBook As Workbook
Dim wdShareOpen As Boolean
Dim wkShareOpen As Boolean
Dim rSource As Range
Dim rTarget As Range
Dim rs As Range
Dim rt As Range
Dim rk As Range
Dim i As Double
Set formBook = ThisWorkbook
Set wbShare = Workbooks("Paid_BookShare.xlsx")
Set wkShare = Workbooks("MyPay_BookShare.xlsx")
For Each wb In Workbooks ' loop wb not loop wdShare
If wb.Name = "Ph_BookShare.xlsx" Then
wdShareOpen = True
End If
Next wb
If Not wdShareOpen Then
ChDir "\\Server\DATA (E)\My P S Project.xls\PS.BookShare\AR.ÃкºÅ١˹Õé"
Workbooks.Open Filename:="\\Server\DATA (E)\My P S Project.xls\PS.BookShare\AR.ÃкºÅ١˹Õé\Ph_BookShare.xlsx"
End If
Set wdShare = Workbooks("Ph_BookShare.xlsx") 'set wdShare after open not before open
With formBook.Sheets("Form")
Set rSource = .Range("B3:B50")
End With
With wdShare.Sheets("Sheet1")
Set rTarget = .Range("f2", .Range("f" & Rows.Count).End(xlUp))
End With
With formBook.Sheets("Form")
i = (.Range("L4") + .Range("M11") + .Range("M12"))
If i <> .Range("J12") Then
MsgBox "â»Ã´µÃǨ¨Ó¹Ç¹à§Ô¹áÅкѹ·Ö¡ãËÁè"
Exit Sub
End If
End With
Application.Calculation = xlCalculationManual
For Each rs In rSource
For Each rt In rTarget
If rt = rs Then rt.Offset(0, 28).Resize(, 2) = "Y"
Next rt
Next rs
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = False
Set rt = wbShare.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
With formBook.Sheets("TemBilling")
'.Range("A2:J2").Copy
.Range("AA2:BB2").Resize(.Range("BC1")).Copy
wbShare.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp) _
.Offset(1, 0).PasteSpecial xlPasteValues
End With
With formBook.Sheets("TemBilling")
.Range("A11:F11").Copy
wbShare.Sheets("Report").Range("A" & Rows.Count).End(xlUp) _
.Offset(1, 0).PasteSpecial xlPasteValues
End With
Application.ScreenUpdating = False
If Sheets("Form").Range("J4") = "à§Ô¹Ê´" Then
Set rk = wkShare.Sheets("Cash").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
With formBook.Sheets("TemBilling")
.Range("A20:N20").Copy
wkShare.Sheets("Cash").Range("A" & Rows.Count).End(xlUp) _
.Offset(1, 0).PasteSpecial xlPasteValues
End With
Else
Set rk = wkShare.Sheets("Check").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
With formBook.Sheets("TemBilling")
.Range("A20:N20").Copy
wkShare.Sheets("Check").Range("A" & Rows.Count).End(xlUp) _
.Offset(1, 0).PasteSpecial xlPasteValues
End With
End If
formBook.Save
wdShare.Save
wkShare.Save
wdShare.Save
'formBook.Sheets("Form").Range("H1,L4:N4,N11:N12").ClearContents
With formBook.Sheets("Form")
.Range("K4") = .Range("K4") + 1
.Range("M2") = .Range("M2") + 1
.Range("N2") = .Range("N2") + 1
End With
Application.ScreenUpdating = True
End Sub
You do not have the required permissions to view the files attached to this post.
snasui.com ยินดีต้อนรับ 
สมาชิกท่านใดที่ยังไม่ได้ระบุ Version ของ Excel ที่ใช้งานจริง สามารถทำตาม Link นี้เพื่อจะได้รับคำตอบที่ตรงกับ Version ของท่านครับ 
โปรดทราบ 
