HELP ! - Macro code to copy sheet name X number of times bas
Find bottlenecks in your Excel workbooks
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

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

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    357
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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"

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    357
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com