Updating a summary report automaticaly from existing data using VBA

[natik]

Hi there,
I'm trying to figure out how I could generate a report using a Command Button. I have a spreadsheet that contains data in columns, some of which I want showing up in the summary report. Any new data entered should be updated in the summary report when I hit the command button. Existing data in the original spreadsheet does not get erased or written over.

So basically, I have data in column range A:R in Sheet 1. Columns A, D. E, F, G, I, J, P, and R need to show up in the report on Sheet 2. I already have 150 rows of data in sheet 1, so i don't want to start over. So I need to copy the relevant columns, and update the report with any new data that gets entered in the columns in Sheet1, in the next empy row.


I have some vba code that copies individual cells, but I don't know how to do it for columns and for new data. VBA-wise I don't know what I'm doing yet. Sorry! I'm just starting to learn it, but really need to automate my reports at work!

I would be very grateful for some help. I'm working on this and will post my code on this thread if needed.

 

[iliace]

Is the report on Sheet 2 columnar as well? So Sheet1 -> Sheet2, is the mapping A->A, D->B, E->C, ... etc up to R-I? The data rows are in 1-to-1 correspondence?

Also, is there a key column? Or some combination of columns that identifies a row as "new"? Perhaps you're working with a date range? Or does each cell need to be checked to establish whether or not it's unique?

 

[natik]

Is the report on Sheet 2 columnar as well? So Sheet1 -> Sheet2, is the mapping A->A, D->B, E->C, ... etc up to R-I? The data rows are in 1-to-1 correspondence?

Also, is there a key column? Or some combination of columns that identifies a row as "new"? Perhaps you're working with a date range? Or does each cell need to be checked to establish whether or not it's unique?

Hi Iliace,
Correct, column mapping and row correspondence is exactly as you describe it.
Unfortunately, the data may or may not be unique, but I still need the new data entered on sheet 1 to pop up on the report. There are payment dates involved which I could filter for once the data is copied over.

Basically, the spreadsheet is a list of applications received by me for reimbursement from a set of eligible individuals participating in one of our programs. So the data involves names, application dates, eligiblity dates, reimbursement dates, place of origin of individual, amount requested, amount approved, etc. Every individual is recorded. Payments are not batched up by payment date, and I need that reflected in the report. The report is for a third party who needs to have the info at that level of detail.

I'm thinking the code could just copy the relevant columns every time i hit the button, including all new data entered since the last time info was entered. I send out the reports every time a set payment date is reached and payments have been made. I could simply filter for the appropriate payment date in sheet 2 (that contains the copied columns) and pdf the result. I know this sounds amateurish, but I'm not sure how I would make this more efficient at this point.

many thanks!

 

[iliace]

You need some way of determining when the last transfer from Sheet1->Sheet2 was made. So then, which column contains the date? Can the code look for the latest date in Sheet2, and then pull in any date from Sheet1 that is after that date?

 

[natik]

Oh great. I hadn't thought of it that way.. It's column R that contains the payment dates. Correct, I would update sheet 2, every time there is a new payment date, after the last one of course.