从变量表复制数据 [英] Copy data from a variable table

问题描述

我一直试图做一个报告，并创建一个宏来将数据从一个文件复制到另一个文件.

I have been trying to do a report and creating a macro to copy the data from one file to another.

我无法弄清楚如何复制数据，因为我需要从中获取数据的表各不相同.

I cannot figure out how to copy the data since the table I need to get my data from varies.

示例一:

我需要复制的是警报"文本下方的内容.

What I need to copy is what is below the Alarm text.

但是在示例一中，我没有严重警报，但是可能有文件.同样适用于主要/次要/警告.

But in, example one, I have no critical alarms but there are files that may have. Same applies to major/minor/warning.

警报"文本下方的最大行数为3，但我可以为1/2/3，甚至没有.

The max of lines below the Alarm text are 3, but I can have 1/2/3 or even none.

在示例2中，我没有数据.

In example 2, I have no data.

在这里，我在所有其他类别中都具有2个关键点和3个.

Here I have 2 critical and 3 on all other categories.

我知道这可能是一个奇怪的问题，但是我不知道如何找到这些值，因为它们可能相差很大.

I know this may be a weird question, but I have no idea in how to find the these values, since they may vary so much.

感谢所有帮助

这是我的代码，但我缺少重要的部分，

Here is the code i have, but i am missing the important part,

          Sub Copy()



Dim wbOpen As Workbook
Dim wbMe As Workbook
Dim vals As Variant

Set wbMe = ThisWorkbook
Set wbOpen = Workbooks.Open("C:\XXX\Core")

'MSS

     vals = wbOpen.Sheets("MSS02NZF").Range("A2:B260").Copy
wbMe.Sheets("MSS02NZF").Range("B5").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _
                 Operation:=xlNone, SkipBlanks:=False, Transpose:=False


       '  wbOpen.Sheets(1).Range("A2:B260").Copy
   ' wbMe.Sheets(1).Range("B5").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _
                 Operation:=xlNone, SkipBlanks:=False, Transpose:=False

     'MME
  vals = wbOpen.Sheets("MME01NZF").Range("A2:H260").Copy
wbMe.Sheets("MME01NZF").Range("B5").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _
                 Operation:=xlNone, SkipBlanks:=False, Transpose:=False


                     'CSCF
  vals = wbOpen.Sheets("CSCF").Range("A2:H2060").Copy
wbMe.Sheets("CSCF").Range("B5").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _
                 Operation:=xlNone, SkipBlanks:=False, Transpose:=False

推荐答案

根据自己的需要进行修改；代码使用A:B作为源，并将结果粘贴到C:D.

Modify to your own need; code is using A:B as your source, and pastes the results in C:D.

Sub test()
    Dim lrow As Long, alarmRow() As Long, alarmEnd() As Long
    Dim count As Long, count2 As Long, rowcount As Long

    ReDim alarmRow(1 To Application.CountIf(Range("A:A"), "Alarm"))
    ReDim alarmEnd(1 To UBound(alarmRow))

    With Worksheets("Sheet4") 'Change this to the Sheetname of your source.
        lrow = .Cells(Rows.count, 1).End(xlUp).Row
        For x = 1 To lrow Step 1
            If .Range("A" & x).Value = "Alarm" Then 'Change "A" column to where your source data is.
                count = count + 1
                alarmRow(count) = x + 1
            ElseIf .Range("A" & x).Value = "" Then 'Change "A" column to where your source data is.

                count2 = count2 + 1
                alarmEnd(count2) = x
            End If
            alarmEnd(UBound(alarmEnd)) = lrow
        Next

        For x = 1 To UBound(alarmRow) Step 1
            lrow = .Cells(Rows.count, 3).End(xlUp).Row + 1
            rowcount = alarmEnd(x) - alarmRow(x)
            .Range("C" & lrow & ":D" & lrow + rowcount).Value = .Range("A" & alarmRow(x) & ":B" & alarmEnd(x)).Value ' Change A/B to where your source data is, and C/D to where you want to put the list.
        Next
    End With
End Sub

有点混乱，但是它是这样工作的: 它将查看单词"Alarm"所在的列表.一旦找到它，单词所在的行号就会被注册到一个数组中.空白行也被带到另一个数组.这将用作复制数据时的范围.

It's a bit of a mess, but here's how it works: It'll look at the list where the word "Alarm" is. Once it finds it, the row number the word is in is registered to an Array. The row of the blank space is also taken to another array. This will serve as the range when copying the data.

这篇关于从变量表复制数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！