For Loop with headers

[StumpedAgain]

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

 

[Joel]

You lost the double quotes around startspot. The double quotes means you
have defined name in your workbook. earlier in the code your have the double
quotes, then latter you forgot to put the double quotes around startspot.

 

[StumpedAgain]

Even when I put the double quotes back in, I get the same error message. Any
other thoughts? Thanks for the help!

 

[Joel]

I rewrote the FOR loop

For Colcount = 2 To (glcount + 1)


with workbooks(CurrentCGAP)
set newsht = .Sheets.Add(after:=.sheets(.sheets.count)
end with

With workbooks("New Workbook Formatting").Worksheets("Printers")
LastRow = .Cells(6,ColCount).End(xlDown)).Rows

.Range(.cells(5,Colcount),.cells(LastRow,ColCount)).copy _
destination:=newsht.Range("A8")
Next