Using a Macro to copy data from all CSV files in folder

MGA22

New Member
Joined
Mar 31, 2021
Messages
3
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
Hello,

I was wondering if it would be possible to copy columns A-I from one worksheet(in the folder) and paste it in cell A1 then call on another 2 macros before looping and copying the data from the next CSV file in the folder. Essentially the new data copied over from the next sheet will overwrite the existing data that was copied over previously.

I'm still quite new to macros and the other macros I've created are quite basic for copying data over to a new table.

I would really appreciate any help.

Thank you.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,137
Office Version
  1. 2016
Platform
  1. Windows

MGA22

New Member
Joined
Mar 31, 2021
Messages
3
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
Thanks for your reply Zot.

I've gone through and made the code below.

VBA Code:
Sub CopyDatafromFolder()
Dim fileName As Variant


 fileNmae = Dir("FolderDestination\ * .csv")

While fileName <> ""
 
 Columns("A:I").Select
    Selection.Copy
    
    Sheets("CSV Template").Range("A1").PasteSpecial


 'Reset filename
 fileName = Dir
 
 Call K_M_v2
 
 
Wend

End Sub


The issue I have at the moment is that I'm not too sure on how to reference my current active worksheet that I want to paste the data into so that it actually pastes.



Thank you,
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,137
Office Version
  1. 2016
Platform
  1. Windows
Say you have macro in workbook you want to work on. During this stage you can define your worksheet already like

Dim ws As Worksheet
Set ws = Activeworkbook.Sheets("Sheet1")

Say you macro open another workbook from your data source file. Once the file opened by macro, the open file is now active usially. At this stage, you can define your source workbook and source worksheet. This way you have reference to both worksheet to be referred to in you code.
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,137
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Here is one example I wrote before

VBA Code:
Sub Test()

Dim Fname As Variant
Dim ws1 As Worksheet, ws2 As Worksheet
Dim wbA As Workbook, wbB As Workbook
Dim rngTotal As Range

Application.ScreenUpdating = False

' Define this Workbook as wbA
Set wbA = ActiveWorkbook
' Define working sheet in wbA. Change sheet name accordingly
Set ws1 = wbA.Sheets("Sheet1")

' Search destination Workbook
Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx; *.xlsm; *.xlsb), *.xls; *.xlsx; *.xlsm; *.xlsb", Title:="Select a File")
If Fname = False Then Exit Sub                         'CANCEL is clicked

' Define opened Workbook as wbB while opening it.
Set wbB = Workbooks.Open(Filename:=Fname, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
' Define working sheet in wbB. Change sheet name accordingly
Set ws2 = wbB.Sheets("Sheet1")

End Sub
 

MGA22

New Member
Joined
Mar 31, 2021
Messages
3
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
Thanks again for your reply,

I understand now that i have to reference this worksheet that i want to copy the data to in my code (below as ws) when i want to paste the copied data and have altered it as per below. Issue is that it doesn't actually copy the data from the other workbooks and paste it in this worksheet("CSV Template"). I can see that it calls on the macro K_M_v2 but the data doesn't actually change.

VBA Code:
Sub CopyDatafromFolder()
Dim fileName As Variant
Dim ws As Worksheet

Set ws = ActiveWorkbook.Sheets("CSV Template")

 fileName = Dir("H:\Retail\Independents Channel\Metcash\Head Office\Financial Analysis\Claims\2021 Metcash Claims\02. February\BBS\ *.csv")

While fileName <> ""
 
 Columns("A:I").Select
    Selection.Copy
    
    ws.Range("A1").PasteSpecial


 'Reset filename
 fileName = Dir
 
 Call K_M_v2
 
 
Wend

Thank you
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,137
Office Version
  1. 2016
Platform
  1. Windows
Thanks again for your reply,

I understand now that i have to reference this worksheet that i want to copy the data to in my code (below as ws) when i want to paste the copied data and have altered it as per below. Issue is that it doesn't actually copy the data from the other workbooks and paste it in this worksheet("CSV Template"). I can see that it calls on the macro K_M_v2 but the data doesn't actually change.

VBA Code:
Sub CopyDatafromFolder()
Dim fileName As Variant
Dim ws As Worksheet

Set ws = ActiveWorkbook.Sheets("CSV Template")

fileName = Dir("H:\Retail\Independents Channel\Metcash\Head Office\Financial Analysis\Claims\2021 Metcash Claims\02. February\BBS\ *.csv")

While fileName <> ""

Columns("A:I").Select
    Selection.Copy
   
    ws.Range("A1").PasteSpecial


'Reset filename
fileName = Dir

Call K_M_v2


Wend

Thank you
It is easier to create identifiable nickname for workbooks (if you are working with more than one workbook) and worksheets. Thsi way easy to refer to and you will not get lost :)

Glad you have that figure out yourself
 

Watch MrExcel Video

Forum statistics

Threads
1,129,368
Messages
5,635,856
Members
416,886
Latest member
coreyalaurence37

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