Open Multiple Files, Copy Worksheet, Paste in to Specific Tab in Original Workbook

**freybe06** · 05-28-2013, 03:25 PM

Hello Everyone,

I'm looking to build a macro that will open specific files, copy the first worksheet, and then paste that information into a specific tab in the original workbook.

For example, the macro will open a workbook called "January.CSV", it will copy the first worksheet (which would be titled "JanuaryWorksheet"), then it will go back in to the original workbook and paste that data in to a tab called "JanuaryData". Next, the macro will open a workbook called "February.XLS", it will copy the first worksheet (which would be titled "FebruaryInfo"), then it will go back in to the original workbook and paste that data in to a tab called "FebruaryData".

As you can tell, this will most likely need to be specialized by me to add in each file name and worksheet names, so if you could just provide me some code using the January and February example I gave above, I'm sure I can add on to it to make it work.

Let me know if you have any questions.

Thanks!

**arlu1201** · 05-28-2013, 03:41 PM

Try this code

Option Explicit

Sub cons_data()

Dim Master As Workbook
Dim sourceBook As Workbook
Dim sourceData As Worksheet
Dim CurrentFileName As String
Dim myPath As String
Dim sname as string

Application.ScreenUpdating = False
Application.DisplayAlerts=false

'The folder containing the files to be recap'd
myPath = "D:\Test"

'Finds the name of the first file of type .xls in the current directory
CurrentFileName = Dir(myPath & "\*.csv")

'Create a workbook for the recap report
Set Master = ThisWorkbook

Do
    Workbooks.Open (myPath & "\" & CurrentFileName)
    Set sourceBook = Workbooks(CurrentFileName)
    Set sourceData = sourceBook.Worksheets(1)
    
    With sourceData
        sname = Left(CurrentFileName, Len(CurrentFileName) - 4) & "Data"
        Master.Worksheets.Add(after:=Master.Worksheets(Master.Worksheets.Count)).Name = sname
        .Cells.Copy Master.Worksheets(sname).Range("A1")
    End With
       
    sourceBook.Close
  
'Calling DIR w/o argument finds the next .xlsx file within the current directory.
CurrentFileName = Dir()
Loop While CurrentFileName <> ""

msgbox "Done"

Application.displayalerts=True
Application.ScreenUpdating = True

End Sub

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button

**freybe06** · 05-29-2013, 11:05 AM

Arlu,

Thank you for the response but I'm not sure this is what I'm looking for. First, not all the files are in CSV format. Some are XLS (as shown in the February part of my example). Second, the worksheets are already created in my current workbook and their naming convention is not tied to the naming convention of the files the macro will be opening. Lastly, the range in which I will be pasting the information varies from file to file. Sometimes I will be pasting the info to A1, sometimes to A2, etc.

I think I just need some code that I can copy and paste for each document I have to open. Then I can customize each section to the appropriate details. I don't think there is really a way to just do a loop and standardize everything due to the fact that they all have separate circumstances. The only thing that stays the same is the number of files that need to be opened and they will always have the same name from month to month.
Thanks again!

**arlu1201** · 05-29-2013, 11:33 AM

Cant the circumstances be put into some condition check? This way, you do not need to make separate codes but can loop everything?

Why dont you just provide me a few of the circumstances and i can see how to loop it?

**freybe06** · 05-29-2013, 12:40 PM

Arlu,

Without attaching any files I think the best I can do is what I started in the original post. Here will be the details:

File 1 to Open/Copy - Filename: "January.CSV", Worksheet: "JanuaryWkst", Range: A1-bottom. Paste to: Active Workbook, Worksheet: "JanuaryData"

File 2 to Open/Copy - Filename: "February.XLS", Worksheet: "FebInfo", Range: A2-bottom. Paste to: Active Workbook, Worksheet: "F2013"

File 2 to Open/Copy - Filename: "Mar.CSV", Worksheet: "March1", Range: A1-bottom. Paste to: Active Workbook, Worksheet: "MarchNumbers"

As you can see in the 3 examples above, there isn't a standard filename, file type, worksheet name, or range for the files to copy. The only thing they have in common is that they are all held in the same folder. With the active workbook, none of the tabs are "linked" to the files I will be copying the information from. The only way I can "link" them is to manually work through the macro and tell it which file goes to which worksheet. (I know this isn't the best way to do it, but the workbook is already put together and cannot be changed.)

Thanks!

**arlu1201** · 05-30-2013, 02:30 AM

We can try and simplify this way. In the master file where you are going to consolidate your data, you can have one sheet (possibly hidden) which will contain all these parameters. For e.g. Column A - File Name, Column B - Source Worksheet name, Column C - Column to be copied, Column D - Destination sheet name.

Then you can have the macro loop through each row of this sheet and action accordingly. If you use this method, if there are any changes to your files, you can just make the changes to this sheet instead of changing the code.

Works for you?

**freybe06** · 05-30-2013, 10:27 AM

That sounds great!

Open Multiple Files, Copy Worksheet, Paste in to Specific Tab in Original Workbook

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Open Multiple Files, Copy Worksheet, Paste in to Specific Tab in Original Workbook

Re: Open Multiple Files, Copy Worksheet, Paste in to Specific Tab in Original Workbook

Re: Open Multiple Files, Copy Worksheet, Paste in to Specific Tab in Original Workbook

Re: Open Multiple Files, Copy Worksheet, Paste in to Specific Tab in Original Workbook

Re: Open Multiple Files, Copy Worksheet, Paste in to Specific Tab in Original Workbook

Re: Open Multiple Files, Copy Worksheet, Paste in to Specific Tab in Original Workbook

Re: Open Multiple Files, Copy Worksheet, Paste in to Specific Tab in Original Workbook

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions