SolvedPrivate

Excel worksheets import modification

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
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…

839 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