VBA: Copy from folder

kris_friis

New Member
Joined
Nov 27, 2020
Messages
8
Office Version
  1. 2013
Platform
  1. Windows
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
VBA Code:
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
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
Will col A always have data in every cell?
 

kris_friis

New Member
Joined
Nov 27, 2020
Messages
8
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Nov 27, 2020
Messages
8
Office Version
  1. 2013
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

kris_friis

New Member
Joined
Nov 27, 2020
Messages
8
Office Version
  1. 2013
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Nov 27, 2020
Messages
8
Office Version
  1. 2013
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 27, 2020
Messages
8
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,997
Messages
5,622,083
Members
415,875
Latest member
Tarali

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top