Change range of column in macro, when new row is added.

[Alcotraz]

Hello guys,


I have a problem, which could be solved in many different ways I believe , but I am new to VBA codes and I would like you to help me with it or at least direct me in the right path.


I have Application Document with 6 sheets which is grouped in 3 parts, 1-2 ( Instruction), 3-4 ( Application) 5-6 ( Certificate) . One page refers to data, and other to the summary.


So I need to instruct people of the work they need to do, so I write Instruction In Sheet2. Columns A to F, which when copies to Sheet4 - A to F , Column H is for applicant to fill how percentage of worked they done, which is when calculated and certified in Sheet6. Once this document is Certified and will send it back to applicants, to fill another application again, so if he wrote 20 % which he has been paid for, he will ask for 50 % , and rewrites in Sheet4 Column H 50% .
Which is been certified ( sometimes 50, or less) and the process repeats itself.


The issue I have now , that I want to see the previous Certified amount or percentage at least in the document once it is certified.


I thought of 2 solutions:


1. Referencing data from Previous document which is saved in the same location. ( So for example Document Application2.01.01.15.xlsx would find his previous buddy in same location, Application1.01.12.14 and would certified Column, to new application, application3 would do find application2 and would copy that column again. The problem that I am facing is that I do keep chaning dates, and there is no static name, I was wondering if I can reference a document with partly known information in same folder, for example it would look not for the date but text application1 in the same location there the active file is ?


2. As I think first option is cannot be done, I was thinking of making a new column in the same document and run a macro code. so once value is certified, I would press button , which would copy Certified amount to application sheet2 new column G which would be previous % . and clear contents of column H in Sheet2 so applicant could make a new entry. , the problem I am facing there now that new rows in instruction sheet, or application sheet could be added and my macro code would not copy new entries and I dont want to change the code each time as I might have 100 applications each month. Data from A to F in Sheet2 is also divided into 3 sections, so there are blanks and rows which needs to be taken into account.


I will draw the basic example below and attach the document for your review.

A B C D E F G H I
[TABLE="width: 500"]
<tbody>[TR]
[TD]Code[/TD]
[TD]Description[/TD]
[TD]Quantity[/TD]
[TD]Unit[/TD]
[TD]Rate[/TD]
[TD]Value[/TD]
[TD]Previous %[/TD]
[TD]Entry %[/TD]
[TD]Total £[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Roof works[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]£1000[/TD]
[TD]£100[/TD]
[TD][/TD]
[TD]10%[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Drainage[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]$2000[/TD]
[TD]£2000[/TD]
[TD]50%[/TD]
[TD]50%[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Total[/TD]
[TD]1100[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Snagging[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]£1000[/TD]
[TD]£1000[/TD]
[TD][/TD]
[TD]10%[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Total Variations[/TD]
[TD]100[/TD]
[/TR]
</tbody>[/TABLE]


This is an example of the table ( Sheet6) that I have, so I only need to copy H values which has percentages ( excl format) to Sheet2 G Column , and Clear H in Sheet2 for new entry, but my macro range needs to be changed once I add new rows in the Sheet2.

Please find link below to download original document.

INSTRUCTION TEMPLATE- TEST2.xlsx - DocDroid

I would appreciate of any help for this matter.