Can someone tell me what this line of code is doing??? Thanks

[rob_sheeds]

This line lies within this sub below....what is it doing as it doesnt work properly....
Sheets("Sheet" & i).Cells.Value = Sheets("Sheet" & i).Cells.Value

Comes from Sub:
Dim i As Long
Workbooks.Open Filename:= _
"C:\Users\rsheedy\Desktop\Daily Sales\Daily Sales Report NEW Open Sales Orders - Copy.xlsm"
ActiveWorkbook.SaveAs Filename:="R:\Reports\Archive\Daily Sales Reports\May 2011\DSR_" & Format(Date, "DDMMYYYY") & ".xlsm", _
FileFormat:=52, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

With ActiveWorkbook
For i = 1 To 6

Sheets("Sheet" & i).Cells.Value = Sheets("Sheet" & i).Cells.Value

Next i

End With

 

[Smitty]

That line is attempting to convert the entire sheet to values, but it can be problematic because it's covering such a large range.

Try:

        With Sheets("Sheet" & i).Cells
            .Copy
            .PasteSpecial Paste:=xlPasteValues
        End With

HTH,

 

[rob_sheeds]

Thanks Smitty,
Can you suggest some better code? Also is it irrelevant if the sheets are called something different?
Cheers

 

[Smitty]

Can you suggest some better code?

Not really, if you're trying to get rid of formulas you need to value them out somehow.

Also is it irrelevant if the sheets are called something different?

Nope, your code doesn't care what the sheets are named since it's iterating through them based on #1 through #6. It could be an issue if you have more/less than 6 sheets, or if there are some you don't want to alter.

I usually use For i = 1 to Sheets.Count which means you don't have to take account of how many sheets there will be, as VBA will figure it out.

 

[rob_sheeds]

Thanks I appreciate your help!

 

[rob_sheeds]

Hmm still wont quite work. Same error.
Note 3 of my sheets are hidden.
Can you suggest something to only copy/paste values A1:T500 for
Sheet = "Daily Sales Report", "Open Orders", 'SOGS"
Again I appreciate you time.

Code now looks like this.
Sub robsheeds()
Dim i As Long
Workbooks.Open Filename:= _
"C:\Users\rsheedy\Desktop\Daily Sales\Daily Sales Report NEW Open Sales Orders - Copy.xlsm"
ActiveWorkbook.SaveAs Filename:="R:\Reports\Archive\Daily Sales Reports\May 2011\DSR_" & Format(Date, "DDMMYYYY") & ".xlsm", _
FileFormat:=52, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

With ActiveWorkbook
For i = 1 To Sheets.Count

With Sheets("Sheet" & i).Cells
.Copy
.PasteSpecial Paste:=xlPasteValues
End With

Next i

End With
Mail_workbook_Outlook_1
End Sub

Thanks

 

[Smitty]

Can you suggest something to only copy/paste values A1:T500 for
Sheet = "Daily Sales Report", "Open Orders", 'SOGS"

The best way to get that code would be to group the sheets and record a macro. If they're hidden then for grouping purposes, just unhide them, do your thing and rehide.

Post back the code you get and someone can clean it up for you.