Vba code for help-CodePudding

Teacher, can I leave you a excel vb problem, baidu for a long time all can not find a solution,
This macro function from a workbook to extract three working table, this functionality is implemented, but not general, on the other work book when I was in other workbook is running, he is still the original workbook, the scope of the
Now I want to solve the problem is how to use this macro to the same template list of all the work
Below is the code: Sub copy z ()

Dim s As String
S=ThisWorkbook. Name
Application. ScreenUpdating=False

Dim GZB As Workbook

The Set GZB=Workbooks. Add

ActiveSheet. Name="listing"
Sheet1. Cells. Copy [a1]

Worksheets. The Add (after:=Worksheets (Worksheets. Count)). The Name="packing list"
Sheet101. Cells. Copy [a1]

Worksheets. The Add (after:=Worksheets (Worksheets. Count)). The Name="invoice"
Sheet9. Cells. Copy [a1]

GZB. SaveAs ThisWorkbook. Path & amp; "\ Tracking#" & amp; S & amp; ". XLS ", FileFormat:=xlExcel8

The Set GZB=Nothing

ActiveWorkbook. Close

Application. ScreenUpdating=True

End Sub

CodePudding user response:

The

Quote: refer to the original poster weixin_45652504 response:

Is not very clear what you mean, can say clearly or send attachments up?
CodePudding user response:
I have just tried it on, if you copy the code to the extracted file and it is no problem;
But you can't copy the code is, in other workbook call succeeds copy of copying "z" to the process of the workbook, which is across the workbook call macros, right?
CodePudding user response:
Change is good, the code is as follows:
 
Option Explicit 

Sub copy z () 

Application. ScreenUpdating=False 

Dim wb As Workbook 
The Set wb=Application. ActiveWorkbook 

Dim GZB As Workbook 
The Set GZB=Workbooks. Add 

Dim objSheet As Worksheet 
The Set objSheet=GZB. ActiveSheet 
ObjSheet. Name="listing" 
Wb. Worksheets (" Sheet1 ") Cells. The Copy objSheet. [a1] 

The Set objSheet=Worksheets. The Add (after:=Worksheets (Worksheets. Count)) 
ObjSheet. Name="packing list" 
Wb. Worksheets (" Sheet101 ") Cells. The Copy objSheet. [a1] 

The Set objSheet=Worksheets. The Add (after:=Worksheets (Worksheets. Count)) 
ObjSheet. Name="invoice" 
Wb. Worksheets (" Sheet9 ") Cells. The Copy objSheet. [a1] 

GZB. SaveAs wb. Path & amp; "\ Tracking#" & amp; Wb. Name & amp; ". XLS ", FileFormat:=xlExcel8 

GZB. Close 
The Set GZB=Nothing 

Application. ScreenUpdating=True 
End Sub 
Download address:
Link: https://pan.baidu.com/s/1206M0jP-5jmqmsqklDaOhA
The extracted code: XPJR

Run the example: