Excel automation- Updation of data and email

[richis]

Hi Everyone,

This is my first ever post on this forum!!

I need some help to automate my excel work:

I will explain what I do currently..



I have SAS CODE(run as a trigger run)

SAS code which generates a table which I export in Excel(lets call it Data Sheet).

This has numbers for Prior month and current month to date(by date) for revenue.



EXCEL WORK:

Excel workbook Report has sheets Sheet1 and Display1

I copy and paste data from Data Sheet and paste it in Sheet1.

Excel sheet Display1 has formulas which take data from Sheet1. I convert Display1 excel sheet as pdf and send it via email.

Once the data comes in for a day’s revenue, I have to freeze it which means I manually copy and paste the data for that day as a value in my excel(Display1 Sheet)…Numbers for Prior days will change when pulled on future date but we want to see the number as it first appeared on that day…

Eg, when I do a pull on 8th july, I get the below numbers:

01Jul2018 90
02Jul2018 123
03Jul2018 256
04Jul2018 123
05Jul2018 256
06Jul2018 548
07Jul2018 567
08Jul2018 400

Now when I do a pull on 9th July I might get :

01Jul2018 100
02Jul2018 150
03Jul2018 200
04Jul2018 250
05Jul2018 300
06Jul2018 350
07Jul2018 400
08Jul2018 450

But in my report, I do want to see 7th July as it was pulled on 8th (567) not 400 and so forth for the coming days…



My current code runs through a trigger run everyday at 5am , so when I come to office I have the data ready and do the Excel work..What I want to do is to automate the excel work as well so I do not have to work on it everyday..the mail automatically gets out!


If this isn't the right spot for such a question, please direct me to the appropriate forum.



Thanks a lot!!

[dflak]

Welcome to the forum. You are in the right place.

One question. Is the downloaded source file waiting for you in a file every morning? If so we can take this automation even further. But for now, the basics. It seems that you have outlined a clear requirement, and I have pieces of code already in place to handle some of it. What is missing is some sample source data.

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[richis]

Attached is an Excel sheet.

On 1st Aug, I have data for 1st which would have been frozen on that day(copy sna pasted as values so it doesnt change the next day I pull data from 1st till 2nd aug
Here I have shown the same concept for 5th and 6th Aug

See sheet Display1 and Data for 5th:
So on 5th Aug, we have data from 1st till 5th in the Data Sheet..I copy and paste the value for 5th as it had come in on 5th..


Now, see the sheet Display2_6th Aug and 6th_Aug
Now, when I do the same pull for 6th Aug, I get new numbers for 1-5th Aug , my numbers would change from 1st to 5th Aug and also I will have a new number for 6th.
But in my Display I do not want the numbers to change for 1st to 5th..only add the number for 6th and then paste it(6th) as a value so its frozen.

In the attached sheet I have created separate tabs to explain myself, but I just have 2 tabs on a daily basis that I work on (Display and Data)
(Also, Col K is manually calculated...which I would like to discuss as well..after this ques is clear)
Please let me know if what I am saying makes sense..

Also, I do have the data ready when I come in...

[dflak]

I think this is what you want. I assume you copy in a new display every day.

[richis]

Thanks Dflak!
I dont really understand the VBA language..could you walk me through the macro you have created??

"Display" is the sheet that I send out as a pdf

Can i record a similar macro using the macro recorder?

[dflak]

The macro recorder would probably not work in this situation. It will catch that you selected Cells B13:C13 on the Display sheet and copied them. If the data you want is always in these two cells than this is OK. However, it will record that they were pasted into Cell A6 on the Data sheet. This is not what you want for tomorrow.

The macro recorder is a good tool to show you the syntax on how to do things, but it has limits: it almost always "hard codes" ranges and it can't loop. However, it will give you an idea of what you want to do. Also, it writes "sloppy" code. If you use the macro recorder and see:

Please Login or Register  to view this content.

You can almost always replace it with

Please Login or Register  to view this content.

With few exceptions you don't need to select anything in VBA.

As for VBA, I could write a book on it and maybe I will when / if I ever retire. However there are two pieces of advice I offer: 1) Option Explicit (see below) and 2) Always tell Excel where you want it to be by specifically referencing the workbook (if you have more than one open), the sheet and the cell in which you want the action to take place. Your idea of what the active sheet or cell is, and VBA's idea of what is active might be two different things. So if you are modifying something the macro recorder has made for you, be wary of activesheet and activecell.

About Option Explicit:
It is a best practice to declare all variables. If you misspell a variable in your code, VBA will silently assume it is a Variant variable and go on executing with no clue to you that you have a bug. Go to the VBA development window, click Tools, Options, and check "Require Variable Declaration." This will insert the following line at the top of all new modules:

Please Login or Register  to view this content.

This option requires all variables to be declared and will give a compiler error for undeclared variables.

As for this particular macro, here's my best shot at explaining it.

Please Login or Register  to view this content.

[richis]

Thank you Dflak!

I kindoff understand what you explained: Copy data from Display to Data?

Sheet Data will have new numbers each day(starting from start of month till today) and what I want is : that new value to be saved as a (copy paste) value inthe Display sheet so it doesnt change...but I guess you are doing the reverse of this...

[dflak]

You want the last number on the Data sheer copied to the Display sheet. OK, so I have it backwards. Where do you get the numbers for the other columns on the display sheet?

[richis]

Some of them are formulas like variance(difference of tow adjacent columns)
Some are manually calculated at the moment

[richis]

Currently, the display sheet has formulas linked to the Data sheet from where it gets the value say for 13th aug(eg. 100) as it comes in today...using sumif..

I just want col C in Display sheet to freeze after the data for 13th has come in , so on 14th when there will be a changed number for 13th(eg. 120) in the "Data" sheet, Display sheet would still show the value as of 13th (100).

So, I was looking for a macro to copy and paste the value in the cell as it had first come in..