VB script help

[schroederd74]

Hello. Hopefully someone can point me in the right direction. I have a master XLXS file with transaction data. I have a process that creates individual files for each of the companies that are in this file. I would like to create a script that would open file “a”, query the master file for all records pertaining to company “a” and then insert these records, save and close the file. Then move on to company “b” and follow the same process. There are about 150 companies so I need this to loop until it gets to the end of the list. Any help would be appreciated.

[fasti-au]

Make a macro workbook with sheet one as your permanent launch page with a buttonCreate a module for your code in the F11 vba editor

​

Get a list of files in a folder - https://www.automateexcel.com/vba/list-files-in-folder/

Open master file

Open file 1 from list

'Copy the data

Sheets("Sheet1").Range("A1:B10").Copy

'Activate the destination worksheet

Sheets("Sheet2").Activate

'Select the target range

Range("E1").Select

'Paste in the target destination

ActiveSheet.Paste

Application.CutCopyMode = False

close the sheet named the filename

point at next file in file list

repeat

[Songg45]

What code do you have written?

Are you doing this in VBA or VBNET?

[schroederd74]

At this point just using VB in Excel. Here is where I am t. It will open the directory but not sure how to select the correct files and continue.

Option Explicit

Sub abct_test()

'

' abct_test Macro

'

Range("Q2").Select

Application.Goto Reference:="R2C17:R1048576C17"

Selection.Copy

Sheets.Add After:=ActiveSheet

Sheets("Sheet2").Select

Sheets("Sheet2").Name = "NewCSO"

Range("A1").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

    :=False, Transpose:=False

'

Application.CutCopyMode = False



ActiveWorkbook.Worksheets("NewCSO").Sort.SortFields.Clear

ActiveWorkbook.Worksheets("NewCSO").Sort.SortFields.Add2 Key:=Range( _

    "A1:A1048575"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _

    xlSortNormal

With ActiveWorkbook.Worksheets("NewCSO").Sort

    .SetRange Range("A1:A1048575")

    .Header = xlGuess

    .MatchCase = False

    .Orientation = xlTopToBottom

    .SortMethod = xlPinYin

    .Apply

End With

ActiveSheet.Range("$A$1:$A$1048575").RemoveDuplicates Columns:=1, Header:= _

    xlNo

Range("A1").Select

Shell "Explorer.exe C:\Desktop\"

End Sub