Consulting

Results 1 to 3 of 3

Thread: looping from one worksheet to the next fails

  1. #1
    VBAX Regular
    Joined
    Dec 2019
    Posts
    10
    Location

    looping from one worksheet to the next fails

    Hi All,

    first attempt at looping.. been working with online searches and got very confused.

    Basically need to copy a range in Sheet(1) then paste special formats to same range in Sheet(2) before repeating the same for Sheet(3)

    Got a feeling am missing something near top of code and the Sheets(2) line of code may be causing code not to loop to Sheet(3)

    Option Explicit
    Sub CopyPasteFormats()
    '
    ' CopyPasteFormats Macro
    '
        Dim WS_Count As Integer
        Dim I As Integer
        WS_Count = Worksheets.Count
        
        For I = 2 To 3
        
        Sheets(1).Activate
        Range("H5").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        
        Sheets(2).Select
        Range("H5").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
        
    Next I
        
    End Sub
    going to use this as one of my reference examples as build my knowledge so any advice massively appreciated as i will not then get confused by too many methodologies.

    Thank you.

  2. #2
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,873
    Location
    1. You defined the For/Next loop nicely, but never used it

    2. Usually no need to select or activate in order to use

    3. Some unneeded variables

    4. I used a variable rCopy to hold the source

    Option Explicit
    
    
    
    
    Sub CopyPasteFormats()
    
    
        Dim i As Long                   '   Long is better
        Dim rCopy As Range
        
        Set rCopy = Sheets(1).Range(Sheets(1).Range("H5"), Sheets(1).Range("H5").End(xlDown))
        
        'no need to select of activate
        For i = 2 To 3
    
    
            'this is always 1 (one) from the Set rCopy above
            rCopy.Copy
        
        'this is the loop variable
            Sheets(i).Range("H5").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
                SkipBlanks:=False, Transpose:=False
    
    
        Next i
    End Sub
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    VBAX Regular
    Joined
    Dec 2019
    Posts
    10
    Location
    Huge thank you Paul_Hossler!

Posting Permissions

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