excel VBA令人难以置信的新手。我在许多不同的地方都发现了这个问题,但似乎无法操纵我尝试工作的代码。本质上,我有一个工作簿,可以创建一个新工作簿,完全复制一张工作表,保存工作簿,然后需要从原始工作簿转移第二张工作表。我已经做到了:
Wb1.Sheets("Scorecard").Range("A1:M37").Value(11) = Wb.Sheets("Scorecard").Range("A1:M37").Value(11)
其中Wb1是“转移到”工作簿,Wb是“转移自”工作簿。问题在于这不是页眉/页脚,图片(徽标)或隐藏本应隐藏的列。
我已经尝试过了,但是似乎只是打开了另一本书,冻结了我的系统,然后打开了第三本书:
Wb.Sheets("Scorecard").Copy
Wb1.Sheets("Scorecard").PasteSpecial xlPasteValues
我觉得自己似乎遗漏了一些非常明显的东西,但是我厌倦了将头撞在墙上的事情。完整的代码如下:
Sub NewWb()
Dim Aname As String
Dim HospName As String
Dim DateDone As String
Dim xPath As String
Dim Wb As Workbook
Dim Wb1 As Workbook
Set Wb = ThisWorkbook
xPath = Application.ActiveWorkbook.Path
Aname = "CS Diversion Prevention Assessment"
HospName = Sheets("Hospital ID").Range("I8")
DateDone = Replace((Sheets("Hospital ID").Range("I13").Text), "/", "-")
Application.ScreenUpdating = False
ActiveWorkbook.Sheets("Compliance Checklist").Copy
With ActiveSheet.UsedRange
.Value = .Value
End With
ActiveSheet.Shapes("Button 1").Delete
Sheets.Add.Name = "Scorecard"
ActiveWorkbook.SaveAs Filename:=xPath & "\" & HospName & "." & Aname & "." & DateDone & ".xlsx"
Set Wb1 = Workbooks.Open(xPath & "\" & HospName & "." & Aname & "." & DateDone & ".xlsx")
Wb.Activate
提前致谢
使用该Worksheet.Copy
方法,这在Excel 2013上对我有用。我也尝试使用该Worksheet.Move
方法进行移动,但是花了大约10秒钟来移动工作表(!)。
Public Sub copy_it_buster()
Dim to_copy As Worksheet, copy_after As Worksheet
Set to_copy = Workbooks("Book2").Sheets(1) ' Or whatever worksheet you're moving from
Set copy_after = Workbooks("Book3").Sheets(1) ' Or whatever worksheet you want to copy after.
' Note that this is in a different workbook!
to_copy.Copy After:=copy_after
'to_copy.Move After:=copy_after ' if you don't want to keep it where
' it was originally
End Sub
编辑2:复制后,要将所有公式转换为固定值,请尝试由Ron de Bruin的MVP网站提供:
dim sh as Worksheet
set sh = ' whatever your new worksheet is
With sh.UsedRange
.Value = .Value
End With
编辑对于Move
我最初测试的情况,当我开始时:
Book2 Book3
Sheet1 Sheet1
我结束了:
no Book2! Book3
Sheet1 Sheet1 (2)
修改为Copy
,我得到:
Book2 Book3
Sheet1 Sheet1 Sheet1(2)
请注意,这要求目标工作簿已经存在。另外,如果您想复制/移动到现有工作表之前而不是之后,只需在调用中将更改After
为。Before
Copy
PS:为了减少对这个项目和以后项目的挫败感,请使用诸如FromWorkbook
和ToWorkbook
而不是Wb
和Wb1
。看起来似乎不多,但是以我的经验来看,它大大减少了调试时的工作量!
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句