将当前工作表的内容复制到 vba 创建的工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18618459/
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
Copy contents of current worksheet to vba created worksheet
提问by Matthew Paulin
I'm tearing my hair out trying to do what should be a really simple adjustment to a macro.
我正在努力对宏进行真正简单的调整。
copy and paste doesn't seem to work. I get a property not supported error.
复制和粘贴似乎不起作用。我收到一个不支持的属性错误。
All I am trying to do is copy all cell contents from the original activesheet in the originating workbook (which will be sName) and paste it to the new workbook sheet(rvname)
我想要做的就是从原始工作簿（将是 sName）中的原始活动表中复制所有单元格内容并将其粘贴到新工作簿表（rvname）
Here is my current code: (I need it to work in excel 2003 and 2007)
这是我当前的代码：（我需要它在 excel 2003 和 2007 中工作）
Sub create_format_wb()
'This macro will create a new workbook
'Containing sheet1,(job plan) Original, (job plan) Revised, and 1 sheet for each task entered in the inputbox.
Dim Newbook As Workbook
Dim i As Integer
Dim sName As String
Dim umName As String
Dim rvName As String
Dim tBox As Integer
Dim jobplannumber As String
Dim oldwb As String
line1:
tBox = Application.InputBox(prompt:="Enter Number of Tasks", Type:=1)
If tBox < 1 Then
MsgBox "Must be at least 1"
GoTo line1
Else
sName = ActiveSheet.Name
umName = (sName & " Original")
rvName = (sName & " Revised")
jobplannumber = sName
Set Newbook = Workbooks.Add
With Newbook
.Title = sName
.SaveAs Filename:=(sName & " .xls")
.Application.SheetsInNewWorkbook = 1
.Sheets.Add(, After:=Sheets(Worksheets.Count)).Name = umName
Worksheets(umName).Range("A1").Select
With Worksheets(umName).QueryTables.Add(Connection:= _
"ODBC;DSN=MX7PROD;Description=MX7PROD;APP=Microsoft Office 2003;WSID=USOXP-93BPBP1;DATABASE=MX7PROD;Trusted_Connection=Yes" _
, Destination:=Range("A1"))
.CommandText = Array( _
"SELECT jobplan_print.taskid, jobplan_print.description, jobplan_print.critical" & Chr(13) _
& "" & Chr(10) & "FROM MX7PROD.dbo.jobplan_print jobplan_print" & Chr(13) & "" & Chr(10) _
& "WHERE (jobplan_print.jpnum= '" & jobplannumber & "' )")
.Name = "Query from MX7PROD"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
.Worksheets(umName).UsedRange.Columns.AutoFit
.Sheets.Add(, After:=Sheets(Worksheets.Count)).Name = rvName
For i = 1 To tBox
.Sheets.Add(, After:=Sheets(Worksheets.Count)).Name = ("Task " & i)
Next i
End With
Worksheets(rvName).UsedRange.Columns.AutoFit
End If
End Sub
Can somone walk me through how to go about this?
有人可以指导我如何解决这个问题吗？
Any help appreciated.
任何帮助表示赞赏。
回答by rory.ap
You could do something like this:
你可以这样做：
Sub Copy()
Workbooks("Book1").Worksheets("Sheet1").Cells.Copy
Workbooks("Book2").Worksheets("Sheet1").Range("A1").Select
Workbooks("Book2").Worksheets("Sheet1").Paste
End Sub
FYI, if you record macros in Excel while doing the operations you'd like to code, you can often get what you're looking for with minimal modifications to the automatically-generated macro code.
仅供参考，如果您在执行您想要编码的操作时在 Excel 中记录宏，您通常可以通过对自动生成的宏代码进行最少的修改来获得所需的内容。
回答by Zenadix
To copy the contents of a worksheet to another existingworksheet:
要将工作表的内容复制到另一个现有工作表：
wsDest.UsedRange.Clear 'Clear the contents of the destination sheet
wsSource.UsedRange.Copy Destination:=wsDest.Range("A1")
Where wsSourceand wsDestare the source and destination worksheets respectively.
哪里wsSource和wsDest分别是源工作表和目标工作表。
回答by Joe Mahley
Would you consider copying the worksheet as a whole? Here's a basic example, you'll have to customize your workbook organization and naming requirements.
您会考虑将工作表作为一个整体复制吗？这是一个基本示例，您必须自定义工作簿组织和命名要求。
Sub CopyWkst()
Dim wksCopyMe As Worksheet
Set wksCopyMe = ThisWorkbook.Worksheets("Sheet1")
wksCopyMe.Copy After:=ThisWorkbook.Sheets(Worksheets.Count)
ThisWorkbook.ActiveSheet.Name = "I'm New!"
End Sub
