Determine exported worksheet name

hardroader

New Member
Joined
Aug 1, 2013
Messages
37
I have an existing macro that works but I would like to eliminate 1 step. I am downloading information from a website in the form of a comma separated file. I then copy that information into another spreadsheet called "Export.csv" and then I run my macro and it updates the information as necessary. I would like to eliminate the need to copy the information into the "Export.csv" file and use the file name that was used when the information was downloaded.


Code:
Sub MorningstarExport()
'
' 2014 RSI Tracker EL Module 9

' MorningstarExport Macro
'
' Option Key  Cntl+Shift+E

' Before you run this marco, the proper information from the website needs to be downloaded and saved to a file named "Export.csv"
    
    Dim UserEntry As String
        
' Select a worksheet to receive the data that is in the Export.csv file

    Do
        UserEntry = Application.InputBox("Enter the worksheet name to received the exported data.", _
            "Destination Sheet", Type:=2)
        If UserEntry = "False" Then Exit Sub                               '  User canceled
        If Evaluate("ISREF('" & UserEntry & "'!A1)") Then Exit Do  'Test if worksheet name exists
        MsgBox "Unable to find sheet named: " & UserEntry, , "Sheet Not Found"
    Loop
    
    Sheets(UserEntry).Select
          
' Copy/Paste the information from export.csv file


    Workbooks("Export.csv").Sheets(1).Range("A1:M113").Copy _
        Destination:=ActiveSheet.Range("AA484")


    Range("AA484:AK486").Select
    Application.CutCopyMode = False
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Font.Bold = True
    
     UserDate = InputBox("Enter the date of this import.")
    
    Range("AA486").Select
    ActiveCell = UserDate
            
    Range("L485").Select
    ActiveCell.FormulaR1C1 = "=CONCATENATE(R[21]C[-3],"" "",R[21]C[-2])"
    Range("L485").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Font.Bold = False
    Selection.Font.Bold = True
    
    Call JumpDueDiligencePage3

ExitSub:
    
End Sub

[CODE]

Thank you for taking the time to look at this.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Forum statistics

Threads
1,214,822
Messages
6,121,765
Members
449,049
Latest member
greyangel23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top