VBA code that references a worksheet [given URL] , then copies and pastes desired range

[macrolearning]

Hi everybody,

First of all, thank you all for your previous collaboration. I am currently engaging more with Excel [2010] and VBA, and with the stumbles along the way, your answers to other people's questions have been great answers to my questions as well. Usually with the Record Macro feature and all of your advice, I manage to learn some new VBA and get some nice projects done. I wouldn't have created this account if it wasn't because I could not find this topic discussed anywhere:


I am working with a lot of worksheets that have a heavy amount of formulas, and the intention is to create a macro that finds a workbook, and copies and pastes a range into another.

Reading the file location URL as an input in a cell is my greatest struggle. Is this possible with VBA? Or does the explicit name of the workbook have to be placed into VBA each time and not into a cell?



TLDR; VBA copy and paste from a new workbook mentioned in a cell from range defined on cells.

Here is an example image in case this explains my problem better:

[TABLE="width: 632"]
<tbody>[TR]
[TD="colspan: 4"]Template[/TD]
[TD][/TD]
[TD="colspan: 4"]Example[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]File Location[/TD]
[TD][/TD]
[TD="colspan: 4"]File Location[/TD]
[/TR]
[TR]
[TD="colspan: 4"][URL OF FILE WOULD GO HERE][/TD]
[TD][/TD]
[TD="colspan: 4"]C:\Users\Public\Pictures\Sample Pictures[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]Range Desired[/TD]
[TD][/TD]
[TD="colspan: 4"]Range Desired[/TD]
[/TR]
[TR]
[TD]From Column[/TD]
[TD]From Row[/TD]
[TD]To Column[/TD]
[TD]To Row[/TD]
[TD][/TD]
[TD]From Column[/TD]
[TD]From Row[/TD]
[TD]To Column[/TD]
[TD]To Row[/TD]
[/TR]
[TR]
[TD="colspan: 4"][Identifying numbers/letters go here][/TD]
[TD][/TD]
[TD]B[/TD]
[TD]5[/TD]
[TD]M[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thanks ahead of time, guys!

 

[JLGWhiz]

The first thing is to use terminology that applies to Excel. URL is an internet term which stands for "Uniform Resource Locator" and applies to such things a site addresses or email addresses. I think you might be using it here in reference to "Path" which is a Parent child sequence from Drive, Directory and subdirectories hierarchical order that defines where a file resides. If you are dealing with only one path, then you can simply define it once in your code as:
example:

myPath = ThisWorkbook.Path 'If all workbooks in the same directory as the one which hosts the code.

Or

fPath = "C:\DeskTop\xlFiles\myProjects" 'If different than the host workbook.

The 'myPath' and 'fPath' variable names bear no significance. You can use any name you want in either place, since it is simply a device to hold the string value of the actual path.
If you have more than one directory to use, then you can either list them with their corresponding file names in worksheet columns and then refer to them in code. Assume file names in Column A and paths in Column B for five files and five directories.

For i = 1 To 5
 With ActiveSheet
  myFile = .Cells(i, 1).Value 'get file name from col A
  myPath = .Cells(i, 2).Value 'get path from col B
  Workbooks.Open(myPath & "\" & myFile) 'concatenate variables with separator inserted to open files.
  'Code to do stuff here
  ActiveWorkbook.Close True
 End With
Next

For more discussion on the subject, you can switch over to the discussions forum or browse the Q&A page, which also has numerous references to this subject.