Report Template ver 59.xlsm
Hi guys! Im very new to macro programming, and only know how to do macro recording and there are many things that cant be done with recording.
But its nice to have a working practical example, to try to apply it in my job. An example would be in my attachment. the ultimate aim is to save time. Hence, when a company releases their results, I will update the sheet "Data1" and "Data2" which will be linked to a data provider, and in-house analysis.
So what im trying to do is the following.
- There will be two buttons in the first sheet. One says "Result Update". One says "Company Update"
if i click company update, a HELLO WORLD popup will happen (ill replace this once i learn how to write the macro)
if i click Result Update the following will happen
- In Sheet Table 2, Cell E4, Choose the cell, add one to any number existing in this cell replace it. (eg: if its 21, it becomes 22)
// the above cant be done in recording mode, because recording cant recognise a number in order to add+1 to it.
- Once the above is completed go to Output Sheet. Find the bottom most row in column T which has a content (lets name this row X). Select the entire row row X. Copy row X. Select the entire row below the row below row X (row X+1). Insert copied cells.
// this operation will create a new line of dates. in the attachment, lowest column T with content is row 151. so copy the entire
// column and paste on the column below it, effectively creating a new row, and "extending" the VLOOKUP.
// I cant do this because recording cannot "find the bottom most row where column T has a content"
- Once the above is completed, In Sheet Output (same sheet) Copy Cell V136 to AB136. Paste Values in V(row X+1) to AB(row X+1)
// this will paste values the latest numbers to the new line added.
// I think i can do this with record macro. but i will have trouble combining.
- Once the above is completed... do the following in this order
- Go to Sheet Writeup, Unhide Row 14-17, Unhide Row 38-39
- Go to Sheet Output, Replace H2 with "Quarter Result Update"
- Go to Sheet Output, Replace Cell T133 with "today()" <-- today's date
// this set i can do with record macro
Bookmarks