VBA: Copy from folder

[kris_friis]

Hi all,
I have written this code, but I have an issue: When it runs the second time (it should run daily) it deletes the previous data and past the new one from below what it deleted. It should delete the first and then paste from the second row

Sub MergeFiles()
Worksheets("Copy").Range("A1:AV12000").Clear
Dim path As String, ThisWB As String, lngFilecounter As Long
Dim wbDest As Workbook, shtDest As Worksheet, ws As Worksheet
Dim Filename As String, Wkb As Workbook
Dim CopyRng As Range, Dest As Range
    Dim RowofCopySheet As Integer
    RowofCopySheet = 2 ' Row to start on in the sheets you are copying from
    ThisWB = ActiveWorkbook.Name

    path = "C:\Users\OneDrive\PowerApp"
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Set shtDest = ActiveWorkbook.Sheets(1)
Filename = Dir(path & "\*.xlsx", vbNormal)
If Len(Filename) = 0 Then Exit Sub
Do Until Filename = vbNullString
If Not Filename = ThisWB Then
Set Wkb = Workbooks.Open(Filename:=path & "\" & Filename)
Set CopyRng = Wkb.Sheets(1).Range(Cells(RowofCopySheet, 1), Cells(ActiveSheet.UsedRange.Rows.Count, ActiveSheet.UsedRange.Columns.Count))
Set Dest = shtDest.Range("A" & shtDest.UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1)
CopyRng.Copy Dest
Wkb.Close False
End If

Filename = Dir()
Loop
Range("A2:AV300").Select

Application.EnableEvents = True
Application.ScreenUpdating = True

    MsgBox "Data is merged"
End Sub
Sub MergeFiles()
Worksheets("Copy").Range("A1:AV12000").Clear
Dim path As String, ThisWB As String, lngFilecounter As Long
Dim wbDest As Workbook, shtDest As Worksheet, ws As Worksheet
Dim Filename As String, Wkb As Workbook
Dim CopyRng As Range, Dest As Range
    Dim RowofCopySheet As Integer
    RowofCopySheet = 2 ' Row to start on in the sheets you are copying from
    ThisWB = ActiveWorkbook.Name

    path = "C:\Users\ksf008\OneDrive - Maersk Group\PowerApp\Mini Database"
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Set shtDest = ActiveWorkbook.Sheets(1)
Filename = Dir(path & "\*.xlsx", vbNormal)
If Len(Filename) = 0 Then Exit Sub
Do Until Filename = vbNullString
If Not Filename = ThisWB Then
Set Wkb = Workbooks.Open(Filename:=path & "\" & Filename)
Set CopyRng = Wkb.Sheets(1).Range(Cells(RowofCopySheet, 1), Cells(ActiveSheet.UsedRange.Rows.Count, ActiveSheet.UsedRange.Columns.Count))
Set Dest = shtDest.Range("A" & shtDest.UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1)
CopyRng.Copy Dest
Wkb.Close False
End If

Filename = Dir()
Loop
Range("A2:AV300").Select

Application.EnableEvents = True
Application.ScreenUpdating = True

    MsgBox "Data is merged"
End Sub

 

[Fluff]

Hi & welcome to MrExcel.
Will col A always have data in every cell?

 

[kris_friis]

Thanks you @Fluff columns A:AV should always be copied from the folder. While AW:BC will always have data in the master sheet

 

[kris_friis]

If it is possible only to copy over new data from the folder, that could also work. So the VBA doesn't delete the sheet, but only copy over new lines!

 

[Fluff]

Is there any column that will always have data on the last used rows?

 

[kris_friis]

A1:AW10 will always have data, so new data should be copied in from row A11. AW:BC are cubevalues, so will only be activated when the data is pasted. Hope that makes sense. Formula should run on weekly basis, and there will be new data in the folder, so thus the master should be updated. Hope it makes sense!

 

[Fluff]

You have not answered my question, also you originally said to copy from row 2, now you are saying from row 11, which is it?

 

[kris_friis]

Sorry, it should be copied from row 2, but be inserted from row 11. I am not sure I completely understand your question, that is why I try to elaborate.

 

[Fluff]

The code needs to know where the last row of data is on the sheets being copied, therefore is there any column which will always have data on the last row?

 

[kris_friis]

I want to code to delete all the data before copying in the new data, so it should delete the existing data in mastersheet from row A11:AW12000, then copy in the new data from the folder (PowerApps, row 2) copied to Master sheet starting from row 11. Next day it should delete all data again from row A11:AW12000 then copy in the data from the folder (PowerApps, row 2) copied to Master sheet starting from row 11.
Right now it does it correctly, but instead of inserting from row 11, it inserts beneath what the code just deleted.
To your question: The last row differs from sheet to sheet, maximum lenght is 2000 rows. In the mastersheet column AW will always have data.