Macro to copy data from multiple Excel files

[Excelsius1]

The attachments are here: https://docs.google.com/folder/d/0B0AZLzY6tv15N2J4aWpDcjVvOEk/edit

I made an earlier thread that perhaps wasn’t clear, so I am redoing it. I’m trying to write a macro that will look into file “File1.xlsx” without opening it and copy cells A3:A8 from the sheet “CopySheet” into the cells A4:A9 of “Summary.xlsm” and the sheet “SummarySheet.” Can anybody please tell me what will be this code in its simplest, but complete form? I have not written a single VBA code before and my goal here to actually learn what I’m doing.

Ok, so now I want to take the above code and add a code that does this: after A4:A9 of “SummarySheet” are filled, the code looks at the NEXT file in the folder called “C:\Test” and copies the same cells (A3:A8) again in the “SummarySheet” of “Summary.xlsm,” except in the next column over. And then keep repeating this process until all the files in the given folder are processed (close to 100). The "SummarySheet" already contains exactly what I want the code to copy. Can anyone please tell me how I can write this macro? I think that if I see these two separate macro codes in action, I can learn a lot about designing macros and maybe come back with specific questions, but right now I’m just lost, with a huge amount of data that I still need to sort and I don’t want to do this manually when there is an easier way. If you guys can’t help, I’d appreciate some tips about getting quality paid help that’s reasonable.

Thanks!

P.S.: I have looked at some VBA code samples, but the only ones I have seen that do what I want are very complicated and long and I can't exactly figure them out. I think that this code can be written in its simplest form that doesn't take up more than a page. Sort of like with HTML codes - if you let a program write it for you, it's going to be too cluttered and you can't understand much from it.

 

[cml19722000]

The attachments are here: https://docs.google.com/folder/d/0B0AZLzY6tv15N2J4aWpDcjVvOEk/edit

I made an earlier thread that perhaps wasn’t clear, so I am redoing it. I’m trying to write a macro that will look into file “File1.xlsx” without opening it and copy cells A3:A8 from the sheet “CopySheet” into the cells A4:A9 of “Summary.xlsm” and the sheet “SummarySheet.” Can anybody please tell me what will be this code in its simplest, but complete form? I have not written a single VBA code before and my goal here to actually learn what I’m doing.

Ok, so now I want to take the above code and add a code that does this: after A4:A9 of “SummarySheet” are filled, the code looks at the NEXT file in the folder called “C:\Test” and copies the same cells (A3:A8) again in the “SummarySheet” of “Summary.xlsm,” except in the next column over. And then keep repeating this process until all the files in the given folder are processed (close to 100). The "SummarySheet" already contains exactly what I want the code to copy. Can anyone please tell me how I can write this macro? I think that if I see these two separate macro codes in action, I can learn a lot about designing macros and maybe come back with specific questions, but right now I’m just lost, with a huge amount of data that I still need to sort and I don’t want to do this manually when there is an easier way. If you guys can’t help, I’d appreciate some tips about getting quality paid help that’s reasonable.

Thanks!

P.S.: I have looked at some VBA code samples, but the only ones I have seen that do what I want are very complicated and long and I can't exactly figure them out. I think that this code can be written in its simplest form that doesn't take up more than a page. Sort of like with HTML codes - if you let a program write it for you, it's going to be too cluttered and you can't understand much from it.

Hi,

Below script is modified from an example from someone, which I have downloaded from Web.
I have modified it, in order to meet your requirements.

• You need to put all your data files under Test folder (at C:\ Drive)
• Open your Summary.xlsm file, press Alt-F11, Insert a new module and put this code into it.
• Or you can download a copy from below link:
  • https://www.box.com/s/bvinx9ct8amkdwm53tb3

Hope this help....

Sub FolderCrawler()

FileType = "*.xls*" 'The file type to search for
FilePath = "C:\Test\" 'The folder to search

Dim OutputCol As Variant
Dim Curr_File As Variant
Dim FldrWkbk As Workbook

OutputCol = 1 'The first row of the active sheet to start writing to

ThisWorkbook.ActiveSheet.Range(Cells(3, OutputCol), Cells(3, OutputCol)) = FilePath & FileType
OutputCol = OutputCol + 1

Curr_File = Dir(FilePath & FileType)

Do Until Curr_File = ""
Set FldrWkbk = Workbooks.Open(FilePath & Curr_File, False, True) 'Open new data file
Sheets("CopySheet").Range("A3:A8").Copy 'Copy data from specific Range

'Move back to Master file
Workbooks("SUMMARY.xlsm").Activate
Sheets(1).Cells(4, OutputCol).Select
ActiveSheet.Paste
OutputCol = OutputCol + 1

FldrWkbk.Close SaveChanges:=False 'Close the data file
Curr_File = Dir 'Select Next File
Loop
Set FldrWkbk = Nothing
End Sub

 

[Excelsius1]

Wow, that is a very nice and short code that works and I think it will work nicely with just a few tweaks.

So I understand that there is an option to select which cells I want to copy from. Do you know how I could modify the code so I could select exactly where I want to copy the data to in the destination summary sheet in terms of both the destination sheet and destination cells? Basically, this is what I'm trying to do:

A3:A8 copy to B3:B8 in destination sheet "SummarySheet1" and then keep moving over to the next columns until all source files are copied.
A10:A20 copy to B10:B20 in destination sheet "SummarySheet2" and then keep moving over to the next columns until all source files are copied
.
.

If I see the sequence of this, I believe I can recreate it for my real sheet, which is rather complicated. For simplicity, in the excel sheet that I posted there is no data in "A10:A20" and there is only one "SummarySheet," but my real data has many sheets and rows full of numbers that I have to sort. If what I described above isn't clear though, I can create an Excel sheet that shows it.

Thank you again!

 

[cml19722000]

Wow, that is a very nice and short code that works and I think it will work nicely with just a few tweaks.

So I understand that there is an option to select which cells I want to copy from. Do you know how I could modify the code so I could select exactly where I want to copy the data to in the destination summary sheet in terms of both the destination sheet and destination cells? Basically, this is what I'm trying to do:

A3:A8 copy to B3:B8 in destination sheet "SummarySheet1" and then keep moving over to the next columns until all source files are copied.
A10:A20 copy to B10:B20 in destination sheet "SummarySheet2" and then keep moving over to the next columns until all source files are copied
.
.

If I see the sequence of this, I believe I can recreate it for my real sheet, which is rather complicated. For simplicity, in the excel sheet that I posted there is no data in "A10:A20" and there is only one "SummarySheet," but my real data has many sheets and rows full of numbers that I have to sort. If what I described above isn't clear though, I can create an Excel sheet that shows it.

Thank you again!

Hi,

You can play around with the selection range and destination target...

Sheets("CopySheet").Range("A3:A8").Copy 'Copy data from specific Range

'Move back to Master file
Workbooks("SUMMARY.xlsm").Activate
Sheets(1).Cells(4, OutputCol).Select
ActiveSheet.Paste
OutputCol = OutputCol + 1

Good luck.

 

[Excelsius1]

Thank you so much for your help. I've been playing around with this code and I understand almost everything now. It would be nice if the paste sheet cells were selected by cell numbers - for example, writing D3 instead of Outputcol = 4 and Cells(3, OutputCol) - but this code works as is and I'll worry about modifying it after I use it for a while. It's just that if you can specify the exact cell with one parameter, the probability of an error is much lower.

There are three important issues that I can't figure out:

1. Right now, the pasted data works only if the active sheet is open. How can I modify this code so that the paste happens even if I am on another Summary sheet? I believe the issue is here:

'Move back to Master file
Workbooks("SUMMARY.xlsm").Activate
Sheets(1).Cells(2, OutputCol).Select 'Which sheet and row to paste to. Must have the selected sheet open
ActiveSheet.Paste
OutputCol = OutputCol + 1 '+2 skips every other column

2. This one is contingent on the above question being resolved: What is the syntax to increase the number of parameters copied and pasted? For example, in addition to what the code does already, I want to add the same copy and paste function for additional sheets. I tried duplicating this part of the code, but it didn't work:

Do Until Curr_File = ""
Set FldrWkbk = Workbooks.Open(FilePath & Curr_File, False, True) 'Open new data file
Sheets("CopySheet").Range("A3:A8").Copy 'Copy data from specific sheet and range

'Move back to Master file
Workbooks("SUMMARY.xlsm").Activate
Sheets(1).Cells(2, OutputCol).Select 'Which sheet and row to paste to. Must have the selected sheet open
ActiveSheet.Paste
OutputCol = OutputCol + 1 '+2 skips every other column

Do Until Curr_File = ""
Set FldrWkbk = Workbooks.Open(FilePath & Curr_File, False, True) 'Open new data file
Sheets("IgnoreSheet").Range("A3:A8").Copy 'Copy data from specific sheet and range

'Move back to Master file
Workbooks("SUMMARY.xlsm").Activate
Sheets(2).Cells(2, OutputCol).Select 'Which sheet and row to paste to. Must have the selected sheet open
ActiveSheet.Paste
OutputCol = OutputCol + 1 '+2 skips every other column

3. Finally, I was able to insert a code to paste the filename, but I can't get the filename to be pasted at the top of every pasted column. How can that be done?

Here is my entire modified code as of now:

Sub FolderCrawler()

FileType = "*.xls*" 'The file type to search for. * is a wildcard
FilePath = "C:\Users\Hume\Desktop\Test Folder\Working Code\To Copy\" 'The folder to search
FileName = Dir("C:\Users\Hume\Desktop\Test Folder\Working Code\To Copy\*.xls*")

Dim OutputCol As Variant
Dim Curr_File As Variant
Dim FldrWkbk As Workbook

OutputCol = 1 'The first column of the active sheet to start writing to. Filename is always reserved for first column

ThisWorkbook.ActiveSheet.Range(Cells(3, OutputCol), Cells(3, OutputCol)) = FileName 'FilePath & FileType is another option
OutputCol = OutputCol + 1

Curr_File = Dir(FilePath & FileType)

Do Until Curr_File = ""
Set FldrWkbk = Workbooks.Open(FilePath & Curr_File, False, True) 'Open new data file
Sheets("CopySheet").Range("A3:A8").Copy 'Copy data from specific sheet and range

'Move back to Master file
Workbooks("SUMMARY.xlsm").Activate
Sheets(1).Cells(2, OutputCol).Select 'Which sheet and row to paste to. Must have the selected sheet open
ActiveSheet.Paste
OutputCol = OutputCol + 1 '+2 skips every other column

FldrWkbk.Close SaveChanges:=False 'Close the data file
Curr_File = Dir 'Select Next File
Loop
Set FldrWkbk = Nothing
End Sub

Thank you!

 

[abcdegf]

Hi,

Below script is modified from an example from someone, which I have downloaded from Web.
I have modified it, in order to meet your requirements.

• You need to put all your data files under Test folder (at C:\ Drive)
• Open your Summary.xlsm file, press Alt-F11, Insert a new module and put this code into it.
• Or you can download a copy from below link:
  • https://www.box.com/s/bvinx9ct8amkdwm53tb3

Hope this help....

Sub FolderCrawler()

FileType = "*.xls*" 'The file type to search for
FilePath = "C:\Test\" 'The folder to search

Dim OutputCol As Variant
Dim Curr_File As Variant
Dim FldrWkbk As Workbook

OutputCol = 1 'The first row of the active sheet to start writing to

ThisWorkbook.ActiveSheet.Range(Cells(3, OutputCol), Cells(3, OutputCol)) = FilePath & FileType
OutputCol = OutputCol + 1

Curr_File = Dir(FilePath & FileType)

Do Until Curr_File = ""
Set FldrWkbk = Workbooks.Open(FilePath & Curr_File, False, True) 'Open new data file
Sheets("CopySheet").Range("A3:A8").Copy 'Copy data from specific Range

'Move back to Master file
Workbooks("SUMMARY.xlsm").Activate
Sheets(1).Cells(4, OutputCol).Select
ActiveSheet.Paste
OutputCol = OutputCol + 1

FldrWkbk.Close SaveChanges:=False 'Close the data file
Curr_File = Dir 'Select Next File
Loop
Set FldrWkbk = Nothing
End Sub

I am having encountering "Run-time error '9': Subscript out of range". Any idea why the problem is occuring?

[FONT=courier new][I]OutputCol = 1 'The first row of the active sheet to start writing to[/I][/FONT]

[FONT=courier new][I]ThisWorkbook.ActiveSheet.Range(Cells(3, OutputCol), Cells(3, OutputCol)) = FilePath & FileType[/I][/FONT]
[FONT=courier new][I]OutputCol = OutputCol + 1[/I][/FONT]

I dont quite get this portion of the code. Would any kind soul care to explain?

[FONT=courier new][I]Sheets(1).Cells(4, OutputCol).Select
[FONT=courier new][I]ActiveSheet.Paste[/I][/FONT]
[FONT=courier new][I]OutputCol = OutputCol + 1[/I][/FONT][/I][/FONT]

May i check how do i change it to, so that i could choose the sheet to the selected and which cell to have to data copied to?

 

[Fanatic]

Hi,

Below script is modified from an example from someone, which I have downloaded from Web.
I have modified it, in order to meet your requirements.

• You need to put all your data files under Test folder (at C:\ Drive)
• Open your Summary.xlsm file, press Alt-F11, Insert a new module and put this code into it.
• Or you can download a copy from below link:
  • https://www.box.com/s/bvinx9ct8amkdwm53tb3

Hope this help....

Sub FolderCrawler()

FileType = "*.xls*" 'The file type to search for
FilePath = "C:\Test" 'The folder to search

Dim OutputCol As Variant
Dim Curr_File As Variant
Dim FldrWkbk As Workbook

OutputCol = 1 'The first row of the active sheet to start writing to

ThisWorkbook.ActiveSheet.Range(Cells(3, OutputCol), Cells(3, OutputCol)) = FilePath & FileType
OutputCol = OutputCol + 1

Curr_File = Dir(FilePath & FileType)

Do Until Curr_File = ""
Set FldrWkbk = Workbooks.Open(FilePath & Curr_File, False, True) 'Open new data file
Sheets("CopySheet").Range("A3:A8").Copy 'Copy data from specific Range

'Move back to Master file
Workbooks("SUMMARY.xlsm").Activate
Sheets(1).Cells(4, OutputCol).Select
ActiveSheet.Paste
OutputCol = OutputCol + 1

FldrWkbk.Close SaveChanges:=False 'Close the data file
Curr_File = Dir 'Select Next File
Loop
Set FldrWkbk = Nothing
End Sub

Hi guys,

I need help, i'm a complete noob in vba excel. So i tried to modify this code for what i was looking for but got stuck.

Got the filetype, filepath, workbook summary and sheet correct, but cant copy in rows.

What i want is what this code does but instead of Columns, i want in rows.
So from a specific folder open around 200 (number varies) excel files, copy the first row of an specific sheet and then copy to an other excel file incrementaly also in rows.

is it possible?

thanks in advance
fanatic