File Name Variables in Macros

[Balfin]

Hi,

Is there a way to assign a variable to a file name, so that I don't have to type the file name in the code every time I want to switch windows? The filename is quite long and made up of several variables so that it contains the date.

Thanks!

 

[Balfin]

We aren't suppose to download anything on the computers at work, so I'll have to do without the HTML Maker.

It looks roughly like this

ID | City | Location | Status | Description | $ | Hr | Code

And city is the only column that matters for breaking the data into separate files. I'll need to take some averages for $ and Hr, but I know how to do that part. The rest is basically just along for the ride, as far as the macro is concerned.

 

[Norie]

Balfin

I'm getting more confused, but that's probably me.

You've now mentioned averages.

What exactly are you trying to do?

I could give you code that seperates data out into new worksheets/workbooks based on criteria in a particular column.

But it sounds like you want to do a lot more.

Any chance of some sample data?

I realise you can't download the Add-in, but seeing some data and the expected result would really help I think.

 

[Balfin]

Um, sorry ignore the average thing. I think I can get that myself when I reach that point. I just need to get the data split out first.

And it will be workbooks, not worksheets if that makes much of a difference to the code.

 

[Norie]

Balfin

It's hard to help without some sample data.

The following code will create new workbooks based on criteria in column A.

Sub DistributeRows()
Dim wbNew As Workbook
Dim wsData As Worksheet
Dim wsCrit As Worksheet
Dim wsNew As Worksheet
Dim rngCrit As Range
Dim LastRow As Long
    
    Set wsData = Worksheets("Master (2)")
    Set wsCrit = Worksheets.Add
    
    LastRow = wsData.Range("A" & Rows.Count).End(xlUp).Row
    
    wsData.Range("A1:A" & LastRow).AdvancedFilter action:=xlFilterCopy, CopyToRange:=wsCrit.Range("A1"), Unique:=True
    
    Set rngCrit = wsCrit.Range("A2")
    While rngCrit.Value <> ""
        Set wsNew = Worksheets.Add
        wsData.Range("A1:E" & LastRow).AdvancedFilter action:=xlFilterCopy, CriteriaRange:=rngCrit.Offset(-1).Resize(2), CopyToRange:=wsNew.Range("A1"), Unique:=True
        wsNew.Name = rngCrit
        wsNew.Copy
        Set wbNew = ActiveWorkbook
        wbNew.SaveAs ThisWorkbook.Path & "\" & rngCrit
        wbNew.Close SaveChanges:=True
        Application.DisplayAlerts = False
        wsNew.Delete
        rngCrit.EntireRow.Delete
        Set rngCrit = wsCrit.Range("A2")
    Wend
    
    wsCrit.Delete
    Application.DisplayAlerts = True
End Sub

Note to use it in your setup it'll probably need a few changes.

 

[Balfin]

Ok, here's a quick sample

ID | City | Location | Status | Description | $ | Hr | Code
1 | N1 | ABC | A | DEF | 1 | 1 | 123
2 | N1 | GHI | A | JKL | 2 | 5 | 456
3 | N2 | MNO | A | PQR | 3 | 2 | 789
4 | W1 | STU | A | VWX | 9 | 8 | 012
5 | O1 | YZA | A | BCD | 5 | 6 | 345
6 | W1 | EFG | A | HIJ | 4 | 4 | 678

In the end, all the N cities will be in one file, the W cities in another the O cities in third.

I made some variables to fill in for city names because they are long and it was easier to variables (the original names look like "143 (New York)"). They are N1, N2, N3, D1 - D4, W1 - W4, O1 - O4, H1, P1 and P2.