vba 宏将一行数据从一个工作簿复制到另一个
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20991908/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
macro to copy one row of data from one workbook to another
提问by Leanne Gilmore
Hi I'm new to VBA want to copy one row of code that starts on C:14 to K:14 and paste it to another workbook into row C5 to K5. I have written this code but still get errors, am I on the right tracks?
嗨，我是 VBA 新手，想复制一行从 C:14 开始到 K:14 的代码，然后将其粘贴到另一个工作簿中的 C5 到 K5 行。我已经编写了这段代码，但仍然出错，我在正确的轨道上吗？
Sub Shankill()
Dim sBook_t As String
Dim sBook_s As String
Dim sSheet_t As String
Dim sSheet_s As String
Dim lMaxRows_t As Long
Dim lMaxRows_s As Long
Dim sMaxCol_s As String
Dim sRange_t As String
Dim sRange_s As String
sBook_t = "Shankill DR.xls"
sBook_s = "DR complete.xls"
sSheet_t = "Shankill DR"
sSheet_s = "DR complete"
lMaxRows_t = Workbooks(sBook_t).Sheets(sSheet_t).Cells(Rows.Count, "C:14").End(xlUp).Row
lMaxRows_s = Workbooks(sBook_s).Sheets(sSheet_s).Cells(Rows.Count, "C5").End(xlUp).Row
sMaxCol_s = Workbooks(sBook_s).Sheets(sSheet_s).Cells(1, Columns.Count).End(xlToLeft).Address
sMaxCol_s = Mid(sMaxCol_s, 2, InStr(2, sMaxCol_s, "$") - 2)
If (lMaxRows_t = 1) Then
sRange_t = "C14:" & sMaxCol_s & lMaxRows_s
sRange_s = "C:5" & sMaxCol_s & lMaxRows_s
Workbooks(sBook_t).Sheets(sSheet_t).Range(sRange_t) = Workbooks(sBook_s).Sheets(sSheet_s).Range(sRange_s).Value
Else
sRange_t = "C:14" & (lMaxRows_t + 1) & ":" & sMaxCol_s & (lMaxRows_t + lMaxRows_s - 1)
sRange_s = "C:5" & sMaxCol_s & lMaxRows_s
Workbooks(sBook_t).Sheets(sSheet_t).Range(sRange_t) = Workbooks(sBook_s).Sheets(sSheet_s).Range(sRange_s).Value
End If
End Sub
回答by Siddharth Rout
want to copy one row of code that starts on C:14 to K:14 and paste it to another workbook into row C5 to K5.
想要将从 C:14 开始的一行代码复制到 K:14 并将其粘贴到另一个工作簿中的行 C5 到 K5。
All you need is
所有你需要的是
Sub Shankill()
Dim sBook_t As String
Dim sBook_s As String
Dim sSheet_t As String
Dim sSheet_s As String
sBook_t = "Shankill DR.xls"
sBook_s = "DR complete.xls"
sSheet_t = "Shankill DR"
sSheet_s = "DR complete"
Workbooks(sBook_t).Sheets(sSheet_t).Range("C14:K14").Copy _
Workbooks(sBook_s).Sheets(sSheet_s).Range("C5")
End Sub
Followup from Comments
评论的跟进
Yes I want to copy across the values that are the result of a calculation?
是的，我想复制计算结果的值吗？
Change your code to this (Untested)
将您的代码更改为此（未经测试）
Sub Shankill()
Dim sBook_t As String
Dim sBook_s As String
Dim sSheet_t As String
Dim sSheet_s As String
sBook_t = "Shankill DR.xls"
sBook_s = "DR complete.xls"
sSheet_t = "Shankill DR"
sSheet_s = "DR complete"
Workbooks(sBook_t).Sheets(sSheet_t).Range("C14:K14").Copy
Workbooks(sBook_s).Sheets(sSheet_s).Range("C5").PasteSpecial Paste:=xlPasteValues
End Sub
回答by Sagar Chavan
Try this
尝试这个
Sub macro1()
Range("A3:M6").Copy Destination:=Cells(ActiveCell.Row + 1, 1)
End Sub
