将excel工作表复制到新文件并将其隐藏

Question

我正试图使用excel VBA将一些工作表复制到一个新文件中，我需要隐藏一些工作表(例如输入工作表，"Market PnLs(Market Place("Markets Graph(Market Place("(我已经写了一个VBA来复制图纸，但我现在知道如何在新文件中隐藏上面的图纸，有人能帮我写代码吗？

Sub split_online()
Dim onlinepath As Range
Set onlinepath = ThisWorkbook.Sheets("Input").Range("G33")
With ActiveWorkbook.Sheets(Array("P&L Metrics (Ecomm- Global)", "Market PnLs (Online)", _
"Markets Graph (Online)", "Market Totals (Online)", "GC(Online)", "Apac(Online)", _
"EMEA (Online)", "AM(Online)", "P&L vs LE (Online)", "P&L vs PY (Online)", _
"Market PnLs (Market Place)", "Markets Graph (Market Place)", _
"Market Totals (Market Place)", "GC(Market Place)", "Apac (Market Place)", _
"EMEA (Market Place)", "AM (Market Place)", "P&L vs LE (Market Place)", _
"P&L vs PY (Market Place)", "Input", "Input"))
.Copy
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & Application.PathSeparator & onlinepath, FileFormat:=51
End With
End Sub

Answer 1

创建一个新工作簿，然后将工作表复制到那里。最后隐藏相关表格。

这就是你想要的吗？(未测试(

Option Explicit
Sub split_online()
Dim onlinepath As Range
Dim wbThis As Workbook
Dim wbThat As Workbook

Set wbThis = ThisWorkbook

Set onlinepath = wbThis.Sheets("Input").Range("G33")

'~~> Create a new workbook
Set wbThat = Workbooks.Add

'~~> Copy the relevant sheets to new workbook
wbThis.Sheets(Array("P&L Metrics (Ecomm- Global)", "Market PnLs (Online)", _
"Markets Graph (Online)", "Market Totals (Online)", "GC(Online)", "Apac(Online)", _
"EMEA (Online)", "AM(Online)", "P&L vs LE (Online)", "P&L vs PY (Online)", _
"Market PnLs (Market Place)", "Markets Graph (Market Place)", _
"Market Totals (Market Place)", "GC(Market Place)", "Apac (Market Place)", _
"EMEA (Market Place)", "AM (Market Place)", "P&L vs LE (Market Place)", _
"P&L vs PY (Market Place)", "Input")).Copy Before:=wbThat.Sheets(1)

With wbThat
'~~> Hide relevant sheets
.Sheets(Array("Market PnLs (Market Place)", "Markets Graph (Market Place)")).Visible = False
.SaveAs Filename:=ThisWorkbook.Path & Application.PathSeparator & onlinepath, FileFormat:=51
End With
End Sub

如果您不想要使用Workbooks.add创建的默认图纸，那么您也可以尝试此操作。

Option Explicit
Sub split_online()
Dim onlinepath As Range
Dim wbThis As Workbook
Dim wbThat As Workbook
Dim shtCount As Long, i As Long
Dim shtToDelete As String

Set wbThis = ThisWorkbook

Set onlinepath = wbThis.Sheets("Input").Range("G33")

'~~> Create a new workbook
Set wbThat = Workbooks.Add
shtCount = wbThat.Sheets.Count

'~~> Delete sheet(s) created by default.
Application.DisplayAlerts = False
On Error Resume Next
For i = wbThat.Sheets.Count To 1 Step -1
wbThat.Sheets(i).Delete
Next i
On Error GoTo 0
Application.DisplayAlerts = True

'~~> Get the name of the sole sheet in the new workbook
shtToDelete = wbThat.Sheets(1).Name

'~~> Copy the relevant sheets to new workbook
wbThis.Sheets(Array("P&L Metrics (Ecomm- Global)", "Market PnLs (Online)", _
"Markets Graph (Online)", "Market Totals (Online)", "GC(Online)", "Apac(Online)", _
"EMEA (Online)", "AM(Online)", "P&L vs LE (Online)", "P&L vs PY (Online)", _
"Market PnLs (Market Place)", "Markets Graph (Market Place)", _
"Market Totals (Market Place)", "GC(Market Place)", "Apac (Market Place)", _
"EMEA (Market Place)", "AM (Market Place)", "P&L vs LE (Market Place)", _
"P&L vs PY (Market Place)", "Input")).Copy Before:=wbThat.Sheets(1)

With wbThat
'~~> Delete the default sheet
Application.DisplayAlerts = False
wbThat.Sheets(shtToDelete).Delete
Application.DisplayAlerts = True

'~~> Hide relevant sheets
.Sheets(Array("Market PnLs (Market Place)", "Markets Graph (Market Place)")).Visible = False
.SaveAs Filename:=ThisWorkbook.Path & Application.PathSeparator & onlinepath, FileFormat:=51
End With
End Sub