Snasui Excel Forum

Code: Select all

Worksheets("Sheet2").Rows(1 & ":" & Worksheets("Sheet2").Rows.Count).Delete

Code: Select all

Cells(ActiveCell.SpecialCells(xlLastCell).Row, 1).Select
Sheets("Sheet2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Code: Select all

Dim strName As String
Dim sheet As Worksheet
Dim wb As Workbook, nb As Workbook
Dim lstRow As Double

Set wb = ThisWorkbook

Application.DisplayAlerts = False

wb.Worksheets("Sheet2").UsedRange.Delete
file_path = "C:\sheetexcel\"
strName = dir(file_path & "\" & "*.xl??")

Do While strName <> vbaNullString
    If strName <> "" Then
        lstRow = wb.Worksheets("Sheet2").UsedRange.Rows.Count + 1
        Set nb = Workbooks.Open(fileName:=file_path & "\" & strName)
        nb.ActiveSheet.UsedRange.Copy
        wb.Worksheets("Sheet2").Range("a" & lstRow).PasteSpecial xlPasteValues
        nb.Close
    End If
    strName = dir
Loop

Application.CutCopyMode = False
Application.DisplayAlerts = True

wb.Sheets("Sheet1").Activate