Clearing the data from mutiple workbooks using VBA

[NixLouise]

Hello, I am brand new to VBA and I have some code to transfer data from multiple spreadsheets into one master spread sheet. This works ok but at the end I need to clear the days data from each of the spreadsheets so the data can be transferred again the next day with out duplicating. Is there something simple I can add to the end of my script to clear all the data I have just transferred? I know this should be easy but I just can get over this last hurdle. Any help would be very greatly appreciated
Here is my script so far:

Sub LoopThroughDirectory()

Dim MyFile As String
Dim erow
Dim Filepath As String

Filepath = "C:\Users\Guy\Desktop\nichola\"
MyFile = Dir(Filepath)
Do While Len(MyFile) > 0
If MyFile = "ZMASTER.xlsm" Then
Exit Sub
End If

Workbooks.Open (Filepath & MyFile)
Range("A2:E25").Copy
ActiveWorkbook.Close

erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, 5))

MyFile = Dir
Loop
End Sub

 

[My Aswer Is This]

Try this:

Range("A2:E25").ClearContents

 

[NixLouise]

Hello, I tried this under the range I already have and it comes up with Run-time error '1004' paste method worksheet class failed

 

[JoeMo]

Hello, I tried this under the range I already have and it comes up with Run-time error '1004' paste method worksheet class failed

You can't clear the copied range before you paste those contents. You can try this (untested):

Sub LoopThroughDirectory()
Dim myBk As Workbook
Dim MyFile As String
Dim erow
Dim Filepath As String
Set myBk = ThisWorkbook
Filepath = "C:\Users\Guy\Desktop\nichola\"
MyFile = Dir(Filepath)
Do While Len(MyFile) > 0
    If MyFile = "ZMASTER.xlsm" Then
    Exit Sub
    End If
    
    Workbooks.Open (Filepath & MyFile)
    With Range("A2:E25")
        .Copy
        With myBk.ActiveSheet
            erow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
            .Paste Destination:=.Cells(erow, 1)
        End With
        .ClearContents
    End With
    ActiveWorkbook.Close savechanges:= True
    MyFile = Dir
Loop
End Sub

 

[NixLouise]

You can't clear the copied range before you paste those contents. You can try this (untested):

Sub LoopThroughDirectory()
Dim myBk As Workbook
Dim MyFile As String
Dim erow
Dim Filepath As String
Set myBk = ThisWorkbook
Filepath = "C:\Users\Guy\Desktop\nichola\"
MyFile = Dir(Filepath)
Do While Len(MyFile) > 0
    If MyFile = "ZMASTER.xlsm" Then
    Exit Sub
    End If
    
    Workbooks.Open (Filepath & MyFile)
    With Range("A2:E25")
        .Copy
        With myBk.ActiveSheet
            erow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
            .Paste Destination:=.Cells(erow, 1)
        End With
        .ClearContents
    End With
    ActiveWorkbook.Close savechanges:= True
    MyFile = Dir
Loop
End Sub

I get it now! That seems to have done the trick.

Thank you so much for your help.