vba 从一个单元格复制并粘贴到另一个工作簿和单元格

Copy & paste from one cell to another workbook and cell

提问by Alex Günsberg

I'm pretty new to vba. I have managed to make some codes work by copying from this site and editing them into my purpose, but this one is just too hard to crack (have tried to solve this for a couple of days now).

我对 vba 很陌生。我设法通过从该站点复制并将它们编辑到我的目的来使一些代码工作，但是这个代码太难破解（已经尝试解决这个问题几天了）。

I want to copy data from one file and paste it to another file (master file). However, it can't overwrite the existing data, so it should select the first empty cell in a specific column. Please Help Me!! If possible could you add as much as possible comments so that I can understand and learn to do it for myself in the future.

我想从一个文件复制数据并将其粘贴到另一个文件（主文件）。但是，它不能覆盖现有数据，因此它应该选择特定列中的第一个空单元格。请帮我！！如果可能的话，您可以添加尽可能多的评论，以便我将来能够理解并学会自己做。

The data files are call logs and these numbers that are copied are results of an analysis that I execute in vba. So there will be a new data file every day, but the actual data that I copy is always located in the same cells. On the other hand the Master file is always the same, but the range where I paste the data changes.

数据文件是通话记录，复制的这些数字是我在 vba 中执行的分析的结果。所以每天都会有一个新的数据文件，但我复制的实际数据总是位于同一个单元格中。另一方面，主文件总是相同的，但我粘贴数据的范围发生了变化。

Example Data file: 25("S1" in excel data file) 44("S2" in exel data file) 8.6.2013("S13" in excel data file) These are all created with a vba code that involves formulas, so they will always be in the same cell. There are some other data cells as well, but I hope to learn from this so that I can include them myself.

示例数据文件： 25（excel数据文件中的“S1”） 44（exel数据文件中的“S2”） 8.6.2013（excel数据文件中的“S13”）这些都是用涉及公式的vba代码创建的，所以它们将始终在同一个单元格中。还有一些其他数据单元格，但我希望从中学习，以便我可以自己包含它们。

Master file on the other hand is the same always. The only thing that varies in the Master file is the cell where I paste the data. The cell is practically always the row bellow the previous insert.

另一方面，主文件总是相同的。主文件中唯一不同的是我粘贴数据的单元格。单元格实际上总是在前一个插入下方的行。

Master file: Cell S1 from data file should be copied to Master File "N3", then the next time to "N4" and then to "N5" and so on... so it should find the first free cell in column N.

主文件：数据文件中的单元格 S1 应复制到主文件“N3”，然后下一次复制到“N4”，然后复制到“N5”等等......所以它应该找到 N 列中的第一个空闲单元格。

Cell S2 from data file should be copied to Master file "H3" and from there downward in the same manner as previous.

应将数据文件中的单元格 S2 复制到主文件“H3”，然后以与以前相同的方式从那里向下复制。

Cell S13 from data file should be copied to Master file "A3" and downwards in the same manner as previous.

数据文件中的单元格 S13 应以与以前相同的方式复制到主文件“A3”并向下复制。

Edit:

编辑：

This is what works in the master file to retrieve the entire column B, but it overwrites the whole column. Hence it works well to import work hours from one file, that is always the same. Then again for the analysis data file the problem is that the code above works from the master file and in this case when the data file actually changes every day I have no idea how to change this so that it would retrieve the right file.

这是在主文件中检索整个 B 列的工作原理，但它会覆盖整个列。因此，从一个文件导入工作时间很有效，这总是相同的。然后对于分析数据文件，问题是上面的代码从主文件工作，在这种情况下，当数据文件每天实际更改时，我不知道如何更改它以便检索正确的文件。

Sub Work_hours()

' copy_paste1 Macro

Dim wsMaster As Worksheet, wbDATA As Workbook
Dim NextRow As Long, LastRow As Long

Set wsMaster = ThisWorkbook.Sheets("Sheet1")
 NextRow = wsMaster.Range("A" & Rows.Count).End(xlUp).Row + 1

Set wbDATA = Workbooks.Open("\C:\users\heppanetti\Google Drive\heppanetti\outbound\working_hours.xlsx")

 With wbDATA.Sheets("Sheet1")
 Columns("B:B").Select
 Selection.Copy
 Windows("ColdCalling_Stats_template.xlsx").Activate
 Columns("B:B").Select
 ActiveSheet.Paste

 wbDATA.Close False
 End With
 End Sub

回答by Alex Günsberg

I got the first part of the macro working, but now I should be able to switch between documents. The Sender document name is unknown which poses a problem in activation...

我让宏的第一部分正常工作，但现在我应该能够在文档之间切换。发件人文档名称未知，这会导致激活问题...

Option Explicit

Sub SendToMaster()
'this macro goes IN the sender workbook
Dim wsSEND As Worksheet, wbMASTER As Workbook
Dim NextRow As Long, LastRow As Long

Set wsSEND = ThisWorkbook.Sheets("Sheet1")
 Range("S2").Select
Selection.Copy

Set wbMASTER = Workbooks.Open("\c:\users\heppanetti\Google Drive\heppanetti\outbound\ColdCalling_stats_template.xlsx ")
Windows("ColdCalling_stats_template.xlsx").Activate
Range("H1").End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False

 'here the macro should activate the SENDER workbook again.
 'However the name is unknown, thus Windows("fileName.xlsm").Activate does not work
  Set wsSEND = ThisWorkbook.Sheets("Sheet1")
  Range("S1").Select
Selection.Copy


 Windows("ColdCalling_stats_template.xlsx").Activate
 Range("N1").End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False




 wbMASTER.Close True     'save and close the master

 End Sub