Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
SolvedPrivate

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

Posted on 2015-02-06
24
16 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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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
 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Distribute Values over date range (2 of 2) 5 22
Excel Calculation 4 44
VBA to Delete the Filtered Rows in an Excel Table 3 31
List Box Entries Excel 2010 5 24
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
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…

790 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