我在一个名为"Master“的Excel电子表格中有大约1,000行数据,我正在尝试根据一个称为区域ID的标识符将数据拆分为42个不同的电子表格。我已经在同一工作簿中的另一个电子表格中创建了一个表,其中包含42个唯一的不同区域,以及我想要命名的新工作表。到目前为止,我有以下代码,它创建了42个新的电子表格并对它们进行了相应的命名。
Sub Create_Tabs()
Dim MyCell As Range, MyRange As Range
Set MyRange = Sheets("TabList").Range("D2")
Set MyRange = Range(MyRange, MyRange.End(xlDown))
For Each MyCell In MyRange
Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
'I would like to create code here to paste the "Master" spreadsheet contents in the newly created worksheet and keep only rows corresponding Region IDs (delete the rest).
Next MyCell
End Sub如何将"Master“电子表格内容粘贴到新创建的工作表中,并只保留对应区域ID的行(删除其余部分)?
发布于 2017-03-15 23:47:04
根据页签名称筛选您的主数据,并将其复制到新工作表中。
像这样的东西会起作用的。您可能需要根据数据更改筛选器字段。
Sub Create_Tabs_And_Copy_Data()
Dim MyCell As Range, MyRange As Range
Set MyRange = Sheets("TabList").Range("D2")
Set MyRange = Range(MyRange, MyRange.End(xlDown))
For Each MyCell In MyRange
Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
'I would like to create code here to paste the "Master" spreadsheet contents in the newly created worksheet and keep only rows corresponding Region IDs (delete the rest).
With Sheets("Master").UsedRange
.AutoFilter
.AutoFilter Field:=1, Criteria1:=MyCell.Value
.SpecialCells(xlCellTypeVisible).Copy Sheets(MyCell.Value).Cells(1, 1)
Application.CutCopyMode = False
End With
Next MyCell
End Subhttps://stackoverflow.com/questions/42814321
复制相似问题