• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

Alpana

New Member
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").Select
ActiveCell.FormulaR1C1 = "='FP space'!R[-7]C[-1]"
Range("C10").Select
ActiveCell.FormulaR1C1 = "='FP space'!R[-8]C"
Range("C11").Select
Sheets("Front Sheet").Select
Sheets.Add
ActiveCell.FormulaR1C1 = "Store no"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Merch group"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Space"
Columns("C:C").Select
Columns("B:B").EntireColumn.AutoFit
Range("A2").Select
ActiveCell.FormulaR1C1 = _
"=IF('Front Sheet'!R[20]C[10]='Front Sheet'!R[8]C[2],'Front Sheet'!RC[2],""NA"")"
Range("A2").Select
Selection.Copy
Application.CutCopyMode = False
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:C2"), Type:=xlFillDefault
Range("A2:C2").Select
Range("B2").Select
ActiveCell.FormulaR1C1 = _
"=IF('Front Sheet'!R[20]C[10]='Front Sheet'!R[8]C[2],'Front Sheet'!R[9]C[2],""NA"")"
Range("B2").Select
ActiveCell.FormulaR1C1 = _
"=IF('Front Sheet'!R[20]C[10]='Front Sheet'!R[8]C[1],'Front Sheet'!R[9]C[2],""NA"")"
Range("B2").Select
ActiveCell.FormulaR1C1 = _
"=IF('Front Sheet'!R[20]C[9]='Front Sheet'!R[8]C[1],'Front Sheet'!R[9]C[2],""NA"")"
Range("A2").Select
ActiveCell.FormulaR1C1 = _
"=IF('Front Sheet'!R22C11='Front Sheet'!R10C3,'Front Sheet'!R2C3,""NA"")"
Range("B2").Select
ActiveCell.FormulaR1C1 = _
"=IF('Front Sheet'!R22C11='Front Sheet'!R10C3,'Front Sheet'!R11C4,""NA"")"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:C2"), Type:=xlFillDefault
Range("B2:C2").Select
Range("C2").Select
ActiveCell.FormulaR1C1 = _
"=IF('Front Sheet'!R22C11='Front Sheet'!R10C3,'Front Sheet'!R[9]C[8],""NA"")"
Range("A2:C2").Select
Selection.AutoFill Destination:=Range("A2:C12"), Type:=xlFillDefault
Range("A2:C12").Select
Range("B2").Select
ActiveCell.FormulaR1C1 = _
"=IF('Front Sheet'!R22C11='Front Sheet'!R10C3,'Front Sheet'!R[9]C4,""NA"")"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B11"), Type:=xlFillDefault
Range("B2:B11").Select
Selection.AutoFill Destination:=Range("B2:B12"), Type:=xlFillDefault
Range("B2:B12").Select
Sheets("Sheet2").Select
Range("C2").Select
ActiveCell.FormulaR1C1 = _
"=IF('Front Sheet'!R22C11='Front Sheet'!R10C3,'Front Sheet'!R[9]C11,""NA"")"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C12"), Type:=xlFillDefault
Range("C2:C12").Select
Range("A1").Select
Sheets("Front Sheet").Select
Range("C2").Select
ActiveCell.FormulaR1C1 = "='FP space'!R[1]C[-2]"
Range("C9").Select
ActiveCell.FormulaR1C1 = "='FP space'!R[-6]C[-1]"
Range("C10").Select
ActiveCell.FormulaR1C1 = "='FP space'!R[-7]C"
Range("C10").Select
ActiveCell.FormulaR1C1 = "='FP space'!R[-7]C"
Range("C11").Select
ActiveSheet.ClearArrows
Range("C2").Select
ActiveCell.FormulaR1C1 = "2007"
Range("C9").Select
ActiveCell.FormulaR1C1 = "1"
Range("C10").Select
ActiveCell.FormulaR1C1 = "14"
Range("C11").Select
ActiveSheet.ClearArrows
 
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!
 
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!
 
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!
 
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?
 
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.
 
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!
 
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

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

'We're doing something on Front sheet...not sure what
With Worksheets("Front Sheet")
.Range("C2") = 2007
.Range("C9") = 1
.Range("C10") = 14
End With
End Sub
[/pre]
 
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.
 
Afraid I can't access uploaded workbooks at my location, but hopefully a fellow forum member will see this and help out...
 
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.
 
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.
 
I am sorry, couldnt find the solution in the file.


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


Any luck with the macro.


Sorry for keeping so many reminder mails,thought it shouldnt miss out in the forum.
 
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.
 
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("Reference").Range("B2:B4").Copy
Sheets("Base sheet").Range("A1").End(xlDown).Offset(1, 0).PasteSpecial _
Paste:=xlPasteAll, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=True

Worksheets("Base sheet").Select
Range("A1").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
 
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".
 
Back
Top