User enter worksheet name and then use sheet to copy data

newyears

New Member
Joined
Apr 11, 2008
Messages
46
I need help on this one. I want user after they select workbook to be prompted to input worksheet name to copy data from that worksheet to another workbook. I'm having issue after user enters worksheet name it errors because name is not what I calling for. I think that I have to set worksheet name but I can't figure out. I'm not concerned with error handling on name of file.

I appreciate any help

Code:
    Dim Filter As String, Title As String
    Dim result As String
    Dim FilterIndex As Integer
    Dim wbsource As Workbook
        
    Dim wbdest As Workbook
    Set wbdest = ActiveWorkbook
        Dim Filename As Variant
        FFilter = "Excel Files (*.xls),*.xls," & _
        "Text Files (*.txt),*.txt," & _
        "All Files (*.*),*.*"
' Default Filter to *.*
FilterIndex = 3
Title = "Select a File to Open"
ChDrive ("c")
ChDir ("C:\Documents\")
With Application
    ' ile Name to selected FileSet F
    Filename = .GetOpenFilename(Filter, FilterIndex, Title)
    ' Reset Start Drive/Path
    ChDrive (Left(.DefaultFilePath, 1))
    ChDir (.DefaultFilePath)
End With
' Exit on Cancel
If Filename = False Then
    MsgBox "No file was selected."
    Exit Sub
End If
Application.ScreenUpdating = False
    
    result = InputBox("Enter worksheet name")
    wbdest.Unprotect
Set wbsource = Workbooks.Open(Filename)
' Set wbsource.Sheets = result
' Set result = wbsource.Sheets(result)
wbsource.Sheets("result").Range("f8").Copy
wbdest.Sheets("data").Range("b4").PasteSpecial Paste:=xlPasteValues
 
Last edited:

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Hi there. If you remove the quotes from your second last line to read wbsource.Sheets(result).Range("f8").Copy
that should do it.
 
Upvote 0
Have you considered this flow chart

GetOpenFilename to select workbook
Open the workbook
Application.InputBox type:=8 to use mouse to select a cell on the source sheet

The type 8 input box removes the problem of misentering the worksheet name.
 
Upvote 0
Have you considered this flow chart

GetOpenFilename to select workbook
Open the workbook
Application.InputBox type:=8 to use mouse to select a cell on the source sheet

The type 8 input box removes the problem of misentering the worksheet name.

Removing the quotes worked. I tried this flow and get error on application line -- says argument not optional
 
Upvote 0
Those are not lines of code to be put in a sub. That was a flow chart to be converted to a VBA routine.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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