大量编辑:
与上次与 Iain 的聊天一样,已经设置了正确的参数。我删除了最后几个代码片段,因为它们不太正确。如果有人仍然感兴趣,请检查编辑历史记录。
希望这是最终的编辑。 ;)
因此,所需的正确条件是:
- 工作表中的月份名称。我们为此使用了一个输入框。
- 我们检查行数。共有三个条件:总共 157 行、总共 41 行以及其他所有条件。
下面的子例程就可以解决这个问题。
Sub BlackwoodTransfer()
Dim Summ As Worksheet, Ws As Worksheet
Dim ShName As String
Dim nRow As Long
Set Summ = ThisWorkbook.Sheets("Summary")
ShName = InputBox("Enter month for Call Flow in mmmm format (ie. November, etc.):") & " Call Flow"
'Returns November Call Flow. This means it will target every sheet that has November Call Flow in its name.
Application.ScreenUpdating = False
For Each Ws In ThisWorkbook.Worksheets
If InStr(1, Ws.Name, ShName) > 0 Then
'Starting from first character of the sheet's name, if it has November, then...
nRow = Summ.Cells(Rows.Count, 1).End(xlUp).Row + 1
'... get the next empty row of the Summary sheet...
Select Case Ws.Cells(Rows.Count, 1).End(xlUp).Row
'... check how many rows this qualified sheet has...
Case 157
'... if there are 157 rows total...
Ws.Range(Cells(57,1),Cells(104,13)).Copy
'... copy Rows 57 to 104, 13 columns wide...
Summ.Range("A" & nRow).PasteSpecial xlPasteAll
'... and paste to next empty row in Summary sheet.
Case 41
Ws.Range(Cells(23,1),Cells(126,13)).Copy
Summ.Range("A" & nRow).PasteSpecial xlPasteAll
Case Else
Ws.Range(Cells(23,1),Cells(30,13)).Copy
Summ.Range("A" & nRow).PasteSpecial xlPasteAll
End Select
End If
Next Ws
Application.ScreenUpdating = True
End Sub
@Iain:查看评论并与 MSDN 数据库交叉引用它们。这应该解释每个函数/方法到底在做什么。希望这可以帮助!