SolvedPrivate

Excel worksheets import modification

Posted on 2015-02-10
3
17 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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
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.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

743 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