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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 10:48 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com