Macro That Opens Another Workbook, Filters Results and Spits into Separate Workbooks

[Ravin]

Hello all, I need some help

I am trying to create a macro that opens a specific workbook in a specific location, or allows me to choose the workbook I am opening

I then want to go to a specific tab and filter row 4 of that opened workbook
I then want to filter column U for the value "V" of that opened workbook

from the presented results

I will have multiple records per ID in column F (The values in F will be a 5 digit number)

ID, Name, Age, Transport
01486 - Peter, 12, Red, Car
01486 - Fred, 0, Orange, Bike
56613 - Rob, 10, Yellow, Boat

I want to paste the values for each id in column F into my macro template

any values in column G into A11 onwards of my template for that ID
any values in column F into B11 onwards of my template for that ID
any values in column A into C11 onwards of my template for that ID
any values in column B into D11 onwards of my template for that ID

then from my macro template

for each ID I want to create a separate non macro workbook, but with headers etc, named as that ID and date i.e 01486_2019-03-22 in a specific location

is this possible, because I am really struggling

R

 

[DanteAmor]

I'm confused, I do not know where you want the information from.

We have 4 sheets.
sh1, sh2, sh3, sh4

sh3 does not work for you, so try sh1, sh2 and sh4

in this line:

[COLOR=#333333]wName = ant & " - " & [/COLOR][COLOR=#ff0000]sh3[/COLOR][COLOR=#333333].Cells(2, "G").Value & " " & Format(Date, "yyyy-mm-dd")[/COLOR]

 

[Ravin]

Hi Dante

i've tried all the sheets and sh3 is the correct sheet. But changing the code to

wName = ant & " - " & sh3.Cells(2, "G").Value & " " & Format(Date, "yyyy-mm-dd")

only partially works

for the first file I get the code and the correct name
for the second file I get a new code but the same name as above
for the third file I get a new code and the same name as above

it only appears to spit out separate files with the correct code

as defined sh3.Cells(2, "F").Value (this works)

but the next part sh3.Cells(2, "G").Value (this repeats)

Format(Date, "yyyy-mm-dd") (this works)

So I get something that looks like below

L - Lemon
M- Lemon
S- Lemon

instead of

L- Lemon
M- Melon
S - Strawberry

R

 

[DanteAmor]

I still do not understand
Check what you have on each sheet of each file in cell G2 and tell me what you want to put.
It is possible that in each sheet of each file in cell G2 you have the same information and that is why it says the same thing.

 

[Ravin]

Ok so I open Macro

it opens the master file with data such as

id1, desc, desc2, desc3, desc4, then supplier id, then supplier name
id2, desc, desc2, desc3, desc4, then supplier id, then supplier name
id3, desc, desc2, desc3, desc4, then supplier id, then supplier name
id4, desc, desc2, desc3, desc4, then supplier id, then supplier name

possible 100k records on main file

from that it is creating a separate file for each supplier id with potentially 100 or so id's per file

that all works

when the temp sheet is created within macro it will have the supplier id, supplier name repeated for how ever many ids are associated
then it moves onto next supplier name below

what is happening is I have a correct file for each supplier ID and dated correctly

am just looking to pass the supplier name along with the ID for each output file

but it apportions the same supplier name to the unique supplier ID's



R

 

[DanteAmor]

I do not understand you.
Do not use which book-sheet-cell to get the data you want.
You could upload several files with examples and you put the cell in yellow from where you want to get the data.

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

And also you put the macro that you are occupying in some of the books.

 

[Ravin]

Hi Dante

Macro Link
https://www.dropbox.com/s/s5icfpg8ypygnj6/DSV Stock Template (Macro).xlsm?dl=0

File that Macro Opens (B)
https://www.dropbox.com/s/g4010a31spzgv83/New Range Classification Week 19 2019.xlsx?dl=0

From (B) we create the separate files

they get named for example 00684 10/05/2019

I would like them named 00684 - Plastics - 10/05/2019

its just the addition of the supplier name to the file name

R

 

[DanteAmor]

I checked your book on the "Final Classification" sheet, in column F, the Supplier Number "00684" in column G says "Home Decor (Home Delivered)".
Where do you want to get the data "Plastics"?

If you are going to send me another file with the explanation, send me a file with a single sheet, with represntative data, I can not work with a file of 75 megabytes.

 

[Ravin]

Sorry I was rushing

So I expected the spat out files to be named

00684 - Home Decor - 10/05/2019
00845 - Keter Uk Limited - 10/05/2019

so 00684 coming from Column F the supplier number
and Home Decor the supplier description coming from column G

but when you run the code with the change you indicated earlier it does this


00684 - Home Decor - 10/05/2019
00845 - Home Decor - 10/05/2019

it recycles the same supplier description for each spat out file, the number and date are correct and the contents of each file are correct its just the naming of the file

R

 

[DanteAmor]

Sorry I was rushing

So I expected the spat out files to be named

00684 - Home Decor - 10/05/2019
00845 - Keter Uk Limited - 10/05/2019

so 00684 coming from Column F the supplier number
and Home Decor the supplier description coming from column G

but when you run the code with the change you indicated earlier it does this


00684 - Home Decor - 10/05/2019
00845 - Home Decor - 10/05/2019

it recycles the same supplier description for each spat out file, the number and date are correct and the contents of each file are correct its just the naming of the file

R

I found it!

wName = ant & " " & [COLOR=#0000cd]sh4.Range("A11").Value[/COLOR] & " " & Format(Date, "yyyy-mm-dd")

 

[Ravin]

Hi Dante

When I run original macro it runs correctly creates 71 files, and then completes with a message box that says end. So that works OK.

When I amend code to wName = ant & " " & sh4.Range("A11").Value & " " & Format(Date, "yyyy-mm-dd")

it stops at 21 files with a run time error, but it does name them correctly

R