如何根据单元格内容选择多个工作表进行移动？ [英] How do I select multiple worksheets to move based on cell content?

问题描述

我需要一个代码，根据每个工作单元格的内容选择多个工作表并移动到一本新书中1,8）。例如，将单元格（1,8）中包含fin Ops的所有工作表移动到book1，然后将单元格（1,8）中包含Re的所有工作表移动到book2。

我试图从下面的代码开始，但作为初学者有点困难：

 对于每个ws在ActiveWorkbook.Worksheets 
 
如果ws.Cells（1，8）.Value =Fin Ops然后$ b，则
 
 
 $ b ActiveSheet.Select 
结束如果
 
下一个ws 
  

任何帮助非常感谢!!

解决方案

你其实是在正确的轨道！您有正确的想法，使用for-each循环来完成所有的工作表。

在此之前您将要做的是创建一些Workbook对象将是您希望将这些工作表移动到的工作簿。你可以这样做：

  Dim oBookOne As Workbook 
 Set oBookOne = Application.Workbooks（< workbook姓名在这里>）
  

重复所需的所有书籍。一旦您创建了所有的工作簿对象，您可以在For-each循环中执行此操作：

 对于ActiveWorkbook中的每个ws。工作表
 
如果ws.Cells（1，8）.Value =Fin Ops然后
 ws.Move之后：= oBookOne.Sheets（oBookOne.Sheets.Count）
结束如果
 
下一个ws 
  

对于您需要过滤的每个条件并且它应该工作！

如果您需要进一步的帮助，Google是您最好的朋友。 这里是关于工作簿的文档对象工作（嘿）和这里是移动功能的文档。

I have google searched to no avail.

I need a code that will select multiple worksheets and move to a new book based on the contents of each worksheets cell(1,8). For example, all worksheets that contain "fin Ops" in cell (1,8) are moved to book1 and then all worksheets that contain "Re" in cell (1,8) are moved to book2.

I tried to start with the code below, but being a beginner is a bit tough:

Dim ws As Worksheet


For Each ws In ActiveWorkbook.Worksheets

 If ws.Cells(1, 8).Value = "Fin Ops" Then
 ActiveSheet.Select
End If

Next ws

Any help is greatly appreciated!!

解决方案

You're actually on the right track! You have the correct idea using a for-each loop to go through all of the worksheets.

What you will want to do prior to this is create a few Workbook objects that will be the workbooks you wish to move these sheets to. You can do this like so:

Dim oBookOne As Workbook
Set oBookOne = Application.Workbooks("<workbook name here>")

Repeating that for all of the books required. Once you create all of your workbook objects, within your For-each loop you can do this:

For Each ws In ActiveWorkbook.Worksheets

  If ws.Cells(1, 8).Value = "Fin Ops" Then
     ws.Move After:= oBookOne.Sheets(oBookOne.Sheets.Count)
  End If

Next ws

Repeat that for every criteria you need to filter and it should work!

If you need further help, Google is your best friend. Here is documentation on how workbook objects work (heh) and here is the documentation on the Move function.

这篇关于如何根据单元格内容选择多个工作表进行移动？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！