Vba - importing a cell value from multiple spreadsheets in a directory

[Cuzzaa]

Hi all,

Hope you can help.

I am using the code below for one of my macros to import a cell range from a different sheet selected by the user into my active workbook in a specific cell range, but is anyone able to help please to tweak the code please so that instead of clicking the button to select which file to import the data, instead, you select a folder directory and then it opens every unique .csv file within the folder, copies cell 'A1' (and closes the spreadsheet again) and then pastes it into my active work book in cell 'A1'?

I would then also like it to keep running until it has repeated the above steps for every unique file within the selected file directory, but in the active workbook pasting the value from cell 'A1' into 'A1', and then 'A1' into 'A2', then 'A1' into A3 and repeats until it's done it for all the .csv files in the folder?

Sub Import()

' Get customer workbook...
Dim customerBook As Workbook
Dim filter As String
Dim caption As String
Dim customerFilename As String
Dim customerWorkbook As Workbook
Dim targetWorkbook As Workbook

Dim aCount As Integer, msg As String
Const msg1 = "The BOM has been imported!" & vbCr & vbCr
Const msg2 = " item(s) could not be found." & vbCr & vbCr & "Please update the 'Lookup' sheet and then try again."
Const msg3 = "All items have been successfully imported."



' make weak assumption that active workbook is the target
Set targetWorkbook = Application.ActiveWorkbook


' get the customer workbook
filter = "Text files (*.xls),*.xls"
caption = "Please select the file "
customerFilename = Application.GetOpenFilename(filter, , caption)


Set customerWorkbook = Application.Workbooks.Open(customerFilename)


' assume range is A2 - K200 in sheet1
' copy data from customer to target workbook
Dim targetSheet As Worksheet
Set targetSheet = targetWorkbook.Worksheets(1)
Dim sourceSheet As Worksheet
Set sourceSheet = customerWorkbook.Worksheets(1)




targetSheet.Range("B13", "L1000").Value = sourceSheet.Range("A2", "K1000").Value


' Close customer workbook
customerWorkbook.Close


' targetSheet.Range("M3").Value = customerFilename


Range("A1").Select

MsgBox "Successfully imported!", vbInformation

End Sub

Any help would be honestly really appreciated!

Thanks so much

 

[JLGWhiz]

According to Forum rules, the scenario in Post #10 exceeds the parameters of the original post in this thread and would need to be treated as a new topic in a new thread. This rule is so that forum members searching for topics they might have an interest in can more readily find those topics.
Regards, JLG

PS. The reason you are skipping rows is because you changed the offset from (2) to (3) on this line of code:

wb.Sheets(1).Range("E2").Copy sh.Cells(Rows.Count, 4).End(xlUp)(3)

 

[Cuzzaa]

According to Forum rules, the scenario in Post #10 exceeds the parameters of the original post in this thread and would need to be treated as a new topic in a new thread. This rule is so that forum members searching for topics they might have an interest in can more readily find those topics.
Regards, JLG

PS. The reason you are skipping rows is because you changed the offset from (2) to (3) on this line of code:

wb.Sheets(1).Range("E2").Copy sh.Cells(Rows.Count, 4).End(xlUp)(3)

Understood. Thanks very much for all your help on this. I am honestly very grateful.

I have created a new thread to ask this question, hopefully someone can offer some assistance.

Thanks again!