Copy data from monthly workbooks paste in a single workbook

Closed
Report
Posts
2
Registration date
Wednesday August 10, 2016
Status
Member
Last seen
August 10, 2016
-
Posts
2
Registration date
Wednesday August 10, 2016
Status
Member
Last seen
August 10, 2016
-
Hi Team,

I want a Macro which can assist on the following time consuming task:

I need to copy data from monthly workbooks (each having only 1 sheet) and paste in a single workbook. I want a Macro which automatically copy these data and paste it in the respective sheets without me manually opening the workbook, copy paste the data and reclose the workbook. Points to note are as follows:

1) If I need to copy data from 12 monthly Workbooks, I need to paste it in 12 different sheets.
2) The date to be copied may vary in terms of number of rows and number of columns
3) All the workbooks from which data will be copied are saved in the same folder
4) The workbooks will be named "January", "February", "March" and so on....
5) If I have data only for 6 months only, will the Macro still work?

An early response on the above shall be highly appreciated.

Many thanks and Kind Regards
Peerreh

1 reply

Posts
2
Registration date
Wednesday August 10, 2016
Status
Member
Last seen
August 10, 2016

Hi Guys,

I have the below Macro that I am currently using. However, it copy and paste data from only 1 workbook. I need to repeat this 12 times to copy data from the 12 workbooks and paste data in 12 different sheets. I am stuck with this. Please help.

Sub Macro1()
' 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

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

' get the customer workbook
filter = "Text files (*.xlsx),*.xlsx"
caption = "Please Select an input file "
customerFilename = Application.GetOpenFilename(filter, , caption)

Set customerWorkbook = Application.Workbooks.Open(customerFilename)

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

targetSheet.Range("A1", "C10").Value = sourceSheet.Range("A1", "C10").Value

' Close customer workbook
customerWorkbook.Close
End Sub