Macro code for copying Data to next row after a certain period

Printable View

09-23-2015, 09:38 PM
Shahadat65

1 Attachment(s)

Macro code for copying Data to next row after a certain period

Hello everyones,greetings

I will appreciate your helping hands towards me.

I am a novice and not professional in EXCEL MACRO but I have a great thurst to learn EXCEL MACRO,

I am prepareing a automation excel sheet to get data from Dhaka Stock Exchange. Here I am giving the codes in below :
Please put these codes in VBA editor and run. I need help on the following :

1] You will see a ROW 2015 in Year column. Datas are extract in this row from Dhaka stock exchange web site while I run
Macro. I need these data in this row until 31 December,2015. So I want these data will not update any more after 31 December
2015 in this row.

2. I need above datas will update in ROW 2016 from 1st January,2016 and will continue update till 31 December,2016 and this
process will contine like this for 2017 & 2018 and so on year after years.

I will reamin ever garateful to you should you help me with the code, Please send me the code separately and advise me
where I need to set these code,

Thank you in advance for your kind help on this.

Please see attach for code,

Regards,
Shahadat
09-23-2015, 10:19 PM
Trebor76

Hi Shahadat65,

Welcome to VBA Express!!

You don't need a macro, just an IF statement to check what the current year is and only return the desired values if it this year. For example the formula in cell B7 would look like this:

=IF(VALUE(YEAR(NOW()))=VALUE($A$7),IF(AG99="","",AG99),"")

Regards,

Robert
09-25-2015, 08:04 AM
Shahadat65

Macro code for copying Data to next row after a certain period

Dear Mr.Robert,
Many thanks for your kind assistance.
I am working as per your advice and will let you know whether this is working or not,

Thanking you once again for your helping hands,

Regards,
Shahadat
=========================================================================== =================================

Quote:

Originally Posted by Trebor76

Hi Shahadat65,

Welcome to VBA Express!!

You don't need a macro, just an IF statement to check what the current year is and only return the desired values if it this year. For example the formula in cell B7 would look like this:

=IF(VALUE(YEAR(NOW()))=VALUE($A$7),IF(AG99="","",AG99),"")

Regards,

Robert
09-25-2015, 08:47 PM
Shahadat65

Dear Mr.Robert,
I have checked and found formula : =IF(VALUE(YEAR(NOW()))=VALUE($A$7),IF(AG99="","",AG99),"") is working, I need some more help on this,

1. Can you please explain VALUE($A$7) , this part of this formula. In particular meaning of $A$7 here,
2. Can you please give me the formula for Cell CellB9 data will copy from cell AE18 for this year.
These will make me clear to wire formula for other cells,

Will appreciate your help on this.

Thank you,

Best Regards,
Shahadat

Quote:

Originally Posted by Trebor76

Hi Shahadat65,

Welcome to VBA Express!!

You don't need a macro, just an IF statement to check what the current year is and only return the desired values if it this year. For example the formula in cell B7 would look like this:

=IF(VALUE(YEAR(NOW()))=VALUE($A$7),IF(AG99="","",AG99),"")

Regards,

Robert
09-25-2015, 09:06 PM
Trebor76

Quote:

Can you please explain VALUE($A$7) , this part of this formula. In particular meaning of $A$7 here

This part of the formula is simply anchored (locked) to cell A7. If you want to change when you copy the formula simply remove the dollar signs as appropriate.

Quote:

Can you please give me the formula for Cell B9 data will copy from cell AE18 for this year

It will return a blank as we're not in 2017 for another 15 months :dunno but you could use this in cell B9:

=IF(VALUE(YEAR(NOW()))=VALUE($A$9),IF(AE99="","",AE99),"")

Robert
09-25-2015, 11:17 PM
Shahadat65

Dear Mr.Robert,
Understand, I can use the formula , =IF(VALUE(YEAR(NOW()))=VALUE($A$9),IF(AE99="","",AE99),"") in B9 , Like this can I use this formula in C9, D9 etc ?.

Is it correct formula to write in B10 , C10 , D10 etc : =IF(VALUE(YEAR(NOW()))=VALUE($A$10),IF(AE99="","",AE99),"")

If I like to copy the formula, I have to remove both dollar sign from A, Am I correct?

Regards,
Shahadat

Quote:

Originally Posted by Trebor76

[/COLOR]

This part of the formula is simply anchored (locked) to cell A7. If you want to change when you copy the formula simply remove the dollar signs as appropriate.

It will return a blank as we're not in 2017 for another 15 months :dunno but you could use this in cell B9:

=IF(VALUE(YEAR(NOW()))=VALUE($A$9),IF(AE99="","",AE99),"")

Robert
09-25-2015, 11:25 PM
Trebor76

Quote:

If I like to copy the formula, I have to remove both dollar sign from A, Am I correct

No, I would remove the second dollar sign i.e. the one that is anchored to the row because you always want to check against the year in column A. You can check what cell(s) the formula is referencing by pressing F2 while on a formula.
10-12-2015, 12:04 AM
Shahadat65

1 Attachment(s)

Copy data year wise

Dear Sirs,

Greetings and hope everything is well with you.

Following your foumula I have prepared the attached excel sheet as per my need.

I would request your kind help to check this and formula is correct.

1. In B7 I need to copy data from AG99 in 2015, after 2015 I do not need data in AG99, for this I have put below formula in B7, please check
whether it is correct or not.
=IF(VALUE(YEAR(NOW()))=VALUE($A$7),IF(AG99="","",AG99),"")

2. In B8 need to copy data from AG100 in 2016,
=IF(VALUE(YEAR(NOW()))=VALUE($A$8),IF(AG100="","",AG100),"")

3. In B9 need copy data from AG101 in 2017,
=IF(VALUE(YEAR(NOW()))=VALUE($A$9),IF(AG101="","",AG101),"")

4. In C8 I need copy data from W100 in 2016, please check this formula is correct or not.
=IF(VALUE(YEAR(NOW()))=VALUE($A$8),IF(W100=" "," ",W100))

5. In Q8 I need copy data from AE18 in 2016 onward, every year data will change in AE18 but this cell AE18 will not change.
=IF(VALUE(YEAR(NOW()))=VALUE($A$8),IF(AE18=" "," ",AE18))

6. In Q9 also I need copy data from AE18 in 2017,
=IF(VALUE(YEAR(NOW()))=VALUE($A$9),IF(AE18=" "," ",AE18))

As I am not expert in Excel, so I need your help to make my undersanding clear.
I will appreciate to look at the excel sheet and verify the formulas that I have arranged
as per your previous advise.

Thanking you and warmth regards,
Shahadat