Auto Update Macros

[PROGEON]

A spreadsheet updates information on a weekly basis.When we record the macro when can only select a particular column in excel to copy. Once the spreadsheet is updated the system automatically recognise that a new column is updated

 

[Joe4]

Welcome to the board.

I think you'll have to provide some more specific details, and maybe an example and what you expected results are.

 

[PROGEON]

I am recording a macro to extract data from another spreadsheet which is updated on a weekly basis. When recording the macro the system recognises which column to copy and paste, but when the spreadsheet is updated the following week the initial macro would still copy the initially specified column and does not copy the new weeks data. Please can you advice me on the process if there is one to make the macro intelligent enough to capture updated figures. Your help is greatly appreciated.

 

[Joe4]

Yes, but we need some logic to determine which column to copy. Is it always the last (right-most column)? Which row can we look at and try to determine where the last column exists?

That is why I was asking for more specific details. We need to know the specific layout of the spreadsheet you are trying to copy from, and where the new data will be showing up.

 

[PROGEON]

Yes, it is always the last right column which is suppose to be copied. I am sorry but I dont understand what the second part of your question means. Its row number 4 that we can look at to determine the last column updated. Thanks

 

[Joe4]

You can use this code to get the last populated column in Row 4. That should give you what you need for your code.

    Dim myLastColNum As Integer
    myLastColNum = Range("IV4").End(xlToLeft).Column