Creating a spreadsheet that updates every month, using vba/macros

[carrus]

Hi,
I have a spreadsheet that needs to be update once a month but because it is quite tedious I would prefer to write a macro that would update the spreadsheet for me. The problem is that I am pretty unfamiliar with this type of thing and don't really know what to do. I have tried to do it by recording macros, but because of the large amount of changes to be made it took me a long time to not get very far. The following are the actions that I would like to write in VBA, any level of help would be appreciated.

1) Replacing columns with other columns found within the same worksheet
2) Replacing cells with cells from other worksheets
3) Replacing cells with cells from other workbooks
4) put a title above a column that reads the month (specifically 5 months in the future, ie. if the month is May I want it to display Oct)
5) create/update graphs using the new data

Thanks in advance!

[arlu1201]

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem. Once you have done this please send me a PM and I will remove this request.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

[AlvaroSiza]

I'll take 2-4 as I don't have a lot of time at the moment.

2) Replacing cells with cells from other worksheets

3 methods to choose from. In order of personal preference and less code. For simplicity, assume you want data in Cell A1 of Sheet 2, to be presented in Cell A1 of Sheet 1

Please Login or Register  to view this content.

3) Replacing cells with cells from other workbooks

Initially, it depends on whether the "other workbooks" are open or closed. If open, it depends if the workbook is part of the active instance of excel, or part of another instance. Generally speaking, you declare (dimension) variables for each of the workbooks, and then employ the same methods in No. 2 qualified with the appropriate workbook object variable. Assuming a closed target workbook (the one you want to get data from), the main workbook would be dimensioned (and ultimately "Set") as ThisWorkbook or ActiveWorkbook, the other set to the other workbook after you have gained the string path of it, for example, using the open file dialog to locate the file. Could look something like this:

Please Login or Register  to view this content.

4) put a title above a column that reads the month (specifically 5 months in the future, ie. if the month is May I want it to display Oct)

You could easily use a formula for this. If, say, May was located in Cell A1, and you wanted October in Cell A2, then:

Please Login or Register  to view this content.

[carrus]

thanks for the help AlvaroSiza, easy to follow and worked well