Dynamic Macro's

**Myrlin1** · 05-02-2007, 09:42 PM

I've kind of run into a dilemna. I work for a large hardware retail company as a programmer and PC tech. I've recently built a program that will allow salesman to build a list of items. It will then take the data, add more information to it and export it to excel.
Well, the problem is this: The worksheets are based around the aisle's in the store. People are picky and don't want to print out seperate sheets for each aisle.
I've basically been trying to learn VBA for Excel so I can write a macro that will run a sort on a specific column (sorting all columns in order) on each sheet. That alone would do the trick, but I would also like to know if there's any way to compile all the data that was sorted to the top of each sheet to an individual sheet.
Since my knowledge of Excel VBA is almost null, and I really don't want to have to go back and tear apart 980 lines of code, I would greatly appreciate any tips or pointers anybody has to offer.

**mudraker** · 05-02-2007, 09:53 PM

Without see specifics of your book I can only supply general type answers

Each sheet can be sorted by aisle number or anyother column - try recording a macro whist you sort a sheet.

Compiling a single sheet is possible using copy/paste & a few other methods

In your message you refered to

data that was sorted to the top of each sheet

Does this mean each sheet has several different sections of which one area is sorted & the other area is not.

**Myrlin1** · 05-04-2007, 08:25 PM

I actually found a way to compile the entire list on one sheet. What I did was create a master sheet, and for every new row that is created on each aisle sheet, it makes a pointer to there respective cell in the master sheet. I have attached a text document containing an example of one of these workbooks. If you change the extension to xml or xls and open with excel, you will see the end result I was looking for. I had to shorten it considerably, because of the file size limitation on this forum.

Being able to do this has solved the majority of my problem. As far as I'm concerned, our users can sort there own work. But I am still curious to know whether or not Excel macro's can use dynamic variables to represent specific items. This would be an extremely useful tool, as dynamics are a key part of data processing.

**Myrlin1** · 05-07-2007, 04:39 PM

In case anybody is interested. I have found a way to work with dynamic variables based on content on a worksheet.

Sub SORT_BY_QTY_ADJ()
'
' Macro Created 5/4/2007 by Jeremy Albright
'

'
    Dim strColumnRange As String
    Dim numCurrentCell As Single
    Dim rngCurrentCell As Range
    Dim rngNextCell As Range
    Dim rngAllRows As Range
    Dim strAllRows As String
    Dim strStartCell As String
    strColumnRange = "G3"
    ActiveSheet.Unprotect
    Set rngCurrentCell = Worksheets("Master").Range(strColumnRange)
    Do While Not IsEmpty(rngCurrentCell)
        Set rngNextCell = rngCurrentCell.Offset(1, 0)
        If rngCurrentCell.Value = "0" Then
            rngCurrentCell.EntireRow.Delete Shift:=xlUp
        End If
        Set rngCurrentCell = rngNextCell
    Loop
    Columns("G:G").Select
    Range("G2").Activate
    strColumnRange = "S3"
    numCurrentCell = "3"
    strStartCell = "A3"
    Set rngCurrentCell = Worksheets("Master").Range(strColumnRange)
    Do While Not IsEmpty(rngCurrentCell)
        Set rngCurrentCell = rngCurrentCell.Offset(1, 0)
        numCurrentCell = numCurrentCell + 1
    Loop
    strAllRows = strStartCell & ":S" & numCurrentCell
    Set rngAllRows = Worksheets("Master").Range(strAllRows)
    rngAllRows.Sort Key1:=Range("G3"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    ActiveSheet.PageSetup.PrintArea = "$A:$G"
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

EDIT: Code wrapped by Simon Lloyd - only due to newbie status