Naming fields loop getting 1004 error

[devanrose33]

Hi,

I have a loop inside a Sub that copies and pastes values and cell names into a new workbook. Randomly, the loop stopped working. I haven't adjusted this code or any of the cells that it's looking at, so it's very strange. It's causing the Object error 1004. The code is below and I've highlighted the line that is causing the problem.

With Application

.ScreenUpdating = False​

.DisplayAlerts = False​

.EnableEvents = False​

.Calculation = xlCalculationManual​

End With

Set wb = Application.Workbooks.Add(1)
Set thisWB = ThisWorkbook

With thisWB.Sheets("Input_Main") 'Copying Row B from Main Input and Pasting into new Excel workbook

wb.Sheets(1).Columns(1).NumberFormat = "@"​

For i = 5 To INPUTEnd + 5 'INPUTEnd = 366​

.Range("B" & i).Copy​

wb.Sheets(1).Range("A" & count).PasteSpecial xlValues '***​

If IsRangeName(.Range("B" & i).Address) Then 'If named range then copy the range name to export file​

wb.Sheets(1).Range("A" & count).Name = .Range("B" & i).Name.Name & "_tmp"​

count = count + 1​

End If​

Next i​

End with

Any suggestions?

Thanks in advance!

 

[JLGWhiz]

You might need to use the Names.Add method instead.

 

[devanrose33]

When doing the .Names.Add method, can you have a name that varies with a loop?

If I substitute the red line above with:
wb.Names.Add Name:=.Range("B" & i).Name.Name & "_tmp", RefersTo:=wb.Range("A" & count)
I've also tried
wb.Names.Add Name:= thisWB.Sheets("Input_Main").Range("B" & i).Name.Name & "_tmp", RefersTo:=wb.Sheets(1).Range("A" & count)
I'm still getting an object error.

It's strange because I have this code underneath what I have pasted above:

For i = 0 To 16
If i <= 8 Then
On Error Resume Next
wb.Sheets(1).Range("A" & count + i).Name = .Range("D" & 8 + i).Name.Name & "_tmp"
Else
wb.Sheets(1).Range("A" & count + i).Name = .Range("E" & i - 1).Name.Name & "_tmp"
End If
Next i

And it works fine.

Oh and btw, I forgot to write on the code above that count = 1 but I'm sure you could assume something along those lines.

 

[devanrose33]

Nevermind, the lower piece of code doesn't work either after removing On Error Resume Next. But it is strange that it has always worked until now. It's a huge excel file with many modules/procedures. I've made changes elsewhere in the tool and added a new tab. I'm not sure which update could have altered this piece to cause errors.