Excel Forum

Code: Select all

Sub CopyDataToDetailSheet()
    Dim ws As Worksheet
    Dim detailSheet As Worksheet
    Dim lastCol As Long
    Dim copyRange As Range

    Set detailSheet = ThisWorkbook.Sheets("detail")
    
    lastCol = 2

    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> "data" And ws.Name <> "OP" And ws.Name <> "detail" Then
            Set copyRange = ws.Range("A7:D30")

            copyRange.Copy Destination:=detailSheet.Cells(3, lastCol)

            lastCol = lastCol + copyRange.Columns.Count
        End If
    Next ws
    MsgBox "การคัดลอกข้อมูลเสร็จสมบูรณ์"
End Sub

Code: Select all

Sub CopyDataToDetailSheet()
    Dim ws As Worksheet
    Dim detailSheet As Worksheet
    Dim lastCol As Long
    Dim copyRange As Range
    On Error Resume Next
    Set detailSheet = ThisWorkbook.Sheets("detail")
    
    lastCol = 2

    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> "data" And ws.Name <> "OP" And ws.Name <> "detail" Then
            Set copyRange = ws.Range("A7:D30")
             lastCol = Application.Match(ws.Range("A5"), detailSheet.Range("b1:bu1"), 0)
            copyRange.Copy Destination:=detailSheet.Cells(3, lastCol + 1)

            lastCol = lastCol * 4
        End If
    Next ws
    MsgBox "การคัดลอกข้อมูลเสร็จสมบูรณ์"
End Sub