Can i create an macro to generate new excel spreadsheet based on the base data?

Alpana · Jun 13, 2012

Hi,

I am very new to VBA and macros.This website really helped me in pick up some points regarding these topics.

Query:

I have 2 excels(base and reference) from which i need the data to be transferred to a new excel sheet.

For example,Excel A is base data and Excel B is reference data, so i want a macro which looks base data in reference data and gives me new data in the new excel sheet.

Chandoo and all the VBA/Macro experts can you please help me defying the macro for this ?

The macro i recorded is something as below:

I KNOW ITS NOT COMPLETE AND UNCLEAR AND COULDNT ATTACH THE SPREADSHEET TO EXPLAIN CLEARLY.

ActiveCell.FormulaR1C1 = "='FP space'!RC[-2]"
Range("C9&#34

.Select
ActiveCell.FormulaR1C1 = "='FP space'!R[-7]C[-1]"
Range("C10&#34

.Select
ActiveCell.FormulaR1C1 = "='FP space'!R[-8]C"
Range("C11&#34

.Select
Sheets("Front Sheet&#34

.Select
Sheets.Add
ActiveCell.FormulaR1C1 = "Store no"
Range("B1&#34

.Select
ActiveCell.FormulaR1C1 = "Merch group"
Range("C1&#34

.Select
ActiveCell.FormulaR1C1 = "Space"
Columns("C:C&#34

.Select
Columns("B:B&#34

.EntireColumn.AutoFit
Range("A2&#34

.Select
ActiveCell.FormulaR1C1 = _
"=IF('Front Sheet'!R[20]C[10]='Front Sheet'!R[8]C[2],'Front Sheet'!RC[2],""NA"&#34

"
Range("A2&#34

.Select
Selection.Copy
Application.CutCopyMode = False
Range("A2&#34

.Select
Selection.AutoFill Destination:=Range("A2:C2&#34

, Type:=xlFillDefault
Range("A2:C2&#34

.Select
Range("B2&#34

.Select
ActiveCell.FormulaR1C1 = _
"=IF('Front Sheet'!R[20]C[10]='Front Sheet'!R[8]C[2],'Front Sheet'!R[9]C[2],""NA"&#34

"
Range("B2&#34

.Select
ActiveCell.FormulaR1C1 = _
"=IF('Front Sheet'!R[20]C[10]='Front Sheet'!R[8]C[1],'Front Sheet'!R[9]C[2],""NA"&#34

"
Range("B2&#34

.Select
ActiveCell.FormulaR1C1 = _
"=IF('Front Sheet'!R[20]C[9]='Front Sheet'!R[8]C[1],'Front Sheet'!R[9]C[2],""NA"&#34

"
Range("A2&#34

.Select
ActiveCell.FormulaR1C1 = _
"=IF('Front Sheet'!R22C11='Front Sheet'!R10C3,'Front Sheet'!R2C3,""NA"&#34

"
Range("B2&#34

.Select
ActiveCell.FormulaR1C1 = _
"=IF('Front Sheet'!R22C11='Front Sheet'!R10C3,'Front Sheet'!R11C4,""NA"&#34

"
Range("B2&#34

.Select
Selection.AutoFill Destination:=Range("B2:C2&#34

, Type:=xlFillDefault
Range("B2:C2&#34

.Select
Range("C2&#34

.Select
ActiveCell.FormulaR1C1 = _
"=IF('Front Sheet'!R22C11='Front Sheet'!R10C3,'Front Sheet'!R[9]C[8],""NA"&#34

"
Range("A2:C2&#34

.Select
Selection.AutoFill Destination:=Range("A2:C12&#34

, Type:=xlFillDefault
Range("A2:C12&#34

.Select
Range("B2&#34

.Select
ActiveCell.FormulaR1C1 = _
"=IF('Front Sheet'!R22C11='Front Sheet'!R10C3,'Front Sheet'!R[9]C4,""NA"&#34

"
Range("B2&#34

.Select
Selection.AutoFill Destination:=Range("B2:B11&#34

, Type:=xlFillDefault
Range("B2:B11&#34

.Select
Selection.AutoFill Destination:=Range("B2:B12&#34

, Type:=xlFillDefault
Range("B2:B12&#34

.Select
Sheets("Sheet2&#34

.Select
Range("C2&#34

.Select
ActiveCell.FormulaR1C1 = _
"=IF('Front Sheet'!R22C11='Front Sheet'!R10C3,'Front Sheet'!R[9]C11,""NA"&#34

"
Range("C2&#34

.Select
Selection.AutoFill Destination:=Range("C2:C12&#34

, Type:=xlFillDefault
Range("C2:C12&#34

.Select
Range("A1&#34

.Select
Sheets("Front Sheet&#34

.Select
Range("C2&#34

.Select
ActiveCell.FormulaR1C1 = "='FP space'!R[1]C[-2]"
Range("C9&#34

.Select
ActiveCell.FormulaR1C1 = "='FP space'!R[-6]C[-1]"
Range("C10&#34

.Select
ActiveCell.FormulaR1C1 = "='FP space'!R[-7]C"
Range("C10&#34

.Select
ActiveCell.FormulaR1C1 = "='FP space'!R[-7]C"
Range("C11&#34

.Select
ActiveSheet.ClearArrows
Range("C2&#34

.Select
ActiveCell.FormulaR1C1 = "2007"
Range("C9&#34

.Select
ActiveCell.FormulaR1C1 = "1"
Range("C10&#34

.Select
ActiveCell.FormulaR1C1 = "14"
Range("C11&#34

.Select
ActiveSheet.ClearArrows

SirJB7 · Jun 13, 2012

Hi, Alpana!

First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.

Give a look at the second green sticky post at this forums main page for uploading guidelines. It will be easier for people to help you if you provide a sample file. Could you?

Regards!

SirJB7 · Jun 13, 2012

Hi, Alpana!

First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.

Give a look at the second green sticky post at this forums main page for uploading guidelines. It would be easier for people who might help you if you provide a sample file and detailed explanations. Could you?

Regards!

SirJB7 · Jun 13, 2012

Hi, Alpana!

First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.

Give a look at the second green sticky post at this forums main page for uploading guidelines. It would be easier for people who might help you if you provide a sample file and detailed explanations. Could you?

Regards!

Alpana · Jun 13, 2012

Hi,

Thanks for the update.I will try to load the file.

Regards

Alpana

Alpana · Jun 13, 2012

Hi,

You can access the file from the below link,where i need help on.

Have explained in the spreadsheet what i need actually.

Could you please help me on this?

Alpana · Jun 13, 2012

sorry , deleted the link as i have some confidential data,can you please suggest me how can i share the documents when its confidential.

Regards,

Alpana.

SirJB7 · Jun 13, 2012

Hi, Alpana!

Give a look at the second green sticky post at this forums main page for uploading guidelines. There you'll find instructions to avoid sending sensitive data.

Regards!

Luke M · Jun 14, 2012

Here's my best guess as re-writing your macro. Near as I could tell, you are setting up a new sheet with some formulas, and there was also something about changing values on Front Sheet. Please advise as to what else you would like to happen, or if this is completely wrong.

[pre]

Code:

Sub Rewritten()
Dim NewSheet As Worksheet

&#39;setup new sheet
Set NewSheet = Sheets.Add
With NewSheet
.Range(&#34;A1&#34;) = &#34;Store no&#34;
.Range(&#34;B1&#34;) = &#34;Merch group&#34;
.Range(&#34;C1&#34;) = &#34;Space&#34;
.Range(&#34;A2&#34;).Formula = &#34;=IF(&#39;Front Sheet&#39;!$K$22=&#39;Front Sheet&#39;!$C$10,&#39;Front Sheet&#39;!$C2,&#34;&#34;NA&#34;&#34;)&#34;
.Range(&#34;B2&#34;).Formula = &#34;=IF(&#39;Front Sheet&#39;!$K$22=&#39;Front Sheet&#39;!$C$10,&#39;Front Sheet&#39;!$D2,&#34;&#34;NA&#34;&#34;)&#34;
.Range(&#34;C2&#34;).Formula = &#34;=IF(&#39;Front Sheet&#39;!$K$22=&#39;Front Sheet&#39;!$C$10,&#39;Front Sheet&#39;!$K2,&#34;&#34;NA&#34;&#34;)&#34;
.Range(&#34;A2:C12&#34;).FillDown
End With

&#39;We&#39;re doing something on Front sheet...not sure what
With Worksheets(&#34;Front Sheet&#34;)
.Range(&#34;C2&#34;) = 2007
.Range(&#34;C9&#34;) = 1
.Range(&#34;C10&#34;) = 14
End With
End Sub

[/pre]

Alpana · Jun 17, 2012

Hi Luke,

Thanks for the code,but i am not sure that its gives me the solution.

I have uploaded an example file in the below link.Would be helpful if you can help me with the code,as i am new to VBA.

https://docs.google.com/spreadsheet/ccc?key=0Ak5Dn7bzFTlZdGM4ZVhxeHRmWmhoYjQxMnF1VVRCUmc#gid=0b3

Given the detailed explanation in the excel.

Once,you open the link can download the file option.

Thanks for the help

Alpana.

Luke M · Jun 18, 2012

Afraid I can't access uploaded workbooks at my location, but hopefully a fellow forum member will see this and help out...

Alpana · Jun 18, 2012

Hi Luke,

Thanks for the reply.

It would be greatful if any of the members can help me out with this.

Or let me know if i can send you the file in anyother possible way.

Alpana.

Hui · Jun 18, 2012

Try this version: https://www.dropbox.com/s/t8n07mjc63phakz/Tool.xls

Alpana · Jun 18, 2012

Thanks hui for uploading the file in the drop box , hope i get some help like this for the code .

Excel experts - please help me out on this , it eases my work a lot.

Let me know if any more information is needed.

Alpana · Jun 19, 2012

Hi All,

Could any one get this?Please update if you have an solution.

Alpana.

Luke M · Jun 19, 2012

Did Hui's solution not solve your problem?

Alpana · Jun 19, 2012

I am sorry, couldnt find the solution in the file.

Guess it the same base data which is attached in the first link.

Alpana · Jun 20, 2012

Hi,

Any luck with the macro.

Sorry for keeping so many reminder mails,thought it shouldnt miss out in the forum.

Alpana · Jun 21, 2012

hi all,

I am not sure if anyone is able to access the file,give an explanation brief below.it would be helpful if you can give me a solution.

In a workbook1 i have 2 worksheets (i.e, sheet1,sheet2)

Sheet1 is linked to several data , if I enter some data manually into the Sheet1, then its takes references from the linked data and gives me output in different cells.The data i enter manually into sheet1 is taken from sheet2.

Example:

In sheet1 , I enter data maually in B2,B3,B4 taking from sheet2 i.e, A2,B2,C2.

When i fill up B2,B3,B4 in sheet1, it generates me certain values in E5:E10.

Theerefore,the values generated in E5:E10 , should copy to a different sheet ie, sheet3

if sum(B2,B3) = sum(E5:E10) .

In this manner, sheet2 contains data in Columns A:C until 900 rows.

So i am trying to workout a macro which inserts the numbers from sheet2 A,B,C (one after the other) into sheet1 B2,B3,& B4 and copies the data generated in K11:K21 into sheet3(new sheet) - one after the other.

My output data will look something like below:

AA BB CC

2006 Apple 0.0

2006 Mango 0.0

2006 Pineapple 0.0

2006 Orange 0.0

2006 Sweets 2.0

2006 Banana 0.0

3120 Apple 0.9

3120 Mango 2.0

3120 Pineapple 3.0

3120 Orange 4.0

3120 Sweets 6.0

3120 Banana 2.0

Hopefully , if this helps,some one please try to help me.

Kind regards.

Hui · Jun 22, 2012

Alpana

Try the following VBA Code

Copy the text and paste it into a VBA Code Module

Then add a button to the Reference worksheet and link it to the Shift_Data subroutine

[pre]

Code:

Sub Shift_Data()

Worksheets(&#34;Reference&#34;).Range(&#34;B2:B4&#34;).Copy
Sheets(&#34;Base sheet&#34;).Range(&#34;A1&#34;).End(xlDown).Offset(1, 0).PasteSpecial _
Paste:=xlPasteAll, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=True

Worksheets(&#34;Base sheet&#34;).Select
Range(&#34;A1&#34;).End(xlDown).Offset(-1, 0).Select
Range(Selection, Selection.End(xlToRight)).Copy
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.PasteSpecial _
Paste:=xlPasteFormats, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Application.CutCopyMode = False

End Sub

[/pre]

You will also need to save the file as a *.xlsm or *.xlsb file

If the above instructions sounds like Chinese try the following file: https://www.dropbox.com/s/3h6jf67bk56vd77/Tool2.xls

Alpana · Jul 8, 2012

Hi Hui,

Thanks for the help.Sorry ,was not online from past 2 weeks to say you thanks...

The code I was looking at was not this one, but , with the help of your forum i could resolve the issue and it clicked.

Thanks to the forum and thanks to chandoo for coming up with a good site and Good aim "Become Awesome in excel".

Can i create an macro to generate new excel spreadsheet based on the base data?

New Member

Excel Rōnin

Excel Rōnin

Excel Rōnin

New Member

New Member

New Member

Excel Rōnin

Excel Ninja

New Member

Excel Ninja

New Member

Excel Ninja

New Member

New Member

Excel Ninja

New Member

New Member

New Member

Excel Ninja

New Member