Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Loop with headers
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Loop with headers
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Loop with headers
Even when I put the double quotes back in, I get the same error message. Any
other thoughts? Thanks for the help! "Joel" wrote: 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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Loop with headers
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 "StumpedAgain" wrote: Even when I put the double quotes back in, I get the same error message. Any other thoughts? Thanks for the help! "Joel" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup using column headers and row headers | Excel Discussion (Misc queries) | |||
returning back to loop check condition without completing the loop | Excel Programming | |||
Hide Column Headers but not row (Headers) | Excel Programming | |||
Loop through column headers to search from column name and get cell range | Excel Programming | |||
Advancing outer Loop Based on criteria of inner loop | Excel Programming |