Paste as Values

[booton]

Hello everyone.

i have this code that is pasting one cell into another sheet at the end but it is keeping the format. i want to paste as value that cell. can someone please help me? Thanks!!!!

Sub Copy_Timestamp()

    Sheets("Historical_Excel").Range("G2").Copy Worksheets("Time_Stamp").Range("A" & Rows.Count).End(xlUp).Offset (1)
        
End Sub

 

[mohadin]

Hi

Sub Copy_Timestamp()
    Sheets("Historical_Excel").Range("G2").Copy
    Worksheets("Time_Stamp").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
End Sub

 

[Fluff]

Another option, that bypasses the clipboard

Sub Copy_Timestamp()

    Worksheets("Time_Stamp").Range("A" & Rows.count).End(xlUp).Offset(1).Value = Sheets("Historical_Excel").Range("G2").Value
        
End Sub

 

[Shiseiji]

Another option, that bypasses the clipboard

Sub Copy_Timestamp()

    Worksheets("Time_Stamp").Range("A" & Rows.count).End(xlUp).Offset(1).Value = Sheets("Historical_Excel").Range("G2").Value
       
End Sub

Modifying the code to

Dim DestWb    As Workbook
    Dim SourceWb  As Workbook
    Dim ThisWb    As Workbook
    Dim SourceWs  As Worksheet
    Dim ThisWs    As Worksheet
    Set SourceWb = ActiveWorkbook
    Set SourceWs = ActiveSheet
    Set DestWb = Workbooks.Add

DestWb.Worksheets("Sheet1").Range("A1").Value = ThisWs.Range("CopyRng").Value

returns error 91 "Object Variable or With Block Variable Not Set"

 

[Fluff]

You haven't assigned anything to the ThisWs variable.

 

[Shiseiji]

Sorry I wasted time with the above, hit [Reply} on accident before the editing was complete.
Hi Fluff, as always thanks in advance! Another "Can't figure out why" returns error 91 "Object Variable or With Block Variable Not Set"
Modifying the code to

    Dim DestWb    As Workbook
    Dim SourceWb  As Workbook
    Dim ThisWb    As Workbook
    Dim SourceWs  As Worksheet
    Dim ThisWs    As Worksheet
    Set SourceWb = ActiveWorkbook
    Set SourceWs = ActiveSheet
    Set DestWb = Workbooks.Add 
    SourceWb.Activate
    DestWb.SaveAs "C:\FileName_& Format(Now, "mm-dd-yyyy hh mm AM/PM") & ".csv", FileFormat:=6
   With SourceWb
    With SourceWs
     [URL='https://www.mrexcel.com/board/threads/vba-save-last-row-and-last-column-value-or-address-as-string-in-named-range-for-use-in-many-modules.1170127/']Rick's LastRow[/URL]'
    ' LastRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row - Errored
    LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    Range("A1", Cells(LastRow, LastCol)).Name = "CopyRng"
    DestWb.Worksheets("Sheet1").Range("A1").Value = ThisWs.Range("CopyRng").Value

 

[Shiseiji]

Sorry I wasted time with the above, hit [Reply} on accident before the editing was complete.
Hi Fluff, as always thanks in advance! Another "Can't figure out why" returns error 91 "Object Variable or With Block Variable Not Set"
Modifying the code to

    Dim DestWb    As Workbook
    Dim SourceWb  As Workbook
    Dim ThisWb    As Workbook
    Dim SourceWs  As Worksheet
    Dim ThisWs    As Worksheet
    Set SourceWb = ActiveWorkbook
    Set SourceWs = ActiveSheet
    Set DestWb = Workbooks.Add
    SourceWb.Activate
    DestWb.SaveAs "C:\FileName_& Format(Now, "mm-dd-yyyy hh mm AM/PM") & ".csv", FileFormat:=6
   With SourceWb
    With SourceWs
     [URL='https://www.mrexcel.com/board/threads/vba-save-last-row-and-last-column-value-or-address-as-string-in-named-range-for-use-in-many-modules.1170127/']Rick's LastRow[/URL]'
    ' LastRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row - Errored
    LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    Range("A1", Cells(LastRow, LastCol)).Name = "CopyRng"
    DestWb.Worksheets("Sheet1").Range("A1").Value = ThisWs.Range("CopyRng").Value

Argggggggggggggggggggg. Thanks and sorry!!!!!!! Delete both if you like. All I had to do was hover over the !@#$ yellow text. Too much habitual "ThisWs"

 

[Fluff]

Did you read what I said?

 

[Shiseiji]

Did you read what I said?

My apologies for lack of clarity. Yes I read it and changed the "ThisWs" to "SourceWs"
Sadly it's still erroring: DestWb.Worksheets("Sheet1").Range("A1").Value = SourceWs.Range("CopyRng").Value 'Subscrip out of range

Sub tm_AtHoc_CEDR_EmployeeRegionRpt()
' 3/21/2022
' 3/15/2022 added code to remove #NULL cells
' https://excel.tips.net/T003068_Inconsistent_Output_for_Empty_Columns_in_a_CSV_File.html
' Copies the source file worksheet to a new worknook. .xlsx files saved as a .csv for this have corrupted
' 2022-02-16 revised to only copy needed cells
' 20210809
' 1-24-2022 changed save to location
' 1-24-2022 revised to not change row 1 titles

    With Application
      .ScreenUpdating = False
      .EnableEvents = True
      .DisplayAlerts = False
      .Calculation = xlManual
    End With ' Application
    Dim LastCol   As Integer
    Dim i         As Long
    Dim LastRow   As Long
    Dim CopyRng   As Range
    Dim oneCell   As Range
    Dim Rng       As Range
    Dim wbPath    As String
    Dim varPath   As String
    Dim fPath     As String
    Dim DestWb    As Workbook
    Dim SourceWb  As Workbook
    Dim ThisWb    As Workbook
    Dim SourceWs  As Worksheet
    Dim ThisWs    As Worksheet
    Set SourceWb = ActiveWorkbook
    Set SourceWs = ActiveSheet

    With SourceWb
    With SourceWs
    Cells.UnMerge
    Range("A1").EntireRow.Delete
    Columns("D:H").Delete
    LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
    LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    Range("A1", Cells(LastRow, LastCol)).Name = "CopyRng"
    Range("CopyRng").Copy ' range is valid $A$1:$C$15843
'---Add a workbook and save as a .cvs file
    Set DestWb = Workbooks.Add
    DestWb.SaveAs "S:\Security (Restricted)\Emergency Management Programs\Operations\AtHoc\Files_for_Upload\CEDR_Data\CEDR_Data_" _
& Format(Now, "mm-dd-yyyy hh mm AM/PM") & ".csv", FileFormat:=6 ' valid
'=== Testing ===
    'https://www.mrexcel.com/board/threads/paste-as-values.1197811/
     DestWb.Worksheets("Sheet1").Range("A1").Value = SourceWs.Range("CopyRng").Value 'Subscrips out of range
'-- more code--

 

[Fluff]

As this is now a totally different question from the op, it needs a new thread. Thanks