将所有工作表复制到一张工作表中 [英] Copy all worksheets into one sheet

问题描述

我正在尝试编写一个excel宏，它将所有工作表复制到一个工作表中。

I am trying to write an excel macro that will copy all my worksheets into one single worksheet.

所有工作表的布局相同，4列中包含数据每行的每个单元格。每张纸都有一个标题。我正在尝试将每张工作表中的预过滤数据复制到结果表中，每张工作表中的数据都将堆叠在一起。

All worksheets are layed out the same, 4 columns with data in every cell of every row. Each sheet has a header. I am trying to copy the prefiltered data from each sheet to a results sheet, the data from each sheet will be stacked on top of each other.

到目前为止，这是什么我有并且它几乎可以正常工作。

So far this is what I have and it's almost working.

Dim sh As Worksheet
Dim iRows As Long

iRows = 0

For Each sh In ActiveWorkbook.Worksheets

sh.Select
Range("A1").Select
Selection.Offset(1, 0).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Worksheets("Results").Select
Range("A1").Select
Selection.Offset(iRows, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues

iRows = Worksheets("Results").UsedRange.Rows.Count
Next sh

我的偏移量不正确，当我复制下一张纸时，我复制了它复制到上一行的数据。

My offset is incorrect, when I copy over the next sheet I copy over the data it copies over the previous row.

如果有人可以提供帮助如果你愿意的话那会很棒也可以解释我在这里做错了什么，这对我是excel和VBA的新手来说非常有用。我猜我不明白粘贴的正确方式？

If anyone can help out that would be great, if you could also explain what I am doing wrong here as well that would be great as I'm new to excel and VBA. I'm guess that I don't understand how the paste works correctly?

推荐答案

Sub tgr()

    Dim ws As Worksheet
    Dim wsDest As Worksheet

    Set wsDest = Sheets("Results")

    For Each ws In ActiveWorkbook.Sheets
        If ws.Name <> wsDest.Name Then
            ws.Range("A2", ws.Range("A2").End(xlToRight).End(xlDown)).Copy
            wsDest.Cells(Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteValues
        End If
    Next ws

End Sub

这篇关于将所有工作表复制到一张工作表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！