SolvedPrivate

How do I Merging Data from Multiple Workbooks into a Summary Workbook

Posted on 2015-02-06
24
13 Views
Last Modified: 2016-02-10
I have various Excel workbooks in a folder. Each workbook has various cells that are part of a range named "State". I want to copy each range from the different workbooks into my open workbook starting in column C1. I want the values copied (not the formulas).
The path to my workhbook is C:\My Documents. The extension on the worksheets is .xlsx

Do you have vba code that can do this?

Conernesto
0
Comment
Question by:Conernesto
  • 12
  • 12
24 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 40594729
Can you post one or 2 of these workbook to make sure that you have not missed on something in the description or else will go back and forth trying to dissect things that would be easier in posting sample.

gowflow
0
 

Author Comment

by:Conernesto
ID: 40594793
Attached is Sample1 and Sample2 worksheets. I want to see the below information on the new worksheet after running the macro/code.

10      700
222      300
300      456
80      90
30      2000
20      0
800      0
200      400
400      500
500      600
C--For-Experts-Exchange-Sample1.xlsx
C--For-Experts-Exchange-Sample2.xlsx
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40594828
Here you go !!!

I gave you the option to choose the directory that you want and select 1 or multiple files that you want.
We can remove this when all fined tuned. !

here is the code

Sub ImportStates()
Dim WB As Workbook
Dim WS As Worksheet
Dim vFile As Variant
Dim sFiles As Variant
Dim oName As Name
Dim ThisWS As Worksheet
Dim ThisMaxRow As Long, I As Long

'---> Disable Events
With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .DisplayAlerts = False
End With


'---> Set Variables
Set ThisWS = ActiveSheet
ThisMaxRow = ThisWS.Range("A" & ThisWS.Rows.Count).End(xlUp).Row + 1


vFile = Application.GetOpenFilename("Excel Files (*.xlsx), *.xslx", , "Import Files", , True)
'vFile = Split(sFiles, """ """)

For Each sFiles In vFile
'For I = LBound(vFile) To UBound(vFile)
    Set WB = Workbooks.Open(sFiles)
    Set WS = WB.ActiveSheet
    'For Each WS In WB.Worksheets
        For Each oName In WB.Names
            If LCase(oName.Name) = "state" Then
                Range(oName.Name).Copy ThisWS.Cells(ThisMaxRow, "C")
                ThisMaxRow = ThisWS.Range("C" & ThisWS.Rows.Count).End(xlUp).Row + 1
            End If
        Next oName
    'Next WS
    WB.Close False
    Set WB = Nothing
    Set WS = Nothing
Next sFiles

'---> Enable Events
With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
End With

MsgBox "State Imported successfully in Col C", vbExclamation

End Sub

Open in new window


Try it and let me know.
Activate macro and just press on the button Import State.

gowflow
ImportState.xlsm
0
 

Author Comment

by:Conernesto
ID: 40594844
I will test this on Monday. Thank you.

Conernesto
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40595338
ok fine let me know.
gowflow
0
 

Author Comment

by:Conernesto
ID: 40598465
Can you modify the code to  copy the value not the formulas? And also change the code so that it copies the first instance of "state" in column C and the nexct "state" in Column D and so on. The code currently places the first instance of "state in Column C and the next instance below the first instanace of C. So If I have two workbooks with information on my range nama state. My worbooks each has a range named state.

I need to code to do this:                                                                Not this:

First Range file    Second range file
1                             5                                                                               1
2                             6                                                                               2    
3                             8                                                                               3
                                                                                                                 5
                                                                                                                 6
                                                                                                                  8
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40598603
ok here is the code and the workbook.

Sub ImportStates()
Dim WB As Workbook
Dim WS As Worksheet
Dim vFile As Variant
Dim sFiles As Variant
Dim oName As Name
Dim ThisWS As Worksheet
Dim ThisMaxRow As Long, I As Long, J As Long

'---> Disable Events
With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .DisplayAlerts = False
End With


'---> Set Variables
Set ThisWS = ActiveSheet
ThisMaxRow = ThisWS.Range("A" & ThisWS.Rows.Count).End(xlUp).Row + 1


vFile = Application.GetOpenFilename("Excel Files (*.xlsx), *.xslx", , "Import Files", , True)
'vFile = Split(sFiles, """ """)

For Each sFiles In vFile
'For I = LBound(vFile) To UBound(vFile)
    Set WB = Workbooks.Open(sFiles)
    Set WS = WB.ActiveSheet
    'For Each WS In WB.Worksheets
        For Each oName In WB.Names
            If LCase(oName.Name) = "state" Then
                Range(oName.Name).Copy
                ThisWS.Cells(ThisMaxRow, J + 3).PasteSpecial (xlPasteValues)
                J = J + 1
            End If
        Next oName
    'Next WS
    WB.Close False
    Set WB = Nothing
    Set WS = Nothing
Next sFiles

'---> Enable Events
With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
End With
ThisWS.UsedRange.EntireColumn.AutoFit
MsgBox "State Imported successfully in Col C", vbExclamation

End Sub

Open in new window


gowflow
ImportState-V01.xlsm
0
 

Author Comment

by:Conernesto
ID: 40598679
Gowflow,

The code is great thank you.

Conernesto
0
 

Author Comment

by:Conernesto
ID: 40598693
Can you do two modifications to the code? 1) import the information to a new worksheet (not the active worksheet) and 2) set the column with to 20.

Thank you.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40598739
shall we call the worksheet State and if State exist we delete it ? Shall we start at row1 ???
Furthermore if it is a new worksheet then do we still copy at column C or we might as well start at col A ???
gowflow
0
 

Author Comment

by:Conernesto
ID: 40598760
Yes - The worksheet can be named State and it can be deleted if it exists. The start row can stay as you currently have it. I would like the width of each column to be 20.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40598763
What about starting at Col A instead of Col C or still need it at Col C ??
gowflow
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 29

Expert Comment

by:gowflow
ID: 40598799
ok here it is starting in Col A
Sub ImportStates()
Dim WB As Workbook
Dim WS As Worksheet
Dim vFile As Variant
Dim sFiles As Variant
Dim oName As Name
Dim WSState As Worksheet
Dim MaxRowS As Long, I As Long, J As Long

'---> Disable Events
With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .DisplayAlerts = False
End With


'---> Set Variables
On Error Resume Next
Set WSState = Sheets("State")
If Err <> 0 Then
    Set WSState = Worksheets.Add(after:=Worksheets(Worksheets.Count))
    WSState.Name = "State"
End If
On Error GoTo 0
WSState.Cells.Delete

vFile = Application.GetOpenFilename("Excel Files (*.xlsx), *.xslx", , "Import Files", , True)


For Each sFiles In vFile

    Set WB = Workbooks.Open(sFiles)
    Set WS = WB.ActiveSheet

        For Each oName In WB.Names
            If LCase(oName.Name) = "state" Then
                Range(oName.Name).Copy
                WSState.Cells(1, J + 1).PasteSpecial (xlPasteValues)
                J = J + 1
            End If
        Next oName

    WB.Close False
    Set WB = Nothing
    Set WS = Nothing
Next sFiles

'---> Enable Events
With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
End With
WSState.UsedRange.EntireColumn.ColumnWidth = "20"

MsgBox "State Imported successfully.", vbExclamation

End Sub

Open in new window


gowflow
ImportState-V02.xlsm
0
 

Author Comment

by:Conernesto
ID: 40598814
I juste created a cell range named "StateLocation". Can you insert this range in Column A and start the above code in Col B? The purpose for leaving column A empty was for me to copy the state location in to this column. So "StateLocation" would be copied once and then the "State" range is copied accross. See sample below.

StateLocation    State       State
AL                           100        800
AZ                           300        600
CA                           500        250
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 40599203
Here it is
gowlfow
ImportState-V03.xlsm
0
 

Author Comment

by:Conernesto
ID: 40599575
gowlfow,

Attached are my two sample worksheets and the worksheet you sent me (ImportStatae-V03.xlsm). I ran the code from the  worksheet you sent me and a new tab called State was created (please see the tab). On the new tab, I am showing the addition to the code that I would like added. Attached are my revised sample1 and sample2 worksheets. The sample worksheets have a range name called LocationCode. I would like to have these range included when the new worksheet "State" is created.

conernesto
C--From-Experts-Exchange-ImportState-V03
C--For-Experts-Exchange-Sample1.xlsx
C--For-Experts-Exchange-Sample2.xlsx
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40600147
Sorry but sake clarity and not confusing whoever is looking at these thread and in no way seeking for points, I have following comment:

I believe we have achieved the purpose of your initial question in full or I should say we have answered your question as to opening workbook and finding range State and importing the data and we even went beyond that in fine-tuning the answers which is fine.

I propose sake clarity that this question be closed with the answer you see best fit and if you believe you need more to add or modify you are welcome to create a related question pointing to this one and ask all that you need to be adjusted or added.

gowflow
0
 

Author Comment

by:Conernesto
ID: 40600726
Agree.

Thank you.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40600920
Pls post a link here of any new question you may need help with.
gowflow
0
 

Author Comment

by:Conernesto
ID: 40601635
I just posted a follow-up question.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40601654
pls put a link in here of the question if you need my help
gowlfow
0
 

Author Comment

by:Conernesto
ID: 40601660
How do I do the link?
0
 

Author Comment

by:Conernesto
ID: 40601673
The new question is called "Excel worksheets import modification". It appears to have an ID 28614195
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40601686
Found and done !!
gowlfow
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now