SolvedPrivate

Excel worksheets import modification

Posted on 2015-02-10
3
18 Views
Last Modified: 2016-02-10
I have Excel VBA code that imports a range named "State" into a new tab on the same workbook.  The code allows me to select one or more worksheets to import. The import starts in column B. I would like to have the code modified to import in column A a range named LocationCode. The attached file named ImportState-V03.xlsm has the code that imports the information that I need from the attached sample1 and sample2 files. Please see ImportState-V03.xlsm tab "State" for a sample. Columns B and C were imported per the original code upon selecting sample1 and sample2. I now need to include with the  import the range named "LocationCode" into column A.

Conernesto
C--For-Experts-Exchange-Sample1.xlsx
C--For-Experts-Exchange-Sample2.xlsx
C--From-Experts-Exchange-ImportState-V03
0
Comment
Question by:Conernesto
  • 2
3 Comments
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 40601681
Here is the new code

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

'This code will propmt you for the worksheets to summarize STATE Revenue_
' You will select a template or as many templates as you want to import_
' A new tab called State will be inserted and the range named "State" will be imported column after column
' If you run the macro twice, the macro will replace the first import and replace the revenues per your selection


'---> 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(2, J + 2).PasteSpecial (xlPasteValues)
                J = J + 1
            ElseIf LCase(oName.Name) = "locationcode" Then
                Range(oName.Name).Copy
                WSState.Cells(2, "A").PasteSpecial (xlPasteValues)
            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


and attached is the file.
gowflow
C--From-EE-ImportState-V04.xlsm
0
 

Author Comment

by:Conernesto
ID: 40601697
This is right on the money. I have one question. How can I get started learning this code?
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40602498
Well its called hard work and lots of search and trial errors and mostly you wanting so badly to do this !!! there are courses out there that initiate to VBA but sorry cant lead you as I am a self learner never took a course.

But last no need to break your head (unless if you like to) this is why we are here. Just throw the questions at us and will be glad to assist.

Case you need further help pls feel free to copy/paste the http address link of a new question in here and I will gladly assist.

gowflow
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from 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.

Question has a verified solution.

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

Suggested Solutions

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

776 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