Offset in for loop not working

[StumpedAgain]

I am having a hard time coppying multiple columns with unknown lengths to
different worksheets and then naming the worksheets with the column headers.
I keep getting the global error when I try to offset the "startspot" or
"startname" to move to the next column. There has to be an easier way or I'm
missing something. Any suggestions?

My current code is as follows:

Dim startspot As Range, startsheet As Integer, personal As Range, startname
As Range

'Range("B5").Name = "startspot"
startsheet = 1
Set startname = Range("B5")
Set startspot = 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
startsheet = startsheet + 1
Range(startname) = Range(startname).Offset(0, 1) 'error: Method
range of object failed global


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

Next

 

[Rick Rothstein \(MVP - VB\)]

'Move to next column and worksheet

Range(startspot) = Range(startspot).Offset(0, 1) 'error: Method
range of object failed global
startsheet = startsheet + 1
Range(startname) = Range(startname).Offset(0, 1) 'error: Method
range of object failed global

Shouldn't you have quote marks around 'startname' in those Range statements?

Rick

 

[StumpedAgain]

Even when altered to look like the following (with quotes), it gives the same
error. I've tried defining the variable name different ways, using quotes,
no quotes, etc. I think I'm not using the variable name in the correct
capacity but I don't know how I should be doing this. Thanks for the help!

Range("startspot") = Range("startspot").Offset(0, 1) 'error: Method
range of object failed global
startsheet = startsheet + 1
Range("startname") = Range("startname").Offset(0, 1)

 

[atpgroups]

        Range(startspot) = Range(startspot).Offset(0, 1) 'error:Method
range of object failed global
        startsheet = startsheet + 1
        Range(startname) = Range(startname).Offset(0, 1) 'error:Method
range of object failed global

I see the problem, I think. startspot is an object, (a range). If you
assign to it with "let" (which is implicit in VB) then you set the
default property. What you want to do is redefine it.
Compounding this you have superflous "Range" functions.

Try:
Set startspot = startspot.Offset(0, 1)
startsheet = startsheet + 1
Set startname = startname.Offset(0, 1)

However, the whole thing can probably be a lot simpler. Try this. (but
be aware I am using Google groups, and it word-wraps annoyingly)

Dim rngName As Range
Dim wsNew As Worksheet

For Each rngName In Sheets("Printers").Range("B5",
wsNew.Range("B5").End(xlToRight))
Set wsNew = Sheets.Add(after:=Sheets.count)
wsNew.Name = rngName.Value
Range(rngName.Offset(1, 0), rngName.End(xlDown)).Copy
wsNew.Range("A8").PasteSpecial
Next

 

[atpgroups]

Dim rngName As Range
Dim wsNew As Worksheet

For Each rngName In Sheets("Printers").Range("B5",
wsNew.Range("B5").End(xlToRight))
  Set wsNew = Sheets.Add(after:=Sheets.count)
  wsNew.Name = rngName.Value
  Range(rngName.Offset(1, 0), rngName.End(xlDown)).Copy
  wsNew.Range("A8").PasteSpecial
Next

Not only did it word-wrap badly, but I forgot a bit too.
The "for" line needs to be..

For Each rngName In Sheets("Printers").Range("B5", _
wsNew.Range("B5").End(xlToRight)).Cells