Macro to Find correct File, Copy a Specific Tab and Close file

[dstaggs]

Hello All,

I am trying to write a Macro to prompt the user to select a file, open the selected file and copy a specific tab into my existing worksheet. I need this because the tab I need to copy will change every month. I am using the below code to prompt the user but I cannot figure out how to make it actually open and copy the tab I need. Any help would be appreciated.

Private Sub CommandButton1_Click()
Dim fDialog As FileDialog, result As Integer, it As Variant
Set fDialog = Application.FileDialog(msoFileDialogOpen)

'Optional: FileDialog properties
fDialog.Title = "Select a file"
fDialog.InitialFileName = "http://Filename.xlsx"

'Optional: Add filters
fDialog.Filters.Clear
fDialog.Filters.Add "All files", "*.*"
fDialog.Filters.Add "Excel files", "*.xlsx"

If fDialog.Show = -1 Then
Debug.Print fDialog.SelectedItems(1)
End If
Application.ScreenUpdating = False
Application.DisplayAlerts = False



End Sub

[Keebellah]

Hi, welcome to the forum, If I look at the code (you forgot the code tags, read the forum's rules/instructions to see how this is done) I am assuming you have not used it since it does not even open the file. and why the URL reference? Is the file on a shared drive or a web page?
The Tab you speak of I assume is a worksheet in that file that you want the user to open.
Where in your file do you want to copy this worksheet? Into a new one? Replace the contents of an existing Worksheet?
Will the file with this macro be the same one as the one where you are saving the worksheet to?
Which version of Excel?

[dstaggs]

Hello,
Thank you for your quick reply. Apologies on my delayed response as well as not using the correct code tags. I have been out of the office for the last week. I will do my best to answer all your questions. I am very new to Macros.
1) No. I have not been able to use the code at all.
2) Yes. The file is stored on our company Web Portal.
3) Yes. Tab = Worksheet. Apologies for not using the correct.
4) I would like the copied worksheet to replace the contents of an existing worksheet.
5) Yes. the Macro will be in the workbook that I wish to copy the worksheet to.
6) I am using Excel 2010.

Thank you in advance for your help.

[Alf]

Depending a bit of how you wish user to select file there are several options but in this macro I just hard coded folder and file name. I also assume that relevant information is found on sheet1 in the file you wish to open.

Macro starts by clearing active sheet in the workbook where you have added the macro. This workbook is known to the macro as "ThisWorkbook"

Please Login or Register  to view this content.

After file is opened rellevant aheet is activated and all information copied to "ThisWorkbook" and then the opened file is closed without saving any canges. There is also the option of making a copy of the sheet of interest and exporting it to "ThisWorkbbok". A thing to consider if the sheet you wish to copy has some very special formating that you would like to keep.

Alf

[dstaggs]

Hello,

I need the user to be able to select a specific file so hard coding the file name and folder will not work.

Thanks,

[Alf]

Your are welcome but consider that a proposed solution depends very much on the information given.

Alf

Ps the magic word you are looking for is perhaps "Application.Dialogs(xlDialogOpen).Show"

[dstaggs]

Hello All,
Here is the solution I was able to piece together. It seems to be working fine. Feedback appreciated.

Please Login or Register  to view this content.