- 帖子
- 95
- 主題
- 29
- 精華
- 0
- 積分
- 150
- 點名
- 0
- 作業系統
- windows2003
- 軟體版本
- office 2003
- 閱讀權限
- 20
- 性別
- 男
- 來自
- Kaoshiung
- 註冊時間
- 2010-11-5
- 最後登錄
- 2018-2-8
|
[/code][b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=50094&ptid=8871]6#[/url] [i]cmo140497[/i] [/b]
不好意思,小弟試出來了,用worksheets(index)就OK了,不過仍感謝您的指點迷津,感恩!
[code]Sub Test()
Dim vCols As Long, cIndex As Long, rIndex As Long
Dim first As Long, last As Long
Dim sCopy As String
Dim r, c
Dim myshtc As Long
Worksheets(1).Rows("48:99").Clear
myshtc = ThisWorkbook.Sheets.Count
first = 3
last = myshtc
sCopy = "B6:P16"
r = Range(sCopy).Rows.Count + 1
c = Range(sCopy).Columns.Count + 1
'可見的欄數總數
vCols = Sheets("DashBoard").Cells.SpecialCells(xlCellTypeVisible).Columns.Count
For i = first To last
rIndex = Int((i - first) / Int(vCols / c))
cIndex = (i - first) Mod Int(vCols / c)
newR = r * rIndex
newC = c * cIndex
With Sheets("DashBoard").[R50].Offset(newR, newC)
.Offset(-1, 1).Value= "ID"
.Offset(-1, 2).Value = Worksheets(i).Name
Worksheets(i).Range(sCopy).Copy .Cells(1, 1)
End With
Next i
End Sub |
|
發表於 2013-1-23 17:28
| 
