将动态单元格/行复制到新工作表或工作簿中 [英] Copying Dynamic Cells/Rows Into New Sheet or Workbook

问题描述

我尝试在网站上搜索解决方案，但找不到完全匹配的内容。我正在尝试找出将执行以下操作的VBA代码：

I tried searching on the site for a solution to this but I was unable to find an exact match. I'm trying to figure out a VBA code that will do the following:

1. 搜索指定列


2. 找到所有匹配值（未指定）


3. 复制所述匹配值的行


4. 将它们放入新的工作表或工作簿中（最好是工作簿）

1. Search through a specified column
2. Locate all matching values (non-specified)
3. Copy the rows of said matching values
4. Place them into a new worksheet or workbook (preferrably workbook)

这是我的问题。指定行中的值可以是300多个唯一值中的1个，因此在VBA代码中指定每个值都是一场噩梦。

Here is my problem. The value in the specified row can be 1 of over 300 unique values, so specifying each one in the VBA code would be a nightmare.

我知道按字母顺序过滤指定的列会起作用，但是我如何告诉宏在每个组的末尾停下来并将其复制到新的工作表中？基本上我会怎么说：如果下一个值与当前值不匹配，则开始新书？ （即：向下搜索C列并复制所有包含蝙蝠的行，然后复制到新书，然后向下搜索C列并复制所有包含汽车的行，然后将警察复制到新书）

I understand filtering that specified column alphabetically will work, but how would I tell the macro to stop at the end of each group and copy it to a new sheet? Basically how would i have it say "If the next value doesn't match the current value, start a new book"? (IE: Search down column C and copy all rows that contain "Bat", then copy to new book, then search down column C and copy all rows containing "Car", then cop to new book)

任何帮助将不胜感激！

推荐答案

假设您的数据已分组，即没有数据像这样：

assuming your data is grouped, ie, not something like:

batman
batman
robin
robin
batman

但相反：

batman
batman
batman
robin
robin

然后，这将捕获所有连续的块并将其推入自己的工作簿中。它还假定所有内容都在A列中，因此，请根据需要进行更改。

Then this will grab all contiguous chunks and shove them in their own workbook. It also assumes everything is in column A, so change that if you need to.

Sub grabber()
Dim thisWorkbook As Workbook
Set thisWorkbook = ActiveWorkbook
last = 1
For i = 1 To 18 'my sample had 18 rows, replace it with how many you have
If Range("A" & i) <> Range("A" & (i + 1)) Then
Range("A" & last & ":A" & i).Copy
Set NewBook = Workbooks.Add
NewBook.Sheets("Sheet1").Range("A1").PasteSpecial xlPasteValues
last = i + 1
thisWorkbook.Activate
End If
Next i
End Sub

这篇关于将动态单元格/行复制到新工作表或工作簿中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！