将多个工作表复制到一个 [英] Copy multiple worksheets into one

问题描述

我花了一段时间寻找帮助编写代码，将匹配的数据从多个工作表复制到单个工作表中，但唯一一个与我发现的类似的工作位于这里：找到匹配，从Sheet1复制行并插入到Sheet2

I have spent a while searching for help on writing code to copy matching data from multiple worksheets into a single sheet, but the only one that did something similar that I found was located here: Find Match, Copy Row from Sheet1 and Insert Into Sheet2

我尝试适应我的需要，但我不是很好的VBA，我无法做到我想做的。任何帮助将不胜感激。

I tried adapting it to fit my needs, but I am not very good at VBA and I could not get it to do what I want. Any help would be greatly appreciated.

我有一个工作簿有四个工作表; 主人， FescoMonthly ，计划和 FescoData 。这些工作表由我们的客户发送给我们，所以我可以导入它们，但是我不想改变它们的布局，这是问题的一部分。我希望宏在每个工作表中搜索匹配的电表号码，并将某些行复制到新的工作表中，名为组合。

What I have is a workbook that has four worksheets; Master, FescoMonthly, Schedule, and FescoData. These worksheets are sent to us by our customers so I can import them, but I do not want to change their layouts, which is part of the problem. I want the macro to search for matching meter numbers in every worksheet and copy certain row to a new worksheet called Combined.

在 Master 工作表中，我需要将整个事物从第5行复制到底部（目前为1982年，但可以添加更多）和列A到F。 - 列A是米数

In the Master worksheet I need to copy the whole thing from row 5 to bottom (currently 1982 but more could be added) and columns A through F. -----Column A is meter number

在 FescoMonthly 工作表中，我需要将第5行复制到底部和列A H通过将相同的仪表编号从列D（仪表编号）到主工作表的列A相匹配。

In the FescoMonthly worksheet I need to copy row 5 to bottom and column A through H by matching identical meter numbers from column D (meter number) to column A of the main worksheet.

在 Schedule 工作表中，我需要通过匹配列A将行5复制到底部和列A到D仪表编号）到主工作表的A列。

In the Schedule worksheet I need to copy row 5 to bottom and column A through D by matching column A (meter number) to column A of the main worksheet.

在 FescoData 工作表中，我需要将第5行复制到底部和列A到C，通过将A列（仪表编号）与主工作表的A列匹配。

In the FescoData worksheet I need to copy row 5 to bottom and column A through C by matching column A (meter number) to column A of the main worksheet.

如果有人可以帮助我，只需匹配和复制 Master 和 FescoMonthly 我想我可以弄清楚如何做别人，我可以转发我的工作，看看我是否正确。

If someone could please help me with just matching and copying Master and FescoMonthly I think I could figure out how to do the others and I could repost my work to see if I am doing it right.

推荐答案

将sht设置为具有要复制的数据的工作表

将trg设置为目标工作表

将colCount设置为要复制的列数

set sht to the worksheet that has the data you want to copy
set trg to the target worksheet
set colCount to the number of columns to copy

Set rng = sht.Range(sht.Cells(5, 1), sht.Cells(65536, 1).End(xlUp).Resize(, colCount)) 
' start at row 5, then get the last row with data, and then expand to the  number of columns
trg.Cells(65536, 1).End(xlUp).Offset(1).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value 
' find last unused cell in first column, and copy the data there

（在excel 2007或更高版本中使用1048576而不是65536）

(use 1048576 instead of 65536 in excel 2007 or later)

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