尝试在 Range 对象中使用字符串对象时，对象_global的方法范围失败

Question

尝试使用范围对象和字符串的组合时，我收到"对象_global失败的方法范围"消息。 我正在尝试遍历多个工作表，从工作表中复制并转置每一行，将转置的行堆叠到一列中，然后移动到下一张纸以抓取其行并将它们粘贴到下一列。

Dim CopyRng, pasteRng, Outnum, compsht As String
Dim myRng, PstRng As Range
For j = 5 To 10     
For i = 1 To tot_centers
Outnum = "out" & j
CopyRng = "ThisWorkbook.Sheets(""" & Outnum & """).Cells(" & i & ", 2), 
ThisWorkbook.Sheets(""" & Outnum & """).Cells(" & i & ", " & tot_days + 2 & ")"
Set myRng = Range(CopyRng)
Sheets(Outnum).Range(myRng).Copy  'THIS IS WHERE DEBUG HITS ERROR
pasteRng = "ThisWorkbook.Sheets(""" & "Compiled Data" & """).Cells(" & ((tot_days * (i - 1)) + (i + 1)) & ", " & j - 2 & ")"
Set PstRng = Range(pasteRng)  'AND I'LL PROBABLY GET THE SAME ERROR HERE
PstRng.PasteSpecial Transpose:=True
Next i
Next j

Answer 1

像这样的东西(tot_days/tot_centers变量名称需要解析(

Dim CopyRng, pasteRng, Outnum, compsht As String
Dim myRng, PstRng As Range, j, i
Dim shtOut As Worksheet
For j = 5 To 10
Set shtOut = ThisWorkbook.Sheets("out" & j)
For i = 1 To tot_centers '<< tot_days ?
shtOut.Cells(i, 2).Resize(1, tot_days + 1).Copy '<< tot_centers?
ThisWorkbook.Sheets("Compiled Data").Cells(tot_days * (i - 1) + (i + 1), _
j - 2).PasteSpecial Transpose:=True
Next i
Next j

Answer 2

对于任何看到这篇文章并想知道如何打开工作表，一次抓取多行并将它们堆叠成单独的列的人，代码的结果如下：

Dim Outnum As String
For j = 5 To 10     'use worksheets with outputs 5 through 10
For i = 1 To tot_centers  'in this case, tot_centers was declared earlier in the code and is and integer I count from a sheet
Outnum = "out" & j
Sheets(Outnum).Range(Sheets(Outnum).Cells(i, 2), Sheets(Outnum).Cells(i, tot_days + 2)).Copy
Sheets("Compiled Data").Range(Sheets("Compiled Data").Cells((tot_days * (i - 1)) + (i + 1), j - 1), Sheets("Compiled Data").Cells((tot_days * (i - 1)) + (i + 1), j - 1)).PasteSpecial Transpose:=True
Next i
Next j
End Sub