VBA - Create a new sheet with shifting references

Hi All,

This is the first time posting here.

I am looking to create a VBA code that would be able to create a new sheet based on a modular template/reference sheet.

Essentially what I want it to do is to have a button I press that would create a new column on the input sheet and a new sheet that's based on the new input column and an existing template. This template sheet will have several formulas that will reference the first column on the input sheet and also serves as the first sheet. Each new sheet I want to generate will also need to reference the input sheet but will be shifted one cell to the right. So essentially, a button will be at the location of the last input column and when pressed, will create a new sheet, reference the new column created and the button will then shift over as well.

The tricky part is this. I would like this file to be used in the future for different purposes. I would like to be able to change the template sheet (ie add new rows and formulas) and have the VBA be able to pick that up and shift references.

I haven't dive too deeply into it yet but I am stuck on how to copy a formula I typed on the sheet into VBA and modifying it (shift cell reference to the right) and retype it back. I'm planning to just read and store all the formulas and shift everything that references the input sheet to the right. Just not how to do this. Another issue was I am not sure what the most efficient way to create a new sheet from a template would be.

Here is an example I was thinking of.

Input sheet:

	A	B
1	Cool Sheet 1	Bad Sheet 2
2	1	34
3	8	3422
4	34	3411
		Click for New Sheet

Template sheet:

Sheet Title: Cool Sheet 1

Cell 1: A2+A3

Cell 2: A2*A4

Cell 3: A3*A3



Example new Sheet 1

Sheet Title: Bad Sheet 2

Cell 1: B2+B3

Cell 2: B2*B4

Cell 3: B3*B3


When the New sheet button is pressed, a new column is generated and requires the user to input a new set of information. A new sheet is also created that will reference the new set of numbers

	A	B	C
1	Cool Sheet 1	Bad Sheet 2	Meh Sheet
2	1	34
3	8	3422
4	34	3411
			Click for New Sheet

Example new Sheet 2

Sheet Title: Meh Sheet

Cell 1: C2+C3

Cell 2: C2*C4

Cell 3: C3*C3




Hope someone can help me out.

Thanks!

Thanks Karmapala,

This is pretty much what I wanted to do. The formulas will probably be quite a bit more complicated but I think that would still work. Reading the sample code, it looks like it takes the template file and allow me to type values with input box. then it generates a new sheet after the old sheet, and replaces all the references that say "!A" with the new reference column.

I'm not too familiar with .Address but I'm assuming that it returns the column letter. If I change up my template and it only references column B, could I just replace the !A with !B and still have it working?

I didn't expect the code to be so short. I guess in my head it seems much more complicated than it actually is.

I'll give it a try tonight and see if it does what I think it does.

Originally Posted by kytung

Thanks Karmapala,
Reading the sample code, it looks like it takes the template file and allow me to type values with input box.
then it generates a new sheet after the old sheet,
and replaces all the references that say "!A" with the new reference column.

YEs you are correct.
In other words, sheet "Input" column A is just for the sheet "Template" reference.

I'm not too familiar with .Address but I'm assuming that it returns the column letter.

Correct.

If I change up my template and it only references column B, could I just replace the !A with !B and still have it working?

I haven't tried it yet. But logically, yes it should still run if the formula in sheet "Template" column A is referencing
to column B in sheet "Input".

So assumed that sheet "Template" formula reference is to column B (after row 1) of sheet "Input",
the next new column will be column C of sheet "Input", where row 1 is the name of the new sheet,
and the rest of the rows is the value the user input.

Just remember that you need to change this line :

Please Login or Register  to view this content.

into

Please Login or Register  to view this content.

So, when you run the code, it will replace the !B to !C.
If you don't change that line (it's still !A), the code won't work
because it won't find "!A" in the newly_created_sheet_with_the_pasted_formula_from_sheet_template
to be replaced with "!C".

I didn't expect the code to be so short.
I guess in my head it seems much more complicated than it actually is.

I think as long as the formula in sheet "Template" is always referencing to whatever_column in sheet "Input",
and the line

Please Login or Register  to view this content.

is referencing to What:="!whatever_column",
then I think the code should still work no matter how complicated is the formula in sheet "Template".

I'll give it a try tonight and see if it does what I think it does.

Please do.
I hope the code still can give you the result as you expected.

I tried this out and tinkered with some of the codes and I think I got it working as I wanted it to. Thank you so much!

I was also able to change the column references from !A to !B and it worked just as well.

I do have one more additional follow-up question. Is there a way to also copy over the format while copying the formulas over?