在多个文件上使用1个宏 [英] Using 1 Macro on Multiple Files
问题描述
Hi,
我正在尝试复制并将100个文件中的宏粘贴到excel中的1个主文件中。我正在使用宏来执行此操作，但是我必须从服务器打开或下载文件，并且每个文件都有不同的名称。我使用创建的宏I
遇到了麻烦，因为在使用第一个文件创建宏之后，所有后续文件都有不同的名称。我的第二个问题是如何将数据粘贴到下一个空行而不是替换第一行中的数据？
I'm trying to copy and paste macros from 100s of files to 1 master file in excel. I'm using macros to do this however I have to open or download a file from an server and each file has a different name. I'm encoutering trouble using the macro I created because after creating the macro using the 1st file all the subsequent files have different names. My second issue is that how do I make the data paste into the next empty row rather than replacing the data in the first row?
我已粘贴到目前为止我所做的事情，但我想知道是否有人可以看看下面，让我知道如何调整代码，以便从打开的文件宏复制？有人还可以检查我是否正确调整宏以将
数据粘贴到下一个可用行中？
I have pasted what I have done so far but I was wondering if someone can take a look below and let me know how I can adjust the code so the macro copies from the file that is open? Can someone also check if I properly adjusted the macro to paste the data in the next availble row?
Sub HandoffDocTestingSept9（）
'
'HandoffDocTestingSept9 Macro
'
'键盘快捷键：Ctrl + Shift + R
'
 范围（"A1"）。选择
  Selection.End（xlDown）。选择
 范围（"A4"）。选择
  Windows（"Hand of Handoff Template for Copy for Sept 07 -2.xlsx"）。激活
 范围（"C3：E3"）。选择
  ActiveCell.FormulaR1C1 ="年份国家[+扩展名] - 客户名称"
  Windows（"关闭doc doc doc v.4.xlsx"）。激活
 范围（"A4"）。选择
  ActiveCell.FormulaR1C1 ="年份国家[+扩展名] - 客户名称"
 范围（"B4"）。选择
  Windows（"Hand of Handoff Template for Copy for Sept 07 -2.xlsx"）。激活
 范围（"C4"）。选择
  ActiveCell.FormulaR1C1 ="Paris"
  Windows（"关闭doc doc doc v.4.xlsx"）。激活
 范围（"B4"）。选择
  ActiveSheet.Paste
 范围（"C4"）。选择
  Windows（"Hand of Handoff Template for Copy for Sept 07 -2.xlsx"）。激活
 范围（"C11"）。选择
  ActiveCell.FormulaR1C1 ="2016年3月11日"
  Windows（"关闭doc doc doc v.4.xlsx"）。激活
 范围（"C4"）。选择
  ActiveCell.FormulaR1C1 ="2016年3月11日"
 范围（"D4"）。选择
  Windows（"Hand of Handoff Template for Copy for Sept 07 -2.xlsx"）。激活
 范围（"C13"）。选择
  Selection.Copy
  Windows（"关闭doc doc doc v.4.xlsx"）。激活
  Selection.PasteSpecial Paste：= xlPasteValues，Operation：= xlNone，SkipBlanks _
      ：= False，Transpose：= False
  Windows（"Hand of Handoff Template for Copy for Sept 07 -2.xlsx"）。激活
 范围（"C20"）。选择
  Application.CutCopyMode = False
  Selection.Copy
  Windows（"关闭doc doc doc v.4.xlsx"）。激活
 范围（"E4"）。选择
  Selection.PasteSpecial Paste：= xlPasteValues，Operation：= xlNone，SkipBlanks _
      ：= False，Transpose：= False
  Windows（"Hand of Handoff Template for Copy for Sept 07 -2.xlsx"）。激活
 范围（"C21"）。选择
  Application.CutCopyMode = False
  Selection.Copy
  Windows（"关闭doc doc doc v.4.xlsx"）。激活
 范围（"F4"）。选择
  Selection.PasteSpecial Paste：= xlPasteValues，Operation：= xlNone，SkipBlanks _
      ：= False，Transpose：= False
 范围（"G4"）。选择
  Windows（"Hand of Handoff Template for Copy for Sept 07 -2.xlsx"）。激活
 范围（"C22"）。选择
  Application.CutCopyMode = False
  Selection.Copy
  Windows（"关闭doc doc doc v.4.xlsx"）。激活
  Selection.PasteSpecial Paste：= xlPasteValues，Operation：= xlNone，SkipBlanks _
      ：= False，Transpose：= False
 范围（"A1"）。选择
  Selection.End（xlDown）。选择
 范围（"H4"）。选择
End Sub
Sub HandoffDocTestingSept9()
'
' HandoffDocTestingSept9 Macro
'
' Keyboard Shortcut: Ctrl+Shift+R
'
Range("A1").Select
Selection.End(xlDown).Select
Range("A4").Select
Windows("Copy of Handoff Template for testing on Sept 07 -2.xlsx").Activate
Range("C3:E3").Select
ActiveCell.FormulaR1C1 = "Year Country [+ Extension] - Client Name"
Windows("Hand off doc working doc v.4.xlsx").Activate
Range("A4").Select
ActiveCell.FormulaR1C1 = "Year Country [+ Extension] - Client Name"
Range("B4").Select
Windows("Copy of Handoff Template for testing on Sept 07 -2.xlsx").Activate
Range("C4").Select
ActiveCell.FormulaR1C1 = "Paris"
Windows("Hand off doc working doc v.4.xlsx").Activate
Range("B4").Select
ActiveSheet.Paste
Range("C4").Select
Windows("Copy of Handoff Template for testing on Sept 07 -2.xlsx").Activate
Range("C11").Select
ActiveCell.FormulaR1C1 = "3/11/2016"
Windows("Hand off doc working doc v.4.xlsx").Activate
Range("C4").Select
ActiveCell.FormulaR1C1 = "3/11/2016"
Range("D4").Select
Windows("Copy of Handoff Template for testing on Sept 07 -2.xlsx").Activate
Range("C13").Select
Selection.Copy
Windows("Hand off doc working doc v.4.xlsx").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("Copy of Handoff Template for testing on Sept 07 -2.xlsx").Activate
Range("C20").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Hand off doc working doc v.4.xlsx").Activate
Range("E4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("Copy of Handoff Template for testing on Sept 07 -2.xlsx").Activate
Range("C21").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Hand off doc working doc v.4.xlsx").Activate
Range("F4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G4").Select
Windows("Copy of Handoff Template for testing on Sept 07 -2.xlsx").Activate
Range("C22").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Hand off doc working doc v.4.xlsx").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Selection.End(xlDown).Select
Range("H4").Select
End Sub
推荐答案
这是讨论Microsoft Excel问题和反馈的论坛，我会把你的问题转到MSDN论坛for Excel
This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel
https://social.msdn .microsoft.com /论坛/ zh-CN / home？论坛= exceldev
我们建议适当发布的原因是您将获得最合格的受访者群，以及定期阅读论坛的其他合作伙伴可以分享他们的知识，也可以从与我们的互动中学习。感谢您的理解。
The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.
这篇关于在多个文件上使用1个宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
