名單樣式(工作表1)
表格樣式(工作表2)
欲套印結果跨儲存格說明
欲套印結果(工作表3)
檔案樣式
https://www.mediafire.com/file/o6igk8t2z65znot/TEST.xlsx/file
請求各位專家幫忙,謝謝
Private Sub CommandButton1_Click()
Dim K As Integer
Dim M As Integer
Dim BlockX As Integer
Dim BlockY As Integer
Dim TempY As Integer
Dim APosX As Integer
Dim APosY As Integer
Dim BPosX As Integer
Dim BPosY As Integer
Dim CPosX As Integer
Dim CPosY As Integer
Dim DPosX As Integer
Dim DPosY As Integer
Dim TotalRow As Integer
Dim M_Max As Integer
TotalRow = Worksheets("工作表1").Range("A65536").End(xlUp).Row
' 計算由【工作表1】的資料每次抓 9 筆的次數
M_Max = Application.Ceiling(TotalRow / 9, 1)
For M = 1 To M_Max
' 計算【工作表2】九宮格相對位置
For K = 1 To 9
BlockY = Application.Ceiling(K / 3, 1)
TempY = K Mod 3
If TempY = 0 Then
BlockX = 3
Else
BlockX = TempY
End If
' 計算【工作表2】的填寫位置
If BlockY = 1 Then
APosY = 5
ElseIf BlockY = 2 Then
APosY = 12
Else
APosY = 19
End If
BPosY = APosY + 1
CPosY = APosY + 1
DPosY = APosY + 2
If BlockX = 1 Then
APosX = 3
ElseIf BlockX = 2 Then
APosX = 10
Else
APosX = 17
End If
BPosX = APosX - 1
CPosX = APosX + 3
DPosX = APosX
'將【工作表1】資料寫入【工作表2】
' 欄位 A
Sheets("工作表2").Cells(APosY, APosX) = Sheets("工作表1").Cells(9 * (M - 1) + K, 1)
' 欄位 B
Sheets("工作表2").Cells(BPosY, BPosX) = Sheets("工作表1").Cells(9 * (M - 1) + K, 2)
' 欄位 C
Sheets("工作表2").Cells(CPosY, CPosX) = Sheets("工作表1").Cells(9 * (M - 1) + K, 3)
' 欄位 D
Sheets("工作表2").Cells(DPosY, DPosX) = Sheets("工作表1").Cells(9 * (M - 1) + K, 4)
Next K
'將【工作表2】複製到【工作表3】
Sheets("工作表2").Select
Sheets("工作表2").Range("A1:U53").Select
Selection.Copy
Sheets("工作表3").Select
Sheets("工作表3").Range("A" & (M - 1) * 53 + 1).Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Next M
End Sub
"名單樣式(工作表1)"
你為何不用 word 的套印功能更好處理你的需求,word 裡設定好表格格式後不會異動,但 excel 會隨著你資料寬度可能變動
https://www.google.com/search?q=word+%E5%A6%82%E4%BD%95%E5%A5%97%E5%8D%B0&oq=word+%E7%9A%84%E5%A5%97%E5%8D%B0%E5%8A%9F%E8%83%BD&aqs=chrome.1.69i57j0i333l2.3624j0j7&sourceid=chrome&ie=UTF-8
這不可用"="解決嗎?