Membership Membership:
Latest New User Latest: vpros
Past 24 Hours Past 24 Hours: 0
Prev. 24 Hours Prev. 24 Hours: 0
User Count Overall: 137

People Online People Online:
Visitors Visitors: 5
Members Members: 0
Total Total: 5

NakedMCSE Computer Tech Support Forums

So you have passed all of the exams and you are now out in the real world of windows servers, linux servers and freebsd servers faced with a tech support problem you have never seen before...

Find free online tech support in the forums below, respecting that advice is free, but work beyond advice is not...

The General forum is provided for off topic discussion.

You need a user account to post - please Register or Login.

Online Tech Support Forums
  Forum  Discussions  General  Excel VBA - Creating unique sheets from a range and copying a variable size range
Previous Previous
Next Next
New Post 5/20/2011 4:37 PM
119 posts
10th Level Poster

Excel VBA - Creating unique sheets from a range and copying a variable size range 

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
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.


Previous Previous
Next Next
  Forum  Discussions  General  Excel VBA - Creating unique sheets from a range and copying a variable size range
Send me One Million FREE Guaranteed Visitors