如果这是重复的,请道歉,但我不知道如何正确措辞。
我有一个工作簿,里面有 7 个工作表,所有工作表的名称都不同。
我想编写一个 if 函数来搜索所有工作表,如果它符合粘贴到"名称"工作表中的条件。我已经尝试了多种方法,但无法实现这一目标。这就是我将获得标准的地方。
Dim Client As String
Client = InputBox("Enter the Clients name")
如果我从一张纸中提取数据,则此代码有效:
Dim pasteRowIndex As Long
pasteRowIndex = 2
Dim LR as Long
LR = Cells(Rows.Count, 1).End(xlUp).Row
For I = 2 To LR
If Cells(I, 1).Value = Client Then
'Now the code to grab the data and past into a new workbook
Rows(I).Select 'This just works on the active sheet, but I want this to iterate through
all the sheets and paste the range into sheet2 in this code
Selection.Copy
'Switch to the sheet where you want to paste it & paste
Sheets("Sheet2").Select 'However I will change this name
Rows(pasteRowIndex).Select
ActiveSheet.Paste
'Next time you find a match, it will be pasted in a new row
pasteRowIndex = pasteRowIndex + 1
'Switch back to your table & continue to search for your criteria
'Sheets("Sheet1").Select
End If
Next I
虽然上面的代码适用于单个工作表,但我希望宏遍历所有工作表,并且它们没有命名为 Sheet1-Sheet7。
此外,必须有一种更清洁的方法。
抱歉,如果不清楚,如果需要更清楚,请告诉我。
您可以按照以下示例代码遍历工作簿中的所有工作表:
Dim Sht As Worksheet
For Each Sht In Worksheets
If Sht.Name <> "Sheet1" Or Sht.Name <> "Sheet7" Then
'Do your stuff here
'sample code to refer the row at specific sheet without activating it
Sht.Rows(I).Copy
Sheets("Sheet2").Cells(pasteRowIndex, 1).PasteSpecial xlPasteAll
End If
Next
将建议避免使用.选择和。激活方法。而是使用工作表变量来引用特定工作表。