Automate daily copying of data from one wksht to another wksht

Originally Posted by keierkr

I need to automate the copying of a range of data in one worksheet to a range of cells in another worksheet location based upon the date of entry.

The project for which the workbook is used requires daily entry of operating and cost data in the first worksheet. A second worksheet is used to accumulate daily costs in table form using cost categories in the first column and dates as column headings. The cost data is to be copied daily form worksheet one to worksheet two in the column with a date heading corresponding to the current date.

I want to program a button to find the 'date' column heading in the second worksheet corresponding to the data entry date, which is a daily entry item, and copy a range of cost data to a range below the 'date' column heading in worksheet two. The cost data ranges are one column wide by 12 rows high and are already defined and formatted in the second worksheet.

My problem is finding the appropriate date column heading in worksheet two and copying the cost data range from worksheet one to the cells below the 'date' column heading in worksheet two.

Advise/help will be appreciated. I'm willing to share the workbook to better visualize the problem.

Hi,

the function to find the date column would be a Match

=MATCH(A1,Sheet2!$B$1:$Z$1,0)

will return the nth column from Sheet2 according to the date in A1

To do this by code, easiest would be in the form

Please Login or Register  to view this content.

The rest of the code could be deduced from a copy of a recorded macro, Tools, Macro record, and perform the required copy, then amend the code to include iColumn as the variable column number.

Let me know how you go.
---