Results 1 to 2 of 2

Thread: copy all sheets

  1. #1
    Seeker ACCA's Avatar
    Join Date
    Feb 2017
    Posts
    19
    Articles
    0
    Excel Version
    excel 2007

    copy all sheets



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I want to copy all data from other sheets to active sheet , all data are arranged in Column A & B, but range are different in each each sheet i.e in "sheet 1" data range from "A2:B200" and in "sheet 2" data range from "A2:B400".

  2. #2
    Conjurer Logit's Avatar
    Join Date
    Nov 2016
    Posts
    252
    Articles
    0
    Excel Version
    2007
    .
    Code:
    
    
    Sub CpyAllSheets()
    
    
    Dim WS1, WScur As Worksheet
    Dim i As Long
    Dim lrow1, lrow As Variant
    
    
    Set WS1 = Sheets("Sheet1")
    
    
    WS1.Cells.ClearContents     '<----- this clears Sheet1 before pasting new data from other sheets.
    
    
    For i = 2 To ActiveWorkbook.Worksheets.Count
            lrow1 = WS1.Cells(Rows.Count, 1).End(xlUp).Row + 1
            Set WScur = Sheets(i)
            lrow = WScur.Cells(Rows.Count, 1).End(xlUp).Row
            
            WScur.Range("A2:Z" & lrow).Copy WS1.Range("A" & lrow1) '<--- change range "A2:Z" as required
            
            
        
    Next i
    
    
    End Sub

Posting Permissions

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