Macro to copy a Relative Address across a range and then to convert those Relative Addresses to Absolute Addresses|VBA & Macros|Excel Forum|My Online Training Hub

I have attached a small demo of an large reporting worksheet and would be grateful for help.

 

I have needed to update the spreadsheet as the program generating the original input data, in its new version, generates a wider range of useful information, which is input to the reporting worksheet.

 

I am trying to reduce the editing and relinking work - the attached Worksheet contains the demo and troubleshooting Macro's.

 

We import the data, tidy it up and when in the correct structure, copy it into the respective entry point Tab in the reporting worksheet. This then feeds the reporting template. For each section we have a respective Tab of Reporting.

 

In reconstructing, I am linking the reporting section Relative Address, copying these for "Probability and Impact" across a range of 5 further sequential columns, linking them to their data "Relatively". I then convert the addresses to absolute.

 

The Macro has been structured to automate this and save key strokes.

 

 

The example is attached in its clean format with 3 Prob / Impact sets of Rows where the first column is linked Relatively

 

A relative duplicate is placed below to use in copying back across the cells to restore original state and blank cells copied to clean up - the process can recommence again for testing

 

The Macro was split into its two component macro parts to assist in trouble shooting.

 

Copying the Relative Cell Address across the range from the start point in both “original position” where the Macro was created and in subsequent positions works fine. = Cntrl+r

 

The second component of then converting the Relative to Absolute, results in the Cells receiving the first range of Absolute addressing = Cntrl+t - does not work. When this Macro runs it converts all to Absolute but it gives each cell the Absolute addresses that were used in the first instance when creating the Macro?? and not those that were created correctly from Cntrl+r and relative to the Address inserted and correctly copied across the range. So it does not start from a new position and run its set of commands correctly.

 

At this stage I do not know VBA and have used the Macro Recorder to do this. 

 

I would be grateful for assistance in resolving and consolidating into one single Macro

 

Kind regards

Gavin