autofilter, pasting data into a filename, and other delights

[slifer666]

hi
i currently use a big list of data, with an autofilter by a field called Workpackage. the autofilter is used to select all instances of each workpackage, and then print the file (to .pdf files)

data sample, from column B:

MHB6068
SWD6500
SWD7500
SWD8103
MHB6000
SWD6900
MHB4200
MHB4300
MHB2200
MHB7000
MHB4800
SWD8103
MHB6068
MHB2200


some of the items are repeated, eg MHB6068
the purpose is to create a .pdf of every row concerning each workpackage. so i auto filter by each work package in turn, and generate a .pdf for each workpackage, showing the data associated with it.
(this is done every day, so it takes a couple hours off my day, and i suspect it can be largely automated)


so. the manual process has been refined as much as i can, but now i'm attempting to automate it. theres a lot that can be done with this via 'recording macros'. but i'm running into some limitations of it that i can't get around.

for example:
1- the workpackages are not consistent each day. in total there is 230ish of them, but usually only about 30-40 are used each day.
when i recorded a macro of changing the selection on the autofilter, it can't recognise that i've just pressed the down arrow to move down the list to the next item, it does this when i look at the macro code:

Selection.AutoFilter Field:=2, Criteria1:="MHB6000"
Selection.AutoFilter Field:=2, Criteria1:="MHB6068"
Selection.AutoFilter Field:=2, Criteria1:="MHB7000"

the problem is, i can't get it to read the Criteria from the actual cell. i think i'd need to pass the data into the macro... or something
tomorrow there may not be anything for MHB6068, so the macro would try and select that, but it'd bring up no results in the autofilter, and if i tried to print it, it'd all be blank (apart from the top rows which are title and column headers and so on)

i want it to try and select each item that's actually in the autofilter, one at a time. (for printing)

i suspect one approach actually, may be to just select every one of the potential workpackages that exists (the full list of 230 available workpackages doesn't change), try to select it, and then if there are no items for that workpackage for that day, just dont print the file and move on to the next one.
this would solve a later problem of how to print each item, and how to know when to stop the loop.



2- edited out - solved



3- edited out - solved, mostly


4- edited out - solved


ok, sorry if that was a bit garbled. if you need anything explained clearer, i'd be happy to, as i'd like any help on this anyone can provide. cheers! :D

 

[slifer666]

ahhh just found something on nosing around on other threads. i can ignore question 2, about the print areas, by doing: File|Print Area|Clear Print Area.
so long as the page is set to fit 1 page wide onto the paper in print > page setup then it works fine.


any other suggestions for the other parts though? question 1 is my main problem

 

[slifer666]

ahhh and also can ignore question 4 with:
Range("B7").Select
Selection.End(xlDown).Select
Selection.Copy

 

[slifer666]

ahahaha i rock, here's question 3

ActiveWorkbook.SaveAs Filename:= _
"S:\...<directories>\MEH4601 101105.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub


i assume i'll be able to put in a variable in there? \"workpackagenumber" 101105.xls

to do this, i'd need to be able to get data from a cell, that i've selected and done Selection.Copy on, and use that as a variable within macro code. is this possible? basically i want to use the macro to select the cell, and use its contents within the same macro.

something like Selection.Paste="workpackagenumber", so (hopefully) it'd convert what i copied, into a variable. i dont think the code would be like that but thats the kind of thing im after. any thoughts, internet?


hmm. question 1 is still the main thing though.

 

[BrianB]

If I have got it right, the aim is to make a separate workbook for each set of Work Packages (?). Hopefully this should give you enough to get on with. You will need to make changes that suit you. I am not sure how you make your .pdf.

If you get stuck, you can record code for the manual process, and incorporate it.

The macro sorts, then transfers sets of packages to sheet 2 (which you can format as you wish) and uses that to make a new workbook for the set. It then clears sheet 2 and does the same again - until the end.

Sub TEST()
    Dim FromSheet As Worksheet ' sheet containing data
    Dim FromRow As Long
    Dim LastRow As Long
    Dim ToSheet As Worksheet   ' sheet for printing
    Dim ToRow As Long
    Dim WorkPackage As String
    Dim CopyRange As Range
    Dim MyDate As String
    Dim MyFileName As String
    '--------------------------------------------------
    MyDate = Format(Now, "dd-mm-yy")
    Set ToSheet = Worksheets("Sheet2")
    Set FromSheet = Worksheets("Sheet1")
    LastRow = FromSheet.Range("B65536").End(xlUp).Row
    rg = "A1:E" & LastRow
    FromSheet.Range(rg).Sort Key1:=Range("B2"), _
        Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom
    '---------------------------------------------------
    '- run through the data starting row 2
    FromRow = 2
    Do
        '- transfer set to sheet 2
        WorkPackage = FromSheet.Cells(FromRow, "B").Value
        ToSheet.Cells.ClearContents
        ' header row
        ToSheet.Range("A1:E1").Value = ToSheet.Range("A1:E1").Value
        ToRow = 2
        '- get list
        While FromSheet.Cells(FromRow, "B").Value = WorkPackage
            Set CopyRange = _
            FromSheet.Range(Cells(FromRow, "A"), Cells(FromRow, "E"))
            CopyRange.Copy Destination:=ToSheet.Cells(ToRow, "A")
            FromRow = FromRow + 1
            ToRow = ToRow + 1
        Wend
        '------------------------------------------------------------
        '-  make workbook (uses WorkPackage & date for the name)
        '- could use FromSheet.Range("A1").Value or whatever instead
        ToSheet.Copy
        MyFileName = WorkPackage & " " & MyDate & ".xls"
        ActiveWorkbook.SaveAs FileName:=MyFileName
        ActiveWorkbook.Close
    Loop While FromRow <= LastRow
    '----------------------------------------------
    '- finish
    MsgBox ("Done")
End Sub

 

[slifer666]

hmmm. thanks for that although i think i may have edited too much out of my original post for it to make sense now.

i'm making .pdf's from whats displayed on the screen. its a print driver. if i click 'print' and select adobe pdf maker, as a printer, (its installed on my pc as a printer, next to the laserprinter and stuff in windows control panel) then it pops up a box asking what to call the file and then it makes a .pdf out of whatever would have been printed.

so what i do each day is go through the autofilter, selecting all the results for each workpackage, and printing each one

 

[BrianB]

Instead of making the workbook from sheet 2 just put code to print. It will then clear ready for the next.

 

[slifer666]

hmmm, lol. your macro is good but complicated, im still trying to understand it. the sort didnt work quite right when i started playing, so im using this:

    Range("A8").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Sort Key1:=Range("B8"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

oh yea i guess i should mention. there are columns A to G with data, and 6 intial rows of header, and a 7th row of the column titles, so the data starts on row 8

thanks for the help so far :D

 

[slifer666]

hmm. i think if i get this right, i'll be able to take the raw .csv data from the database, format it, sort it, select it by workpackage and save each workpackage's items to a file named 'workpackage' 'todays date', then print each one to a .pdf file, which takes the name of the .xls its from automatically!

so the whole lot would be automated. i've still got to publish the final .pdf files onto a website but that is a different matter. this half of the job can be done automagically, yay