Run Time Errors - Code not working

Hi

Can someone advise me on how to fix the code below

I have hundreds of different rows to export into different individual tabs. When I run this code I get
Run-time Error ‘1004’
Cannot rename a sheet to the same name as another sheet, a referenced object library or a workbook referenced by visual basic

Additionally, the code is always generating tabs inside the two workbooks – even though this there are 2 options to not to this – it always happens!!! It is also not picking up the corresponding cells.

Any help is most grateful

-------------

Sub FillOutTemplate()

Dim LastRw As Long, Rw As Long, Cnt As Long
Dim dSht As Worksheet, tSht As Worksheet
Dim MakeBooks As Boolean, SavePath As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set dSht = Sheets("Sales")
Set tSht = Sheets("Template")

'Option to create separate workbooks
MakeBooks = MsgBox("Create separate workbooks?" & vbLf & vbLf & _
"YES = Template will be copied to separate workbooks" & vbLf & _
"NO = Template will be copied to sheets within this workbook", _
vbYesNo + vbQuestion) = vbYes


If MakeBooks Then
MsgBox "Please select a destination for the new workbooks"
Do
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
.Show
If .SelectedItems.Count > 0 Then
SavePath = .SelectedItems(1) & "\"
Exit Do
Else
If MsgBox("Do you wish to abort?", _
vbYesNo + vbQuestion) = vbYes Then Exit Sub
End If
End With
Loop
End If

LastRw = dSht.Range("A" & Rows.Count).End(xlUp).Row

For Rw = 3 To LastRw
tSht.Copy After:=Worksheets(Worksheets.Count)
With ActiveSheet

.Name = dSht.Range("F3" & Rw)
.Range("A3").Value = dSht.Range("D5" & Rw).Value
.Range("B3").Value = dSht.Range("D7 & Rw").Value
.Range("C3").Value = dSht.Range("D9 & Rw").Value
.Range("D3").Value = dSht.Range("D11 & Rw").Value
.Range("E3").Value = dSht.Range("D13 & Rw").Value
.Range("F3").Value = dSht.Range("D15 & Rw").Value
.Range("G3").Value = dSht.Range("D19 & Rw").Value
.Range("H3").Value = dSht.Range("C19 & Rw").Value
.Range("I3").Value = dSht.Range("C20 & Rw").Value
.Range("J3").Value = dSht.Range("C21 & Rw").Value
.Range("K3").Value = dSht.Range("C22 & Rw").Value
.Range("L3").Value = dSht.Range("C23 & Rw").Value
.Range("M3").Value = dSht.Range("D18 & Rw").Value
.Range("N3").Value = dSht.Range("D19 & Rw").Value
.Range("O3").Value = dSht.Range("D20 & Rw").Value
.Range("P3").Value = dSht.Range("D21 & Rw").Value
.Range("Q3").Value = dSht.Range("D22 & Rw").Value
.Range("R3").Value = dSht.Range("D23 & Rw").Value
.Range("S3").Value = dSht.Range("D25 & Rw").Value
.Range("T3").Value = dSht.Range("D27 & Rw").Value
.Range("U3").Value = dSht.Range("D29 & Rw").Value
.Range("V3").Value = dSht.Range("D31 & Rw").Value
.Range("W3").Value = dSht.Range("D33 & Rw").Value
.Range("X3").Value = dSht.Range("D35 & Rw").Value
.Range("Y3").Value = dSht.Range("D37 & Rw ").Value
.Range("Z3").Value = dSht.Range("D39").Value
.Range("AA3").Value = dSht.Range("D41").Value
.Range("AB3").Value = dSht.Range("D43").Value
.Range("AC3").Value = dSht.Range("D45").Value
.Range("AD3").Value = dSht.Range("D47").Value
.Range("AE3").Value = dSht.Range("D49").Value
.Range("AF3").Value = dSht.Range("D51").Value
.Range("AG3").Value = dSht.Range("D53").Value
.Range("AH3").Value = dSht.Range("D55").Value
.Range("AI3").Value = dSht.Range("D58").Value
.Range("AJ3").Value = dSht.Range("D60").Value
.Range("AK3").Value = dSht.Range("D62").Value
End With


If MakeBooks Then
ActiveSheet.Move
ActiveWorkbook.SaveAs SavePath & Range("F3").Value, xlNormal
ActiveWorkbook.Close False
End If
Cnt = Cnt + 1
Next Rw

dSht.Activate
If MakeBooks Then
MsgBox "Workbooks created: " & Cnt
Else
MsgBox "Worksheets created: " & Cnt
End If

Application.ScreenUpdating = True
End Sub

Sorry - see below

Please Login or Register  to view this content.

The error is highlighting

Please Login or Register  to view this content.

Hi

Basically what I was trying to do with

Please Login or Register  to view this content.

Was to createthe output sheet name as the cell value on the master sheet. Is this wrong?

No, as long as you do not have a sheet name with the cell value of dSht.Range("F3" & Rw). Even if you already have, you should get an error on that line, which says along the line "You can not create a sheet".

Does the new sheet created have a name which is dSht.Range("F3" & Rw)?
What is dSht.Range("F3" & Rw)?
If all these assumptions are correct, you should not get an error on this line

Please Login or Register  to view this content.

.

The easiest way to pin down the error is: To step over the code using F8 and see where the code errors.

Hi The code errors

Please Login or Register  to view this content.

What is

Please Login or Register  to view this content.

this is what I want to call my new workbook. I'm really stumped here!!!

Sorry!
I did not spot this line was wrong

Please Login or Register  to view this content.

It should be

Please Login or Register  to view this content.

The code is now error at

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Where am I going wrong?!

Sophia,
Did you also try
.Name = dSht.Range("F3" & Rw)
?
If so, did you get an error?

It would be easier to see the error if you could attach the sample please.

When you say the code errors, what does the error say?

Hi - that didnt work

Originally Posted by pareshj

Hi,

Try this:

ActiveSheet..Name = dSht.Range("F3").value

And remove With Activesheet from above line.


Regads,
Paresh J

Hi it says

Run-time error '1004':

Application- defined or object -defined error

Hi I removed the double dot

Originally Posted by pareshj

Sophia,

Please remove the double dot.

ActiveSheet..Name replace with ActiveSheet.Name

No

Should I replace End With with something?

Originally Posted by pareshj

Hi,

Remove End With too..

Still the error is highlighting for

ActiveSheet.Range("A3").Value = dSht.Range("D5").Value

Input_ Output.xlsx

HI

Its very classified - but I have made a mock one which follwos the same lines

I have now tested the code and see why the code errors.
This line is wrong

Please Login or Register  to view this content.

Should be

Please Login or Register  to view this content.

Saying row 3 column F.
You will get another error.
If the column F and what ever the row number is blank, that if F3, F4 cells are blanks.
You will also get another error called " You can not use the same sheet name again if you try to use the same cell value to create a sheet name.

HI

Many thanks

However - its now saying "Invalid use of property as the error".
This is my new code

Please Login or Register  to view this content.

Sorry!
It was my mistake!
I think you copied the line from post#29
Meant to be

Please Login or Register  to view this content.

Thanks AB33 - the error is still coming up for

Please Login or Register  to view this content.

Even with the above.

Does anyone have a simpler code?.

Please try the attached.
I did make the sample myself.
A simple code? It is your own code.