I'm trying to get a changing number of entries under multiple headings copied
to separate worksheets with the heading name as the worksheet name. I have
almost got it figured out, but need a little help finishing the for loop. I
am having a hard time getting 'startspot' moving over a column (and probably
will have a hard time getting 'startname' to do the same thing). My error is
listed below as the global error which means I'm probably just not
defining/using something properly. One of the "can't see the forest because
of the trees" issues. Any help is much appreciated!

Code as follows:

Dim Numpersonal As Integer, numworkteam As Integer, glcount As Integer


Windows("New Workbook Formatting").Activate
With Worksheets("Printers").Range("B5")
Numpersonal = Range(.Offset(1, 0), .End(xlDown)).Rows.Count
glcount = Range(.Offset(0, 0), .End(xlToRight)).Columns.Count
End With

Dim startspot As Range, personal As Range, startname As Range

Range("B5").Name = "startspot"
startsheet = 1
Set startname = Range("B5")

For i = 1 To glcount

'Go to correct workbook and select all entries in given column and copy
Windows("New Workbook Formatting").Activate
Sheets("Printers").Range("startspot").Select
With Worksheets("Printers").Range("startspot")
Numpersonal = Range(.Offset(1, 0), .End(xlDown)).Rows.Count
End With

Set personal = Range("startspot")
personal.Offset(1).Resize(Numpersonal, 1).Select
Selection.Copy

'Paste to new worksheet in CurrentCGAP and rename worksheet
Windows(CurrentCGAP).Activate
Sheets.Add
Range("A8").Select
ActiveSheet.Paste
ActiveSheet.Name = startname

'Move to next column and worksheet
Range(startspot) = Range(startspot).Offset(0, 1) 'error: Method
range of object failed global
Range(startname) = Range(startname).Offset(0, 1)

With ActiveSheet
.Move After:=Worksheets(Worksheets.Count)
End With

Next