将多个工作表中的数据合并到Excel中的摘要工作表中 [英] Consolidating Data from Multiple Worksheets into a Summary Worksheet in Excel
问题描述
推荐答案
我是vba的新手，我正在尝试在同一工作簿中的40个不同工作表上创建单行数据的摘要页面。我剪切并粘贴了Ron de Bruin的代码（见下文），我不断收到编译错误，即"LastRow"一词。以下行突出显示
： Last = LastRow（DestSh）
I am very new to vba and I am attempting to create a summary page on a single row of data, across 40 different worksheets in the same workbook. I cut and pasted Ron de Bruin's code (see below) and I keep getting a compilation error, the term "LastRow" in the following line is highlighted: Last = LastRow(DestSh)
整个代码如下...
期权明确
'从第2行复制到最后一行，数据为
'
'注意：此示例使用函数LastRow
'
'在示例1中，您可以看到您可以复制所有单元格这一行的工作表：
$
'设置CopyRng = sh.UsedRange
'
'但是如果我们不想复制相同的标题怎么办？每次都行。
'以下示例将从第2行复制到最后一行，每张纸上有数据
'
'更改如果你想在另一行开始，请在宏中开始行。
'
''填写起始行
'StartRow = 2
'当您运行其中一个示例时，它将首先删除摘要工作表
'命名为RDBMergeSheet（如果存在）然后添加新工作簿。
'这个确定在运行代码后，数据始终是最新的。
'*****阅读网站上的提示****
$
Sub CopyDataWithoutHeaders（）
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim shLast As Long
Dim CopyRng As Range
Dim StartRow As Long
应用程序
.ScreenUpdating = False
.EnableEvents = False
结束用$
'删除工作表'RDBMergeSheet"如果存在，则为
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets（" RDBMergeSheet"）。删除
On Error GoTo 0
Application.DisplayAlerts = True
'添加名称为"RDBMergeSheet"的工作表< b $ b 设置DestSh = ActiveWorkbook.Worksheets.Add
DestSh.Name =" RDBMergeSheet"
'填写开始行
StartRow = 2
'循环浏览所有工作表并将数据复制到DestSh
For Each sh In ActiveWorkbook.Worksheets
'循环浏览除RDBMerge工作表以外的所有工作表和&b $ b '信息工作表，如果需要，您可以向阵列中添加更多工作表。
如果是IsError（Application.Match（sh.Name，_
Array（DestSh.Name，"信息"），
0））然后
'在DestSh上找到包含数据的最后一行，然后是&bbsp; Last = LastRow（DestSh）
shLast = LastRow（sh）
'如果sh不为空，如果最后一行> = StartRow复制CopyRng
如果shLast> 0和shLast> = StartRow然后是
&NBSP;&NBSP; '设置您要复制的范围
设置CopyRng = sh.Range（sh.Rows（StartRow），sh.Rows（shLast））
;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; '测试DestSh中是否有足够的行来复制所有数据
&NBSP;如果Last + CopyRng.Rows.Count> DestSh.Rows.Count然后是
&NBSP; MsgBox"Destsh中没有足够的行"< b $ b &NBSP;&NBSP;&NBSP;&NBSP; GoTo ExitTheSub
结束如果
'此示例复制值/格式，如果您只想复制<
;&NBSP; '值或想要复制此页面上示例1下面的所有内容
&NBSP; CopyRng.Copy
使用DestSh.Cells（最后+1，" A"）
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; .PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
结束与$
结束如果
结束如果是
下一页
退出TheSub：
Application.GoTo DestSh.Cells（1）
'在DestSh表单中自动调整列宽为
DestSh.Columns.AutoFit
应用程序
.ScreenUpdating = True
.EnableEvents = True
_____________________________________________
Option Explicit
'Copy from row 2 till the last row with data
'
'Note: This example use the function LastRow
'
'In example 1 you can see that you can copy all cells on a worksheet with this line:
'Set CopyRng = sh.UsedRange
'
'But what if we do not want to copy the same header row each time.
'The example below will copy from row 2 till the last row with data on each sheet
'
'Change the start row in the macro if you want to start in a different row.
'
''Fill in the start row
'StartRow = 2
'When you run one of the examples it will first delete the summary worksheet
'named RDBMergeSheet if it exists and then adds a new one to the workbook.
'This ensures that the data is always up to date after you run the code.
'*****READ THE TIPS on the website****
Sub CopyDataWithoutHeaders()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim shLast As Long
Dim CopyRng As Range
Dim StartRow As Long
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
'Delete the sheet "RDBMergeSheet" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets("RDBMergeSheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True
'Add a worksheet with the name "RDBMergeSheet"
Set DestSh = ActiveWorkbook.Worksheets.Add
DestSh.Name = "RDBMergeSheet"
'Fill in the start row
StartRow = 2
'loop through all worksheets and copy the data to the DestSh
For Each sh In ActiveWorkbook.Worksheets
'Loop through all worksheets except the RDBMerge worksheet and the
'Information worksheet, you can ad more sheets to the array if you want.
If IsError(Application.Match(sh.Name, _
Array(DestSh.Name, "Information"),
0)) Then
'Find the last row with data on the DestSh and sh
Last = LastRow(DestSh)
shLast = LastRow(sh)
'If sh is not empty and if the last row >= StartRow copy the CopyRng
If shLast > 0 And shLast >= StartRow Then
'Set the range that you want to copy
Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast))
'Test if there enough rows in the DestSh to copy all the data
If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
MsgBox "There are not enough rows in the Destsh"
GoTo ExitTheSub
End If
'This example copies values/formats, if you only want to copy the
'values or want to copy everything look below example 1 on this page
CopyRng.Copy
With DestSh.Cells(Last + 1, "A")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With
End If
End If
Next
ExitTheSub:
Application.GoTo DestSh.Cells(1)
'AutoFit the column width in the DestSh sheet
DestSh.Columns.AutoFit
With Application
.ScreenUpdating = True
.EnableEvents = True
_____________________________________________
我的学习曲线陡峭现在，我正处于一个紧迫的截止日期，可以编译和汇总这些数据。我正在快速学习，但现在我正在寻找一个快速的"剪切粘贴"。解决方案，所以我可以继续前进。任何帮助将不胜感激。
My learning curve is steep right now and I am on a tight deadline to compile and summarize this data. I am learning fast, but right now I'm looking for a quick "cut-paste" solution, so I can keep moving. Any help would be appreciated.
谢谢！
G
这篇关于将多个工作表中的数据合并到Excel中的摘要工作表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
