HELP ! - Macro code to copy sheet name X number of times bas

[Cosmos75]

I need help with fine-tuning my macro. I have a macro that looks for lists on several worksheets that vary in length and I have that part done. I want to add next to those lists the names of the sheets they came from. Here's that part of the code that I cant get to work.


For Each ws In Worksheets
If ws.Name <> "Summary" Then
If ws.Name <> "Conversion Factors" Then
If ws.Name <> "HI" Then

upperconstituent = ws.Range("B65536").End(xlUp).End(xlUp).Offset(1, 0).Address
lowerconstituent = ws.Range("B65536").End(xlUp).Address

ws.Select
ws.Range(upperconstituent, lowerconstituent).Select
RowCounter = Selection.Count
For i = 1 To RowCounter
Sheets("HI").Select
Range("A65536").End(xlUp).Select
CoatingCounter = Selection.Count
Range(Range("A65536").End(xlUp).Address, Range("A", CoatingCounter + i)).Value = ws.Name
Next i

End If
End If
End If


Next ws

I need help with thhis part of the code

Range("A", CoatingCounter + i)).

I get an error message "Method 'Range' of Object '_Global' Failed"

[Cosmos75]

Fixed it!

Dim RowCounter As Integer
Dim CoatingCounter As Integer

'Insert Coating Name
ws.Select
ws.Range(upperconstituent, lowerconstituent).Select
RowCounter = Selection.Count
Sheets("HI").Select
Range(Range("A65536").End(xlUp).Offset(1, 0).Address, Range("A65536").End(xlUp).Offset(RowCounter, 0).Address).Value = ws.Name