Getting Error - Method Range of Object - Worksheet Failed - Help!

[missit]

Hi,

I have some code that worked well in the past, but am trying to automate it a little more. The code basically copies a template, names the new worksheet from a list and populates a cell (A6) on the new worksheet.
[Code]
Sub Create_Sheets()
'copy existing sheet and create new sheets
Application.DisplayAlerts = False 'stops error message regarding duplicate range names


Dim LR As Long, i As Long



Dim wks As Worksheet
Dim wksp As Worksheet

Set wks = Sheets("Group")
Set wksp = Sheets("Providers")

With wks
LR = wksp.Range("A").Offset(wksp.Rows.Count - 2, 0).End(xlUp) 'looks at the list of names on Providers and counts number of sheets to create

For i = 1 To LR


Sheets("TEMP").Copy After:=Sheets("Group") '1st sheet is sheet to be copied, 2nd sheet is placement of all new sheets
ActiveSheet.Name = Left(wks.Cells(i, 1), 31) ' names worksheet using the first 31 characters from the left
ActiveSheet.Cells(6, 1) = wks.Cells(i, 1) ' enters names of new sheets in A6

Next

End With

Application.DisplayAlerts = True ' turns error message display back on

End Sub
[Code]
The line that it stops on is the LR= line? I used to have (For i = 1 To 3 ) here, but I am creating this worksheet for another person who does not know anything about coding and I want it to automatically determine the number of sheets to create from the list (Providers).


Any help would be greatly appreciated.

Carole

[AB33]

Please use code tags with your code.

This should work in any range as long the sheet is blank

Please Login or Register  to view this content.

[AB33]

Please Login or Register  to view this content.

Looks at the last non empty row and returns a long (Number of rows)

[missit]

hi,

Thanks so much for the code. It woks perfectly. I posted another reply, but please disregard as it was the following code that messed up the data it was pulling.

You are the BEST!!!!.

Carole

[missit]

Hi,

I was wondering what I would need to change to allow other things to be on the Provider's Worksheet. I would like to put a couple of Macro Buttons and instructions on how to use the worksheet. The data that needs to be used in the Code would always be in column A.

Thanks

Carole

[AB33]

Carole,
You can create a macro button, but without attaching the button to a code is not much point.
The code is stored on the module, so instead of users need to go to the module to run a code, they can use a button which can be in any sheet. When the person presses the button, it runs the code without going in the module.
The easiest way to do is to have all your codes on the module first. Then on a separate excel sheet, you can create these buttons, you then attach them to each code. You can also include an explanation next to the button which explains what the button does.

To create a button, you do not need a code, just go to excel-developer-choose insert-choose one from controls- draw a button. Once the button is created, you then right click on the button, then choose assign(Attach) a code. The window will show you which code you want to attach to a button.
Play around first creating a button.

[missit]

Hi,

You misunderstood what I was asking. I know how to create the buttons and attach them to the code that is in the modules, but if I put the buttons on the Providers worksheet, then the code you gave me trys to pull all of the data in those rows in. I want to limit the code to just column A.

Does that make sense?
[code]
LR = wksp.Cells.Find("*", , , , xlByRows, xlPrevious).Row
[code]

thanks Carole

[AB33]

Carole,

"the code you gave me trys to pull all of the data in those rows in"
I have not provided you any code. All I did was corrected one of the lines.

Please Login or Register  to view this content.

This line does not return any value, it just looks at the last non-empty cell.

For i = 1 To LR

Is looping from row 1 to the last row in your data.
The LR may be 5,10,100,10000, it depends on the last raw in your data.
If you want to restrict your loop, you need to do
Either start it from different row.
let's say row 10.

Or,
When you loop down, instead of looping to unknown length, you can put in a fixed end.
Let's say from row 1 to 100
I= 1 to 100
We removed LR, as the last row may go below 100, but you only want to loop down to 100.
To sum up: The function of LR is to find a number(long), does not have any other function.

[missit]

Thanks, got it.

Carole