Hi All,
I'm looking to automate a document that we send out to our customers. Basically it used to be a word doc that contained a whole bunch of descriptions and instructions about out product. This document gets altered to suit for each job we complete which takes a lot of time. Using excel and VBA I'm trying to make the whole thing go a bit faster. Basically what I'm doing is I have an 'Input' sheet where all the options are present in a tree form and then you put a '1' next to it if its there. That '1' hides or unhides entire rows in the 'Content' sheet.
The way I've done this is to first off set the locations of both the '1' and the content that that 1 refers to. Like this:
'This line refers to the cell that gets a '1' in it:
Set headUpperSwivelLock = Sheets("Input").Range("J7")
'This line sets the location of the related content:
Set contHeadUpperSwivelLock = Sheets("Content").Rows("101")
this is then turned on and off by an IF statement that depends on the 1.
This working great but I'm concerned that later on I'm going to want to come in and add extra information in. That means that I would have to add extra rows into the middle of the content data. This is obviously going to screw up any references that are below where I have added rows as VBA isn't aware of what I've done.
Finally my question. Is it possible to have VBA locations linked to a sheet so that if you move data in that sheet the reference gets update? i.e. like it works in excel where if you cut and paste a cell the formulas relating to it will update with the new reference.
I'm trying to get around this by adding a whole bunch of spare empty rows throughout the doc that get hidden at the end of a script if they're empty. Not a very elegant solution though.
Thanks for any help!
Best,
Nick
I'm looking to automate a document that we send out to our customers. Basically it used to be a word doc that contained a whole bunch of descriptions and instructions about out product. This document gets altered to suit for each job we complete which takes a lot of time. Using excel and VBA I'm trying to make the whole thing go a bit faster. Basically what I'm doing is I have an 'Input' sheet where all the options are present in a tree form and then you put a '1' next to it if its there. That '1' hides or unhides entire rows in the 'Content' sheet.
The way I've done this is to first off set the locations of both the '1' and the content that that 1 refers to. Like this:
'This line refers to the cell that gets a '1' in it:
Set headUpperSwivelLock = Sheets("Input").Range("J7")
'This line sets the location of the related content:
Set contHeadUpperSwivelLock = Sheets("Content").Rows("101")
this is then turned on and off by an IF statement that depends on the 1.
This working great but I'm concerned that later on I'm going to want to come in and add extra information in. That means that I would have to add extra rows into the middle of the content data. This is obviously going to screw up any references that are below where I have added rows as VBA isn't aware of what I've done.
Finally my question. Is it possible to have VBA locations linked to a sheet so that if you move data in that sheet the reference gets update? i.e. like it works in excel where if you cut and paste a cell the formulas relating to it will update with the new reference.
I'm trying to get around this by adding a whole bunch of spare empty rows throughout the doc that get hidden at the end of a script if they're empty. Not a very elegant solution though.
Thanks for any help!
Best,
Nick
