Excel VBA -将单元格内容从一个表复制到另一个表

I am working on a project where a large amount of data is stored in an excel workbook, with a new sheet for every quarter. I need need to create a dashboard of the data which shows graphs of data from the last 4 quarters. I have set a dropdown to select the quarter and am attempting to pull the relevant data through from the source sheets to the dashboard sheet but I just can't get it to work.

我正在做一个项目，在这个项目中，大量的数据存储在excel工作簿中，每个季度都有一个新的工作表。我需要创建一个数据仪表板，显示过去4个季度的数据图表。我已经设置了一个下拉菜单来选择季度，并试图从源表中提取相关数据到仪表板表中，但我就是不能让它工作。

    Private Sub SelectionChange(ByVal Target As Range)

Dim Q4 As Integer, Q1 As Integer, Q2 As Integer, Q3 As Integer, selectedQ As Variant


    'read selected quarter
    selectedQ = ActiveSheet.Range("B3").Value


        'compare selected quarter to identify sheet index
        'Q4 is current quarter

        If selectedQ = "15-16 Q4" Then Q4 = 10 Else
            If selectedQ = "16-17 Q1" Then Q4 = 11 Else
            If selectedQ = "16-17 Q2" Then Q4 = 12 Else
            If selectedQ = "16-17 Q3" Then Q4 = 13 Else
            If selectedQ = "16-17 Q4" Then Q4 = 14 Else
            If selectedQ = "17-18 Q1" Then Q4 = 15 Else
            If selectedQ = "17-18 Q2" Then Q4 = 16 Else
            If selectedQ = "17-18 Q3" Then Q4 = 17 Else
            If selectedQ = "17-18 Q4" Then Q4 = 18 Else
            If selectedQ = "18-19 Q1" Then Q4 = 19 Else
        End If

        'set sheet index for previous quarters
        If Q4 > 3 Then
            Q3 = Q4 - 1
            Q2 = Q4 - 2
            Q1 = Q4 - 3
        End If


        'fill current quarter using Sheets(1).Range(1, 1) as source

            'under 3 reg
            ActiveSheet.Range("C10").Value = Sheets(Q4).Range("Z21").Value
            ActiveSheet.Range("C11").Value = Sheets(Q4).Range("Z29").Value
            ActiveSheet.Range("C12").Value = Sheets(Q4).Range("Z39").Value
            ActiveSheet.Range("C13").Value = Sheets(Q4).Range("Z50").Value
            ActiveSheet.Range("C14:C19").Value = Sheets(Q4).Range("Z60:Z65").Value



End Sub

I initially started this project on a mac but have also tried to debug this on windows office 2007. When I watch the variable selectedQ on the PC it shows the message "can't compile module". What am I missing?

我最初在mac上启动这个项目，但也尝试在windows office 2007上调试这个项目。当我在PC上看到变量selectedQ时，它会显示消息“无法编译模块”。我缺少什么?

Thanks in advance

谢谢提前

Iain

伊恩•

4 个解决方案

#1

It seems you could simply go like follows:

看起来你可以简单地这样做:

Q4 = 6 + (Left(selectedQ, 2) - 15)*4 + Right(selectedQ, 1)

Should you want to keep it like you're doing then you'd better use a Select Case construct like follows:

如果你想让它像你正在做的那样，你最好使用一个选择案例结构，如下所示:

Select Case 
    Case "15-16 Q4"
          Q4 = 10
    Case "16-17 Q1"
          Q4 = 11
    Case "16-17 Q2"
          Q4 = 12
    Case "16-17 Q3"
          Q4 = 13
    Case "16-17 Q4"
          Q4 = 14
    Case "17-18 Q1"
          Q4 = 15
    Case "17-18 Q2"
          Q4 = 16
    Case "17-18 Q3"
          Q4 = 17
    Case "17-18 Q4"
          Q4 = 18
    Case "18-19 Q1"
          Q4 = 19
End Select

#2

Your If statement should be

你的If语句应该是

If selectedQ = "15-16 Q4" Then 
    Q4 = 10
ElseIf selectedQ = "16-17 Q1" Then 
    Q4 = 11 
ElseIf selectedQ = "16-17 Q2" Then 
    Q4 = 12
ElseIf selectedQ = "16-17 Q3" Then
' Etc
End If

#3

I noticed that you chosen to use the index of the sheets, Sheets(Q4) where Q4 is integer, this may cause errors if someone added, moved, or removed sheets from the workbook.

我注意到您选择使用Q4为整数的表、表(Q4)的索引，如果有人从工作簿中添加、移动或删除表，这可能会导致错误。

I highly recommend you to use the names of the worksheets instead. the formula will be Sheets("Sheet's name").value You can construct a simple function that loops over names to get the previous sheets' names for Q3, Q3, and Q1 if you want. In all ways, I advice you to limit the errors chance of using sheet indices.

我强烈建议您改用工作表的名称。公式将是表(“表的名称”)。值您可以构造一个简单的函数，该函数在名称上循环，以获取以前的表的Q3、Q3和Q1的名称(如果您愿意的话)。总之，我建议您限制使用表索引的错误机会。

Another notice, that I advice you to use the formal If, Then, Else if, , , Else, End if. Not the nested If you used, otherwise use the select Case statement that suggested by @user3598756.

另一个注意，我建议你使用正式的If，然后，Else If, Else, End If。如果使用的不是嵌套语句，则使用@user3598756建议的select Case语句。

Best Regards.

致以最亲切的问候。

#4

Thanks for the suggestions above, especially the select case.

感谢以上的建议，尤其是选择案例。

In the end I believe the problem was caused by a corruption that occurred due to using a file from an older version of office, then working on it in a newer office version and on office for Mac. I was really starting to doubt myself, but copying and pasting everything into a blank workbook solved the problem.

最后我相信这个问题是由于腐败发生的由于使用一个文件从一个旧版本的办公室,然后在新的办公室工作版本和Mac的办公室。我真的开始怀疑自己,但一切复制并粘贴到一个空白工作簿解决了这个问题。

#1