Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
SolvedPrivate

Excel worksheets import modification

Posted on 2015-02-10
3
Medium Priority
?
24 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
[X]
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
  • 2
3 Comments
 
LVL 31

Accepted Solution

by:
gowflow earned 2000 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 31

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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

671 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