Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Private
  • Views: 25
  • Last Modified:

Excel worksheets import modification

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
Conernesto
Asked:
Conernesto
  • 2
1 Solution
 
gowflowCommented:
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
 
ConernestoAuthor Commented:
This is right on the money. I have one question. How can I get started learning this code?
0
 
gowflowCommented:
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

Independent Software Vendors: 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!

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