Results 1 to 4 of 4

Combine Sheets to 1 result sheet

Threaded View

  1. #1
    Forum Contributor
    Join Date
    11-14-2012
    Location
    Kentucky
    MS-Off Ver
    Office 16
    Posts
    112

    Combine Sheets to 1 result sheet

    My macro works completely fine the only problem is I can only get it to copy all sheets on my current workbook. I would like to be able to define which sheets exactly to copy from. Here is the current macro.

    Sub All_Sheets_To_One()
    
    
        Dim i As Integer
        Dim j As Long
        Dim SheetCnt As Integer
        Dim lstRow1 As Long
        Dim lstRow2 As Long
        Dim lstCol As Integer
        Dim ws1 As Worksheet
        
        With Application
            .DisplayAlerts = False
            .EnableEvents = False
            .ScreenUpdating = False
        End With
    
        On Error Resume Next
    
       
        Sheets("result").Activate
        range("A1:A10000").Select
        Selection.ClearContents
           range("B1:B10000").Select
        Selection.ClearContents
            range("C1:C10000").Select
        Selection.ClearContents
            range("D1:D10000").Select
        Selection.ClearContents
            range("E1:E10000").Select
        Selection.ClearContents
            range("F1:F10000").Select
        Selection.ClearContents
            range("G1:G10000").Select
        Selection.ClearContents
            range("H1:H10000").Select
        Selection.ClearContents
            range("I1:I10000").Select
        Selection.ClearContents
            range("J1:J10000").Select
        Selection.ClearContents
    
        SheetCnt = Worksheets.Count
        
    
    
        Sheets.Add (dashboard)
        ActiveSheet.Name = "result"
        Set ws1 = Sheets("result")
        lstRow2 = 1
    
        j = 1
    
    
        For i = 1 To SheetCnt
            Worksheets(i).Select
    
       
            lstCol = ActiveSheet.Cells(1, ActiveSheet.Columns.Count).End(xlToLeft).Column
    
    
            lstRow1 = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
    
    
            range("A" & j, Cells(lstRow1, lstCol)).Select
    
    
            Selection.Copy
            ws1.range("A" & lstRow2).PasteSpecial
            Application.CutCopyMode = False
    
    
            lstRow2 = ws1.Cells(65536, "A").End(xlUp).Row + 1
    
    
            j = 2
        Next
    
        With Application
            .DisplayAlerts = True
            .EnableEvents = True
            .ScreenUpdating = True
        End With
    
        Sheets("result").Select
        Cells.EntireColumn.AutoFit
        range("A1").Select
        
        MsgBox ("DONE")
    
    End Sub
    Last edited by arlu1201; 01-16-2013 at 07:15 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1