Lairds Computer Services

I recently had to write some Excel VBA code to split out some raw data into multiple sheets with just the data for that sheet copied to it.

This is a little harder than it sounds.  The sheet I was copying from was called "Raw Data" and the first column contained the keys that I wanted to use to create the unique sheets.

'Select the raw data worksheet
Set rawsheet = Worksheets("Raw Data")

'Select the originating numbers
Set copyfromraw = rawsheet.Range("A2", Range("A65535").End(xlUp))

'Create a unique list of numbers @ column X
rawsheet.Range("X1").Delete
copyfromraw.AdvancedFilter xlFilterCopy, , rawsheet.Range("X1"), True

'Select the unique names
Set sheetnames = rawsheet.Range("X2", Range("X65535").End(xlUp))

'Select the raw data
Set copyfromraw = rawsheet.Range("A1", "I" & CStr(rawsheet.Range("A65535").End(xlUp).Row))

'Cycle through uniques and copy data to respective sheets
For Each curcell In sheetnames
    strtext = curcell
    'Create a new worksheet
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = strtext
    'Copy in raw data, using autofilter to select just the matching records
    copyfromraw.AutoFilter 1, strtext
    copyfromraw.Copy Worksheets(strtext).Range("J9")
Next curcell

Notice how when selecting the data we set the size of the range based on the A column -

rawsheet.Range("A1", "I" & CStr(rawsheet.Range("A65535").End(xlUp).Row))

This uses concatenation and CStr to take the row number of the last entry in the A column and use it to create the end of the range on the I column.