excel - 协助 Excel VBA 参考单元格将工作表移动到新工作簿并保存
问题描述
我浏览了这个论坛和其他论坛来创建代码来完成这项任务。我以前没有 VBA 经验,所以请温柔一点。
本质上,我希望用户填写 Excel 表单并单击按钮。该按钮将引用在单元格 K4 中选择的内容,然后根据该选择,将隐藏的工作表复制到新工作簿中,然后提示用户保存。
我使用的代码是:
Private Sub RSM_Click()
Dim newWkbk As Workbook
Dim newWksht As Worksheet
Dim wksht As Worksheet
Dim test As String
If StrComp(Me.Range("K4").Text, "INTERNAL USB", vbTextCompare) = 0 Then
test = "RSM_InternalUSB"
ElseIf StrComp(Me.Range("K4").Text, "INTERNAL 24 HR", vbTextCompare) = 0 Then
test = "RSM_Internal24Hr"
Else
test = "RSM_External"
End If
For Each wksht In ThisWorkbook.Worksheets
If wksht.Name = test Then
wksht.Visible = xlSheetVisible
Set newWksht = wksht.Copy
newWksht.Name = "RSM Onboarding Guide"
Set newWkbk = newWksht.Parent
End If
Next wksht
Dim varResult As Variant
Dim ActBook As Workbook
'displays the save file dialog
varResult = Application.GetSaveAsFilename(FileFilter:= _
"Excel Files (*.xlsm), *.xlsm", Title:="RSM Guide", _
InitialFileName:="\\Onboarding\")
'checks to make sure the user hasn't canceled the dialog
If varResult <> False Then
ActiveWorkbook.SaveCopyAs Filename:=varResult
Exit Sub
End If
End Sub
但是我得到了一个
编译错误:预期的函数或变量
在Set newwksht = wksht.Copy
链上。它不喜欢副本。
我什至不知道保存部分是否会起作用,因为我无法通过这个
解决方案
可以试试这个:
If GetSheet(test, newWksht) Then
With newWksht
.Visible = xlSheetVisible
.Copy ' this will make a copy of the referenced sheet in a newly created workbook
ActiveSheet.Name = "new"
.Visible = xlSheetHidden
End With
Dim varResult As Variant
'displays the save file dialog
varResult = Application.GetSaveAsFilename(FileFilter:= _
"Excel Files (*.xls*), *.xls*", Title:="RSM Guide", _
InitialFileName:="\\Onboarding\")
With ActiveWorkbook ' reference the newly created workbook, which is the "active" one
'checks to make sure the user hasn't canceled the dialog
If varResult <> False Then .SaveAs Filename:=varResult
End With
End If
它使用这个GetSheet()
“助手”功能的地方:
Function GetSheet(shtName As String, retSht As Worksheet) As Boolean
Set retSht = Worksheets(shtName)
GetSheet = Not retSht Is Nothing
End Function
推荐阅读
- javascript - 在 Javascript 中使用 elementbyid 设置值
- c++ - 如何将 lambda 函数的返回值传递给 C++ 中的另一个函数?
- macos - 为什么 Net-SSLeay 停止通过 GitHub 工作流程在 macOS 上安装?
- typescript - 带有 typescript 3.9 的 ag-grid - 出现构建错误
- css - Angular 的实用 css 框架/库 - Angular Material - Flex Layout 项目
- arrays - 如何获取 mongo 中所有嵌套数组的计数?
- flutter - 状态栏的高度总是返回 0
- android - 错误应为 BEGIN_OBJECT,但在第 1 行第 2 列是 BEGIN_ARRAY
- python - 在类中调用方法时遇到问题
- javacc - Javacc 错误报告导致“扩展可以由空字符串匹配”。