• Status: Solved
  • Priority: Medium
  • Security: Private
  • Views: 21
  • Last Modified:

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

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
Conernesto
Asked:
Conernesto
  • 12
  • 12
1 Solution
 
gowflowCommented:
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
 
ConernestoAuthor Commented:
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
 
gowflowCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ConernestoAuthor Commented:
I will test this on Monday. Thank you.

Conernesto
0
 
gowflowCommented:
ok fine let me know.
gowflow
0
 
ConernestoAuthor Commented:
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
 
gowflowCommented:
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
 
ConernestoAuthor Commented:
Gowflow,

The code is great thank you.

Conernesto
0
 
ConernestoAuthor Commented:
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
 
gowflowCommented:
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
 
ConernestoAuthor Commented:
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
 
gowflowCommented:
What about starting at Col A instead of Col C or still need it at Col C ??
gowflow
0
 
gowflowCommented:
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
 
ConernestoAuthor Commented:
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
 
gowflowCommented:
Here it is
gowlfow
ImportState-V03.xlsm
0
 
ConernestoAuthor Commented:
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
 
gowflowCommented:
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
 
ConernestoAuthor Commented:
Agree.

Thank you.
0
 
gowflowCommented:
Pls post a link here of any new question you may need help with.
gowflow
0
 
ConernestoAuthor Commented:
I just posted a follow-up question.
0
 
gowflowCommented:
pls put a link in here of the question if you need my help
gowlfow
0
 
ConernestoAuthor Commented:
How do I do the link?
0
 
ConernestoAuthor Commented:
The new question is called "Excel worksheets import modification". It appears to have an ID 28614195
0
 
gowflowCommented:
Found and done !!
gowlfow
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 12
  • 12
Tackle projects and never again get stuck behind a technical roadblock.
Join Now