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]

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 Name	Account Reference	Invoice
Freddos are BiS	Wahoo-123	1231​
Freddos are BiS	Wahoo-123	1236​
Hunnie Ltd	Wahoo-124	3151​
Toys are Okay	Wahoo-125	2351​
Toys are Okay	Wahoo-125	123​
Toys are Okay	Wahoo-125	23461​
Toys are Okay	Wahoo-125	346345​

Then with filter example:

Account Name	Account Reference	Invoice
Toys are Okay	Wahoo-125	2351​
Toys are Okay	Wahoo-125	123​
Toys are Okay	Wahoo-125	23461​
Toys are Okay	Wahoo-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

 

[Akuini]

@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.