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"
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"