[tbeatham]

Can anyone tell me how I can fix a cell reference in Excel so that the formula doesn't change when I move things about. The dollar sign does not seem to work.

I have a spreadsheet that has 2 workbooks, the first work book has a number of coumns across the page i.e. 10 weeks which is being used to give a 10 week average. I then run a macro each week that deletes the first column, and moves all the remaining columns along one so that I can then enter the details in the end column for the most recent weeks information. It is this end column I want to refer to in the other workbook to pick up a figure and use in another formula but unfotrunately every time I run the macro the referencing in the other workbook changes to pick up the original figure. i.e. the column reference moves with the running of the macro.

I have tried using the dollar sign to fix the cell refrence but this is not having any effect.

Would be really grateful if someone could help me as this will save me a whole load of work having to re-key information.

Cheers

[turbodante]

Can you post a sample - remove personal/sensitive data before doing so.

[tbeatham]

Please see the attached spreadsheet for information.

[turbodante]

tbeatham, it seems to me there's a lot of redundant stuff in your macro - which is often the case with recording macros.

Case in hand, there's a lot of recorded actions of scrolling the screen where this is unnessessary, so have taken these out in the code and have don;e some general tidying up.

Also, there have been areas in the code where you have copied contents to the end week only to clear their contents - I have deleted these also.

The code has been whittled down a bit. I have included a quick way of solving the problem with your moving references in the 'All' tab, which is to replace the '=' before doing the macro and then to put it back in afterwards.

As usual, please test the code on sample data before running on mission critical stuff.

Sub Update()
'
Set wSH = Sheets("Wages (2)")
Set aSH = Sheets("All (2)")

wSH.Cells.Replace What:="=", Replacement:="xxx"

    
        aSH.Range("F5:U66").Cut Range("E5")
        aSH.Range("F71:U107").Cut Range("E71")
        aSH.Range("F112:U173").Cut Range("E112")
    
        aSH.Range("F178:U239").Cut Range("E178")
        aSH.Range("F244:U300").Cut Range("E244")
    
        aSH.Range("F307:U342").Cut Range("E307")
    
wSH.Cells.Replace What:="xxx", Replacement:="="
End Sub

[tbeatham]

Thanks for the update on my spreadsheet and your help with my problem but I am a little lost as to what you have given me. You have said that you have tidied my code up but I have no copy of the spreadsheet so see what you have done and you have provided me with a macro routine but I am not sure what I am supposed to do with this. Do I stick it at the end of my macro or at the beginnning.
Really would appreciate some more guidance thanks.
Cheers,
Tim

[turbodante]

Originally Posted by tbeatham

Thanks for the update on my spreadsheet and your help with my problem but I am a little lost as to what you have given me. You have said that you have tidied my code up but I have no copy of the spreadsheet so see what you have done and you have provided me with a macro routine but I am not sure what I am supposed to do with this. Do I stick it at the end of my macro or at the beginnning.
Really would appreciate some more guidance thanks.
Cheers,
Tim

Sorry tim, I should have been clearer. The routine is to replace the one you have in module6. You can copy/paste it in any of the modules you have when you open up the VB editor.

[turbodante]

Also, you may want to alter the following lines in the code to reflect the names of your sheets...

Set wSH = Sheets("Wages (2)")
Set aSH = Sheets("All (2)")

to

Set wSH = Sheets("Wages")
Set aSH = Sheets("All")

[tbeatham]

Thanks for the speedy reply and I amnaged to figure out the details but have tried the macro and unfortunately it does not seem to work correctly for the weeks as it just removes the end column and moves the data along each week without putting a new blank column on the end. The macro does appear to hold the cell referencing on the wages worksheet.
The columns are supposed to basically drop of the first week in the series and then add a new column to have data entered for the latest week so that effectively you get a 17 week moving average calculated. This is probably why I copied the previous weeks data across and then blanked out the figures.
I'll have a work on it as I think it won't take much to complete now.
Thanks again for your help with this.
Reagrds,
tim