extracting data from one workbook and pasting into another workbook automation

[danhendo888]

I have two workbooks.

https://1drv.ms/x/s!AvjBsEPEq12ngS2YS0oBxz5iM-e9?e=Yp3tbV

Workbook 1 just has one sheet of data in a table format. There are many columns. One of them is called "Vendor" and is composed of the names of companies.
Workbook 2 has many sheets. Each company name in the first workbook make up the names of the sheet names in this workbook.

In summary this is what I do:
Extract data from software. Paste the data in Workbook 1
Open Workbook 2. Go to sheet called Company 1.
Filter Workbook 1 to show only records for Company 1 (via the Vendor column)
Copy and paste the data into the last sheet in Workbook 2.
Refresh formulas in the sheet called Company 1
Delete the data that I pasted in the last sheet in Workbook 2
Go to sheet called Company 2.. and so and so forth

I separate the data and the sheets into two separate workbooks because if I have everything in one workbook, it lags/freezes a lot.

Is there a way around working with many sheets with formulas AND a big data table in ONE workbook.
Could I automate the above steps somehow? What would you recommend to consider for automation?
Thank you

 

[danhendo888]

How do I copy and create a macro?
I just need to change the name of the sheet used (Vendor) to different sheet names.
Also, if there is code to open and close the data workbook, would be great to incorporate it into the code.


Sub test()
'
' test Macro
'

'
Windows("Data.xlsm").Activate
Range("A1").Select
Application.CutCopyMode = False
Application.CutCopyMode = False
Application.CutCopyMode = False
Application.CutCopyMode = False
Range("A1:EM22099").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Workbooks("Sheet2.xlsm").Sheets("Vendor").Range("A1:A2"), CopyToRange:=Range( _
"A22102"), Unique:=False
Windows("Sheet2.xlsm").Activate
Windows("Data.xlsm").Activate
Range("A1").Select
Selection.End(xlDown).Select
Range("A22100").Select
Selection.End(xlDown).Select
Range("A22103").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Windows("Sheet2.xlsm").Activate
Sheets("Sheet2").Select
Range("A2").Select
ActiveSheet.Paste
End Sub

 

[danhendo888]

What is the code to choose no to this option in Macros:
https://imgur.com/a/VdTfjQx