Create a macro to remove previous data from an excel and populate with data in another

Hi

I am trying to create a macro or VBA script where I can remove the data stored previously and then automatically populate it with data stored in another workbook.

Apart from this also looking for macro where I can club data from many workbooks into a single workbook and same sheet.

Thanks a tons!

Here is code to cycle thru all excel files in given folder,
then combine the data into 1 sheet.

here, the given start folder is in: Range("B1").Value
ex: C:\temp

paste this code into a module, then run: CombineFiles

Private wbSrc As Workbook, wbTarg As Workbook

Sub CombineFiles()
ScanFilesIn1Folder Range("B1").Value
End Sub


Private Sub ScanFilesIn1Folder(ByVal pvStartDir)
Dim FileSystem As Object
Dim Folder As Object
Dim oFile As Object
Dim i As Integer
Dim wbStart As Workbook

If IsNull(pvStartDir) Then
  MsgBox "No start folder"
  Exit Sub
End If

Set wbStart = ActiveWorkbook
Set FileSystem = CreateObject("Scripting.FileSystemObject")
Set Folder = FileSystem.GetFolder(pvStartDir)

Range("K1").Value = "files"
Range("K2").Select
For Each oFile In Folder.Files
    
    If InStr(oFile.Name, ".xls") > 0 Then
       ActiveCell.Value = oFile
       i = i + 1
       
       AddData i, oFile
       
       wbStart.Activate
       ActiveCell.Offset(1, 0).Select  'next row of file trace
    End If
    
skip1:
Next
wbTarg.Activate
wbTarg.Save
MsgBox "Done"

Set oFile = Nothing
Set Folder = Nothing
Set FileSystem = Nothing
Set wbSrc = Nothing
Set wbTarg = Nothing
Set wbStart = Nothing
End Sub


Private Sub AddData(ByVal pvFileNum, ByVal pvFile)
Dim ws As Worksheet

Workbooks.Open pvFile
Set wbSrc = ActiveWorkbook

For Each ws In Sheets
    ws.Activate
    
    If pvFileNum = 1 Then    'use the 1st file as the base sheet
       pvFileNum = 2
       Sheets(1).Select
       Sheets(1).Copy
       ActiveWorkbook.SaveAs "c:\temp\CombinedData.xls"
       Set wbTarg = ActiveWorkbook
       ActiveSheet.Name = "combined"
       Rows("1:5").Delete
       GoSub Move2Btm
    Else
       Rows("1:6").Delete
       Range("A1").Select
       ActiveSheet.UsedRange.Select
       Selection.Copy
       
        wbTarg.Activate
        ActiveSheet.Paste
        Application.CutCopyMode = False
        GoSub Move2Btm
        wbSrc.Activate
    End If
Next
wbSrc.Close False
Set ws = Nothing
Exit Sub

'----------
Move2Btm:
'----------
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select  'next row
Return
End Sub

Ok thanks. Also can you help me with creating a macro or VBA script where I can remove the data stored previously and then automatically populate it with data stored in another workbook. I am able to create the macro but on running it, it populates the data only partially and not the complete data from the file it has to copy from