remove sheets and copy content

[vics_roo]

Hello

I need to process some files like that.
Need to create new sheet for each file.
Open file and copy content to new created sheet.
Previous sheets must be delete.

1. For delete sheets (all except 1) I using loop with Worksheets(i).Delete. But appearing message "This sheet hold data. Do you want to delete sheet?" How delete sheets quite ?

2. Copy content file to new sheet not working.
The error is
Run-time error '438':
Object doesn't support this property of method


Here is code with comments

Private Sub main()

    Application.ScreenUpdating = False
   
    'get list of files
    Dim dirName As String
    dirName = getDirName()
    Dim fileNameList As Collection
    Set fileNameList = getFileNames(dirName, "dbf")
    Dim N As Long
    N = fileNameList.Count

    'vars
    Dim lastRow As Long
    Dim lastColumn As Long
    Dim lastColumnName As String
    Dim i As Long
    Dim tmpFileName As String
    Dim tmpWB As Workbook
    Dim wb As Workbook
    Dim newWS As Worksheet
    Set wb = ActiveWorkbook
    
''''''''''''''''''''''This for question one'''''''''''''''''''
    'remove old sheets except the first
    Worksheets(1).Activate
    For i = Worksheets.Count To 2 Step -1
        Worksheets(i).Delete
    Next i
''''''''''''''''''''''End for question one'''''''''''''''''''
    


    'loop for files
    For i = 1 To N
        Application.StatusBar = "Processing " & fileNameList.Item(i)
        tmpFileName = dirName & "\" & fileNameList.Item(i)

        Workbooks.Open tmpFileName, UpdateLinks:=0
        Set tmpWB = ActiveWorkbook
        
        lastRow = getLastRow()
        lastColumn = getLastColumn()
        lastColumnName = getColumnName(lastColumn)
        
        'create sheet and set name as filename
        Set newWS = wb.Worksheets.Add(After:=Worksheets(Worksheets.Count))
        newWS.Name = fileNameList.Item(i)
        tmpWB.Activate
        ''''''''''''''''''''''This for question two'''''''''''''''''''
        tmpWB.ActiveSheet.Range("A2:" & lastColumnName & lastRow).Copy wb.newWS.Range("A" & 3)   'Here is error
        ''''''''''''''''''''''End for question two'''''''''''''''''''
        
        'close file without saving
        ActiveWorkbook.Close savechanges:=False
    Next
  
    'unfreeze
    Application.ScreenUpdating = False
    MsgBox " Files processed"
    Shell """" & "explorer.exe" & """ """ & dirName & """", vbMaximizedFocus
End Sub

Thanks at advance

 

[mohadin]

Hi
Try

''''''''''''''''''''''This for question one'''''''''''''''''''
    'remove old sheets except the first
    Worksheets(1).Activate
    Application.DisplayAlerts = False '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
    For i = Worksheets.Count To 2 Step -1
        Worksheets(i).Delete
    Next i
     Application.DisplayAlerts = False '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
''''''''''''''''''''''End for question one'''''''''''''''''''

 ''''''''''''''''''''''This for question two'''''''''''''''''''
        tmpWB.ActiveSheet.Cells(llastColumnName, lastRow).Copy wb.newWS.Range("A" & 3)  'Range("A2:" & lastColumnName & lastRow).Copy wb.newWS.Range("A" & 3)   'Here is error
        ''''''''''''''''''''''End for question two'''''''''''''''''''