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
Bookmarks