How to copy data into next available column

[AndyM90]

Hi All,

I have a worksheet for which I want to automatically input data in daily. I am stuck on how to write a macro which selects the next blank cell in row 1 and inputs yesterdays date (DD/MM/YYYY)?

Secondly, I then want to be able to run a calculation (e.g. =SUMIFS(EURdata!F:F,EURdata!D:D,"EURO") and copy the answer into the next available cell in row 3 (in worksheet "EUR").

Any help would be greatly appreciated.

Thanks,

Andy

 

[Tyron]

Hi All, I have a worksheet for which I want to automatically input data in daily. I am stuck on how to write a macro which selects the next blank cell in row 1 and inputs yesterdays date (DD/MM/YYYY)? Secondly, I then want to be able to run a calculation (e.g. =SUMIFS(EURdata!F:F,EURdata!D:D,"EURO") and copy the answer into the next available cell in row 3 (in worksheet "EUR"). Any help would be greatly appreciated. Thanks, Andy

Hey Andy, What it looks like you are trying to do seems to be brief enough that you should be able to use the macro recorder. Although it seems like there is more to it than what you say based on trying to select the next blank cell. Most of what you can do can be accomplished without the macro which will free the macro up to just copy it into your data page. Before you do that you want to either in a test sheet of your workbook or on some graph paper decide in which cells you want what values. I have manipulated the date in several worksheets that I have prepared. If you just want to have yesterdays date already in a cell that would be:

 =(TODAY())-1

This takes today's date and subtracts one day from it. Beyond that you just need to format the cell to get the information to display as DD/MM/YYYY. You seem to have the other formula finished so plug that into a cell on your worksheet. Once you have done that then all you have to do is record a macro that copies that information from sheet1 into sheet2 in the specific locations that you want. Sort of using sheet 1 as your data entry while Sheet 2 holds all of your completed information. Once you have succesfully completed recording your macro and you have verified that there are no problems then you can link it to a button to make it easier. later Ty

 

[AndyM90]

the recorder didn't help me out too much unfortunately, but with your help I have managed to piece a few pieces of code together to get the desired result:

Sheets("ABC").Activate
Cells(3, Columns.Count).End(xlToLeft).Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=WORKDAY(NOW(),-1)"

ActiveCell.Offset(1, 0).Select
ActiveCell.Formula = "=SUMIFS....."

Thanks!

 

[Tyron]

the recorder didn't help me out too much unfortunately, but with your help I have managed to piece a few pieces of code together to get the desired result:

Sheets("ABC").Activate
Cells(3, Columns.Count).End(xlToLeft).Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=WORKDAY(NOW(),-1)"

ActiveCell.Offset(1, 0).Select
ActiveCell.Formula = "=SUMIFS....."

Thanks!

Hey AndyM90,

Looks like some intense VBA/macro'ing there. I am suprised that you had to do as much, but glad that you found a solution.

later

Ty