作者热门文章
- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我正在寻找有关如何编写执行以下操作的宏的建议。我想它很容易做到,但我无法弄清楚。提前致谢!
开始
Sub Copy_Timesheet()
'
' Copy_Timesheet Macro
'
'
Selection.Copy
Windows("WorkbookB").Activate
Find_Blank_Row()
Dim BlankRow As Long
BlankRow = Range("A65536").End(xlUp).Row + 1
Cells(BlankRow, 1).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ThisWorkbook.Activate
ActiveCell.Offset(3, 0).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Windows("WorkbookB").Activate
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ThisWorkbook.Activate
ActiveCell.Offset(-4, 0).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Windows("WorkbookB").Activate
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ThisWorkbook.Activate
ActiveCell.Offset(9, -1).Range("A1:E1").Select
Application.CutCopyMode = False
Selection.Copy
Windows("WorkbookB").Activate
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ThisWorkbook.Activate
ActiveCell.Offset(0, 6).Range("A1:H1").Select
Application.CutCopyMode = False
Selection.Copy
Windows("WorkbookB").Activate
ActiveCell.Offset(0, 5).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
最佳答案
现在您已经在生成代码方面付出了一些努力,这里是您所说的内容的重构版本。 (我没有检查这是否与您实际录制的内容相匹配,但是您不厌其烦地录制某些东西的事实表明您自己并没有懒得做。)
Sub Copy_Timesheet()
'Set up some objects to make life easier in the rest of the code
' "the active sheet (in the workbook I am running this macro in)"
Dim wsSrc As Worksheet
Set wsSrc = ThisWorkbook.ActiveSheet
'the sheet in the other workbook
Dim wsDst As Worksheet
Set wsDst = Workbooks("WorkbookB").Worksheets("destination_sheet_name") 'change sheet name to whatever you need
Dim BlankRow As Long
'Fully qualify ranges so that we ensure we are working with the sheet we expect to be
'Use Rows.Count rather than 65536 just in case we are working in a recent workbook that allows 1048576 rows
BlankRow = wsDst.Range("A" & wsDst.Rows.Count).End(xlUp).Row + 1
'In the active sheet (in the workbook I am running this macro in [Title changes but same formatting each time]), copy cell B9. Paste in column A on the next blank row of the other workbook I am using [Can have the same title every time I run this process, or just be the only other workbook open]
wsDst.Range("A" & BlankRow).Value = wsSrc.Range("B9").Value
'In the active sheet (in the workbook I am running this macro in), copy cell B8. Paste in column B of the row identified above.
wsDst.Range("B" & BlankRow).Value = wsSrc.Range("B8").Value
'In the active sheet (in the workbook I am running this macro in), copy cell B12. Paste in column C of the row identified above.
wsDst.Range("C" & BlankRow).Value = wsSrc.Range("B12").Value
'In the active sheet (in the workbook I am running this macro in), copy cells A17:E17. Paste in D:H of the row identified above.
wsDst.Range("D" & BlankRow & ":H" & BlankRow).Value = wsSrc.Range("A17:E17").Value
'In the active sheet (in the workbook I am running this macro in), copy cells A17:E17. Paste in D:H of the row identified above.
'No need to do this - we just did it
'In the active sheet (in the workbook I am running this macro in), copy cells G17:N17. Paste in I:P of the row identified above.
wsDst.Range("I" & BlankRow & ":P" & BlankRow).Value = wsSrc.Range("G17:N17").Value
End Sub
关于VBA - 如何在 Excel 工作簿之间复制单元格(工作簿名称更改的地方)?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/43463812/
我是一名优秀的程序员,十分优秀!