VBA Question - How to automate copy and pasting unique information tied to an E.g Company Name from a single worksheet but for 1000's of companies

Question129

New Member
Joined
Jan 6, 2021
Messages
1
Office Version
  1. 2007
Hi All

It's my first time using Excel VBA's and I had a question as title suggests.

To break it down to you, I want to create a 1000 unique files xlsx from a single worksheet. This single worksheet, let's call it ''Transaction List'' will contain all transactions for every account. I want to able to create a statement for every account from this transaction list, but also with a unique name for every file as well.

To add as well, the top row (I.e Account Name, Account Reference and Invoice) all have a filter. So I can search by all transaction just for Toys are Okay and exclude the rest.

Example below of what it looks like: (Transaction List)


Account NameAccount ReferenceInvoice
Freddos are BiSWahoo-123
1231​
Freddos are BiSWahoo-123
1236​
Hunnie LtdWahoo-124
3151​
Toys are OkayWahoo-125
2351​
Toys are OkayWahoo-125
123​
Toys are OkayWahoo-125
23461​
Toys are OkayWahoo-125
346345​

Then with filter example:

1609952775384.png




Account NameAccount ReferenceInvoice
Toys are OkayWahoo-125
2351​
Toys are OkayWahoo-125
123​
Toys are OkayWahoo-125
23461​
Toys are OkayWahoo-125
346345​

I want to be able to paste that information in the table above, into a statement format. So I will paste that information into a blank statement format.

But the issue, I'm coming across with this, is that I can't record the VBA Macro in a way to jump to next filter box. It contains a unique reference.

ActiveSheet.Range("$A$1:$C$8").AutoFilter Field:=1, Criteria1:= _
"Freddos are BiS"


It's this part here the "Freddos are BiS" is creating an issue. Because I can't go through a 1000 accounts uniquely changing the VBA to tailor it.

Would anyone know a better way of dealing with this? And also any way of saving files in an automated way that was unique? Like a statement file .xlsx called ''Hunnie Ltd''?

Thank you in advance! It's very complicated and it would really save me a lot of time, so any answers is very generous.





An example of the VBA is below of the actions taken:

ActiveSheet.Range("$A$1:$C$8").AutoFilter Field:=1
ActiveSheet.Range("$A$1:$C$8").AutoFilter Field:=1, Criteria1:= _
"Freddos are BiS"
Selection.Copy
Sheets.Add After:=ActiveSheet
ActiveSheet.Paste
Sheets("Sheet3").Select
Range("A2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet3").Select
Sheets("Sheet3").Name = "Freddos are BiS"
Sheets("Freddos are BiS").Select
Application.CutCopyMode = False
Sheets("Freddos are BiS").Copy After:=Sheets(3)
Sheets("Freddos are BiS (2)").Select
Sheets("Freddos are BiS (2)").Move
Sheets("Freddos are BiS (2)").Select
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\Desktop\Freddos are BiS.xlsx", FileFormat:=xlOpenXMLWorkbook _
, CreateBackup:=False
ActiveWindow.Close
Sheets("Transactions List").Select
ActiveSheet.Range("$A$1:$C$8").AutoFilter Field:=1, Criteria1:="Hunnie Ltd"
Selection.Copy
Sheets("Statement").Select
Sheets.Add After:=ActiveSheet
ActiveSheet.Paste
Range("A2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet5").Select
Sheets("Sheet5").Name = "Hunnie Ltd"
Range("A1:C2").Select
Range("C2").Activate
Application.CutCopyMode = False
Selection.Copy
Sheets("Hunnie Ltd").Select
Application.CutCopyMode = False
Sheets("Hunnie Ltd").Move
ActiveWorkbook.SaveAs Filename:="C:\Usersl\Desktop\Hunnie Ltd.xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False



1609952775384.png
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,963
Office Version
  1. 365
Platform
  1. Windows
@Question129 Welcome to the Forum.
Could you upload 2 sample workbooks to a free site such as dropbox.com or google drive & then share the link here?
The first workbook is the workbook that has the data & the second one is the output (say for "Freddos are BiS")
Because I don't quite understand what the output should look like.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,207
Messages
5,640,848
Members
417,173
Latest member
Tuanphun

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top