REF Error

[Chris Mcarthur]

On sheets 1 through 30 representing each day of the month I have data that I would like to transfer to the Summary sheet.
so for example I have '1'!A2 as a formula on the summary sheet and when i type it in, it seems to work but when I delete out all the data on sheet '1' and copy and paste the new information into when a new month begins, I get a REf error on my Summary sheet.why wont it pick up what is still in cell A2 on Sheet '1'?

 

[Joe4]

Welcome to the Board!

How exactly are you deleting the data?
If you are deleting rows, you will mess up your row reference formulas. It would be better to simply clear the data instead of deleting the rows (highlight all the data and hit the Delete button on your keyboard).

Otherwise, you would need to change yoru reference formula, maybe introduce INDIRECT.

Note that you can also use an Excel macro to easily and quickly clear all the data out of all the sheets at once.

 

[Chris Mcarthur]

Welcome to the Board!

How exactly are you deleting the data?
If you are deleting rows, you will mess up your row reference formulas. It would be better to simply clear the data instead of deleting the rows (highlight all the data and hit the Delete button on your keyboard).

Otherwise, you would need to change yoru reference formula, maybe introduce INDIRECT.

Note that you can also use an Excel macro to easily and quickly clear all the data out of all the sheets at once.

Hi thanks for that, I did try the delete on the keyboard but it keeps the formatting in place. can you tell me more about the Macro method?

 

[Joe4]

Hi thanks for that, I did try the delete on the keyboard but it keeps the formatting in place.

Yes, it will only clear the data, not the formatting, though we may be able to clear the formatting too.
What kind of formatting are we talking about?

can you tell me more about the Macro method?

Are all the sheets basically structured the same way?
What rows/columns are populated?
Are there any completely blank rows in the middle of your data or not?
Are there are header rows that you do NOT want cleared?

 

[Chris Mcarthur]

Yes, it will only clear the data, not the formatting, though we may be able to clear the formatting too.
What kind of formatting are we talking about?


Are all the sheets basically structured the same way?
What rows/columns are populated?
Are there any completely blank rows in the middle of your data or not?
Are there are header rows that you do NOT want cleared?

The formatting is fill color, etc that wont be the same from month to month, based on employees off, sick, etc..

Yes all the sheets are structured the same way with title/headers in place, 5 columns but i only am transferring only column A and E date to the summary sheet... no completely blank rows are copied and pasted in.

 

[Joe4]

What I am trying to get a picture of is which rows/cells need to be cleared.
Is it every row on each page (except the Summary page), except row 1?
Or are there multiple header rows to leave?

 

[Chris Mcarthur]

What I am trying to get a picture of is which rows/cells need to be cleared.
Is it every row on each page (except the Summary page), except row 1?
Or are there multiple header rows to leave?

Yes every row is cleared except for the 1st row.

 

[Joe4]

The following code will clear all data and color formatting out of every sheet not named "Summary", and will leave the headers in row 1.

Sub MyMacro()

    Dim ws As Worksheet
    Dim rng As Range
    
    Application.ScreenUpdating = False

'   Loop through all worksheets
    For Each ws In Worksheets
'       Skip summary sheet
        If ws.Name <> "Summary" Then
'           Select range to be current region except for first row
            Set rng = ws.Range("A1").CurrentRegion.Offset(1, 0)
'           Clear range
            rng.ClearContents
'           Clear color formatting in range
            With rng.Interior
                .Pattern = xlNone
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
        End If
    Next ws
    
    Application.ScreenUpdating = True
    
End Sub

 

[Chris Mcarthur]

Thank you very much.

 

[Joe4]

You are welcome.
Hope that helps to simplify and streamline things for you!