Copying Data from Workbook

I'm a VBA noob who's hopefully creating a workbook where the user chooses a file and sheet within this file and the main workbook copies data from this sheet from specific (known) cell ranges and worksheet into a sheet in the main workbook (in specific sheet and cell range).


The file is selected via a file explorer which opens on double clicking the appropriate cell in the main workbook. This returns the full file path as a string in this cell (e.g.
C:\ExcelTools\Workbook1.xls). I have set up a function to just get the file name (e.g. Workbook1.xls)

Currently the sheet has to be typed in manually (if anyone has any ideas how to extract sheet names from a file path then I would be grateful!)


I've been trying (and failing) to find a way to open the selected file, select a range of cells (e.g. A1:R190) from the user-specified sheet name and copy and paste them (or =Range("").Value them) into a range of cells (e.g. B2:S191) another named sheet of the main file.

I think I'm having an issue with getting the VBA to recognise the selected file and sheet name as they are as strings in cells. Their values may change as the user selects different files so I can't just put one specific file path into the module.

Any ideas? let me know if anything doesnt make sense!

In terms of copying and pasting i have no code yet

For the rest of it I essentially have 2 cells with strings containing a file path and a sheet name that the user will change

Good afternoon ScantilyClared

...and welcome to the forum!!

First things first. It sounds as though you need to tidy up your file open routine before you start identifying cells to copy. VBA provides a built in way of opening files using the standard Excel dialog box - capture the file using this and you don't need to worry about copying the filename into cells and splitting up the file name from the rest of the path and all that kind of thing.

The link below contains a file which demonstrates the command (GetOpenFilename) and also shows how to extract the sheet names from a closed file. Sort this bit out first, then come back with your questions about copying ranges.
http://www.appspro.com/Downloads/GetSheetNames.zip

HTH

DominicB