Thread: Copy cells from sheet to sheet as text.

Hi all,

I have a workbook made up of 2 main sheets. The user fills in a userform and it saves the data to the sheet "DATABASE". No users are allowed to edit the data on this sheet and it is password protected so they can't even see it. Instead I have a "VIEWER" sheet which is a copy of "DATABASE" except without some of the columns. What I've just realised though is that the way I have written it the forumlas are copied across but for them to work one of the columns is missing. Is there a way that I can make it copy the numbers not the forumulas in the cells?

This is the code I have used:

(ViewAllBookings is the button on the main menu sheet.)

Sub ViewAllBookings()
'
' ViewAllBookings Macro
' Macro recorded 17/09/2009 by Cassandra Poyiadjis-Osler
'To open Viewer

    Sheets("VIEWER").Select
    Range("A1").Select
    
    Dim rng    As Range

    Sheets("VIEWER").Cells.Clear
    With Sheets("Database")
        Set rng = .UsedRange

        rng.Resize(rng.Rows.Count, 20).Copy Sheets("VIEWER").Cells(1, 1)
    End With
End Sub

I was thinking that the best way would be to add a line specifiying that the copy is done as text so instead of copying: the following into column G
=IF(ISERROR(VLOOKUP(Q3,BACKGROUND!J:M,4,FALSE)=TRUE),"",VLOOKUP(Q3,BACKGROUND!J:M,4,FALSE))
(Q3 is the cell that isn't there) it would just copy the number 205.


hope that makes some sense.

many thanks,
Z