Macro to add rows depending on the size of data volume and create new workbook

[bimmy80]

There are 2 sheets Main File and DBR Report.

Main File is where the data is pasted.Since data is generated for 2 clients (code is AA and US) it contains 2 buttons.

DBR Report contains Template into which data is pasted into specific columns from Main File.

In short, when either of the buttons are clicked, data from Main File is pasted into specific columns in the Template.

When AA button is clicked, data is pasted into the template and column Q is updated with code AA.
Same thing happens when US button is clicked, only column Q is updated with code US.

Below is the code that does this partly -

Please Login or Register  to view this content.

There are 3 columns in the template which are already updated with certain data

Column A
FR:
TO:

Column B
BA
BA

Column Q
AA
AA

Looking for few additions to the above code as below :

1)Depending on the volume of data in the Main File the macro should add
or deduct rows from the template format.

2)Columns A, B and Q should be duplicated one below the other depending
on the volume of data in the Main File.

3)Macro should create an individual workbook of DBR Report sheet
(where the template is)

4)The new workbook should be named as AA_REACC_Draft_20Apr,
the sheet tab should also reflect as AA_REACC_Draft_20Apr.

5)Since the name contains date, macro should rename the workbook as
well as the sheet tab with date when the macro is run.
Ex :
a)When macro is run on 21Apr,workbook and sheet tab
should be named as AA_REACC_Draft_21Apr
b)When macro is run on 30Apr, workbook and sheet tab
should be named as AA_REACC_Draft_30Apr

6)The new workbook will be saved where the macro is kept.

The above addition should be achieved when AA or US buttons are clicked.
(Any Idea for achieving point 5 is welcome)

Attaching sample sheet with few explanations.

[ARowbot]

If you start with your template to have 2 rows initially then this will do most of what you have requested. Unfortunately you cannot name a Workbook without saving it (To my knowledge)

Please Login or Register  to view this content.

[bimmy80]

Should the above code be pasted over the original code.

I tried, when I clicked on the button got below error

Compile error :
Sub or function not defined

[ARowbot]

In my code there is a line that says

Please Login or Register  to view this content.

This is where your code should go. (you need to define the variable colQ btw!)

EDIT: I just realised you are passing it colQ so it doesn't need defining! The only other thing wrong that I can see is with the save file, Both are saved as AA...... I am assuming the US button should save it as US.... But hopefully that should be an easy fix for you...if not let me know!

[bimmy80]

I'm new to macro so PLEASE help me out here.

Pasted macro as suggested above. But unable to define the variable colQ

Please Login or Register  to view this content.

[ARowbot]

Apologies, I edited my post just as you were posting!

EDIT: I just realised you are passing it colQ so it doesn't need defining! The only other thing wrong that I can see is with the save file, Both are saved as AA...... I am assuming the US button should save it as US.... But hopefully that should be an easy fix for you...if not let me know!

The latest code you have pasted seems to work for me! (But call it test instead of bimmy since that's what the buttons are calling!)

[bimmy80]

I removed the below code

Please Login or Register  to view this content.

And it works partly.

Can you HELP me out with the buttons. (If you can attach the final sample sheet it would be GR8)

EDIT :
When clicked on Button AA it should do all the above activity and also update column Q with AA. Similary for Button US

[ARowbot]

All Problems should now be fixed. Enjoy:

EDIT: It currently saves it to my dropbox which obviously you don't have access to: so you will need to change that bit of the code

[bimmy80]

Exactly what I'm looking for.

THANKS A TONNE... Especially for your patience.

[ARowbot]

You're welcome. If all the problems are now solved then please mark the thread as solved using thread tools at the top.

And If I have helped then any reputation you may wish to give would also be appreciated