如何提取合并的数据并将其放入不同的工作表中? [英] How do i extract merged data and put them into different worksheets?

问题描述

我如何使用excel vba选择性地提取合并的数据(不从excel表中手动单击并复制，也没有专门列出要复制的数据范围.我想要的是这样一种方式程序将从F列到H列提取具有相同名称类型的整个行，例如分别使用不同名称(John，Charlie)的Martin_1，Martin_2和Martin _3并将其复制并粘贴到新工作表中.

How do i use excel vba to extract merged data selectively(not manually clicking and copying from the excel sheet and also not specifically listing the range of the data that i want to copy. What i want is in a way such that the programme extract the entire row that has the same type of name from column F to column H for example Martin_1,Martin_2 and Martin _3 respectively for different names(John, Charlie) and copy and paste them to a new worksheet.

将提取的数据放入3个不同的工作表中，即索引1，索引2和索引3，如下所示

这是我尝试输入的代码:

Here is my attempted code:

dim i as integer
dim lastmartinrow as integer
dim c as string
dim j as integer
dim lastjohnrow as integer
dim b as string
dim k as integer
dim lastcharlierow as integer
dim a as string

for i = 1 to lastmartinrow
Set c = .Find("Martin_1","Martin_2",Martin_3" LookIn:=xlValues)
If c Is Nothing Then
'find the last row has the same "Martin_1","Martin_2","Martin_3") 
'copy the entire rows from 1 to lastmartinrow and paste it to a new worksheet("Index1").name
for j = lastmartinrow + 1 to lastjohnrow
Set b = .Find("John_1","John_2","John_3" LookIn:=xlValues)
If b Is Nothing Then
'find the last row has the same "John_1","John_2","John_3") 
'copy the entire rows from lastmartinrow + 1 to lastjohnrow and paste it to a new worksheet("Index2").name
for k = lastjohnrow + 1 to lastcharlierow
Set a = .Find("Charlie_1","Charlie_2",Charlie_3" LookIn:=xlValues)
If a Is Nothing Then
'find the last row has the same Charlie_1","Charlie_2",Charlie_3") 
'copy the entire rows from lastjohnrow + 1 to lastcharlierow and paste it to a new worksheet("Index3").name

更新代码错误因为以前我没有在崩溃时保存电子表格，所以这是我现在正在使用的新工作表.[！[在此处输入图片描述] [6]] [6]

error for the updated code Because previously i didnt save the spreadsheet when it crashed so this is the new sheet1 i am using right now.. [![enter image description here][6]][6]

推荐答案

我创建了一个通用代码，它将所有F中存在的所有匹配值(John，Marin，Charlie等)复制到H列并将其粘贴到Index3中床单.它不会复制与其他任何行都不匹配的值(此后立即).

I have created a generic code, It will copy all the matching values(John,Marin,Charlie etc) present in F to H columns and paste it in Index3 sheet. It will not copy values with single row means which are not matching with any other row(immediately after that).

Sub UpdateVal()
    Static count As Long
    Dim iRow As Long
    Dim aRow As Long
    Dim a As Long
    Dim b As Long
    Dim selectRange As Range
    j = 2
    iRow = 1
    LastLine = ActiveSheet.UsedRange.Rows.count
    While iRow < LastLine + 1
        a = iRow + 1
        b = iRow + 17 ' Max Group Size with Same name in F to H column
        count = 1
        If Cells(iRow, "F").Value = "Martin1" Then
            sheetname = "Index1"
        ElseIf Cells(iRow, "F").Value = "John1" Then
            sheetname = "Index2"
        Else
            sheetname = "Index3"
        End If
        For aRow = a To b
            If Cells(iRow, "F") = Cells(aRow, "F") And Cells(iRow, "G") = Cells(aRow, "G") And Cells(iRow, "H") = Cells(aRow, "H") Then
                count = count + 1
            Else
                Set selectRange = Range("A" & iRow & ":J" & aRow - 1)
                selectRange.Copy
                indexrowcount = Sheets(sheetname).UsedRange.Rows.count
                Sheets(sheetname).Range("A" & indexrowcount).PasteSpecial xlPasteAll
                iRow = iRow + count
                Exit For
           End If
        Next aRow
    Wend
End Sub

这篇关于如何提取合并的数据并将其放入不同的工作表中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！