VBA - Prompt User to Open a Workbook and to Select a Worksheet, But Opens an blank file

[Praveen_k_g]

I would like the User to choose a Workbook and then select the worksheet that they need. The code works perfectly when it is Debug - Step Into. But, When the complete macro is run through the button, the file do get open and prompts to choose the sheet but no sheets or cells are visible. It's all BLANK. There is no protection to the file. Column names and Row numbers are not visible


ans = MsgBox("Choose the file to retrive the data?", vbYesNo, "Choose Source")

If ans = vbYes Then

myfile = Application.GetOpenFilename(, , "Browse for Workbook")

If myfile <> False Then

ThisWorkbook.Sheets("Reference").Range("AA2") = myfile

Set src_data = Workbooks.Open(myfile)

SheetName = Application.InputBox("Choose a cell in Desired Sheet ", "Select Sheet",Type:=8).Worksheet.Name
sht = SheetName

[Richard Buttrey]

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context. Your code is in any case missing End If statements

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

In addition would you change your location to something meaningful. This often helps if we need to consider regional/local settings.

[Praveen_k_g]

The End If statements are in full code, I assumed this part of the code was giving me the issue so I uploaded just that part. My bad!

I have attached 2 files, Destination and Source.

When i run trhough the code in Step Into mode, it works right on point. But, when the complete macro is run - file do get open, prompts user to write the sheet name. The sheets, rows, columns everything is blank (Grey zone).

Sub LoadData()
Dim ws As Worksheet
Dim desiredSheetName As String
Dim c As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

ans = MsgBox("Choose the file to retrive the data?", vbYesNo, "Choose Source")
If ans = vbYes Then
myfile = Application.GetOpenFilename(, , "Browse for Workbook")
If myfile <> False Then
ThisWorkbook.Sheets("Destination").Range("AA2") = myfile
Set src_data = Workbooks.Open(myfile)

On Error Resume Next
desiredSheetName = InputBox("Select any cell inside the target sheet: ",type:=8).worksheet.name ' Here i tried with .parent also
sht = desiredSheetName
On Error GoTo 0

Set dest = ThisWorkbook.Worksheets("Destination")

src_data.Activate

'Get the Last Row Details of Source and Destination workbooks
lastcell = src_data.Sheets(sht).Cells(Rows.Count, "C").End(xlUp).Row
LastRowD = dest.Cells(dest.Rows.Count, "F").End(xlUp).Offset(0).Row

src_data.Activate
Sheets(sht).Select
Range("A:B,D:D").Select
Selection.Copy
dest.Activate
Range("F1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipblanks:=False, Transpose:=False

src_data.Close False

dest.Select

End If
Else
Exit Sub
End If

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub