Copy and Paste technique, referencing variables within cells

I currently receive a number of reports, that are preformated in XLSX format from a separate reporting tool. These form our source of truth, of which I've developed a number of audit templates to provide exceptions reporting.

The only area I'm currently struggling with, is automating the collection of the input data. That is, automatically bringing the above-mentioned Excel data into my spreadsheets.

I'll explain the process for input, and look forward to some feedback from the guru's within the forum:


1. Open a file based upon worksheet showing Variables
1a. Source UNC Path = (e.g. \\SERVER\Path\)
1b. Source File Name = (e.g. FILENAME.xlsx)
1c. Source Worksheet = (e.g. Sheet1
1d. Source Cell = (e.g. A8)
1e. Destination Worksheet = (e.g. "RAW DATA - CMDB")
1f. Destination Cell = M2

2. Based upon steps 1a to 1d, select the entire range of data from A8.
NOTE: I have found some code which works quite fine for this.
For the purposes of this example, let's say cells A8-C10

3. Based upon steps 1e to 1f, paste the copied data

4. Repeat steps 2 to 3, for all entries within the table within Step 1.


I've spent quite some time investigating such a solution, to no evail. Your assistance would be extremely appreciated.

Is it just the pasting that you are having trouble with? Which cells will you be copying, and to where will you be pasting them, when you repeat the process?

Originally Posted by djdjdj

Is it just the pasting that you are having trouble with? Which cells will you be copying, and to where will you be pasting them, when you repeat the process?

Thanks for asking djdjdj,

YYYY-MM-DD Copy Paste from Cell Values_v0.21.xlsx
5516.xlsx
ALL-TADDMInventory.xlsx

I guess I'm struggling in most of the points. Putting the whole copy and paste into VBA that loops.

I've mocked up a set of 3 files (and attached) as a basic example of the Template (YYYY-MM...xlsx) and the source data files (5516.xlsx and All-TADDM....xlsx).

* YYYY-MM-DD Copy Paste from Cell Values_v0.21.xlsx -->
The basic setup of the template file is that I have
- "Raw Data..." worksheets which will contain some formulas to the left (yellow highlighted fields) and the destination for the source data files to the right (blue highlighted fields); and
- "Variables" worksheet containing all the attributes I've described in my original post. Below, I've also added the actual content contained in this worksheet as attached:

Source UNC Path: \\media-pc\N\Temp\ , \\media-pc\N\Temp\
Source File Name: 5516.xlsx , ALL-TADDMInventory.xlsx
Source Worksheet: CI Details_4 , Data
Source Cell: A8 , A2
Destination Worksheet: Raw Data - CMDB Dump , Raw Data - TADDM Dump
Destination Cell: K2 , H2


Hopefully this makes sense, and I look forward to your guidance.