Paste just the Value

[Bull]

Using the code below, is there a way to paste special just the value,
and not the formatting.
I have the below taking data from Sheet (DATA) and pasting it in Sheet
(LOG). Right now it pastes the borders, value, and formatting.

Sub test()
With Worksheets("LOG")
Worksheets("DATA").Range("FULLNAME").Copy _
Destination:=.Cells(Rows.Count, 1).End(xlUp)(2)
Worksheets("DATA").Range("DEPT").Copy _
Destination:=.Cells(Rows.Count, 2).End(xlUp)(2)
End With
End Sub

Bull

 

[Dave Peterson]

Sub test()
With Worksheets("LOG")

'copy|paste special|values in code
'and I like .offset(1,0) better than (2)
Worksheets("DATA").Range("FULLNAME").Copy
.cells(.rows.count,1).end(xlup).offset(1,0).pastespecial paste:=xlpastevalues

....

End Sub

 

[Rick Rothstein \(MVP - VB\)]

You could try it this way...

Sub test()
With Worksheets("Sheet2")
.Cells(Rows.Count, 1).End(xlUp)(2) = _
Worksheets("Sheet1").Range("FULLNAME")
.Cells(Rows.Count, 2).End(xlUp)(2) = _
Worksheets("Sheet1").Range("DEPT")
End With
End Sub

Note: Line continuations used to avoid your newsreader from splitting the
two lines in the With/EndWith block at odd locations.

Rick

 

[Rick Rothstein \(MVP - VB\)]

Actually, I rushed the answer I posted... while not necessary because it is
the "default property", I do not like to rely on default properties and
meant to 'tack on' the .Value reference to those statements...

Sub test()
With Worksheets("Sheet2")
.Cells(Rows.Count, 1).End(xlUp)(2).Value = _
Worksheets("Sheet1").Range("FULLNAME").Value
.Cells(Rows.Count, 2).End(xlUp)(2).Value = _
Worksheets("Sheet1").Range("DEPT").Value
End With
End Sub

Rick

 

[Bull]

Actually, I rushed the answer I posted... while not necessary because it is
the "default property", I do not like to rely on default properties and
meant to 'tack on' the .Value reference to those statements...

Sub test()
  With Worksheets("Sheet2")
    .Cells(Rows.Count, 1).End(xlUp)(2).Value = _
                          Worksheets("Sheet1").Range("FULLNAME").Value
    .Cells(Rows.Count, 2).End(xlUp)(2).Value = _
                          Worksheets("Sheet1").Range("DEPT").Value
  End With
End Sub

Rick

message





- Show quoted text -

Thanks a lot guys...Appreciate the help. Bull

 

[Bull]

Actually, I rushed the answer I posted... while not necessary because it is
the "default property", I do not like to rely on default properties and
meant to 'tack on' the .Value reference to those statements...

Sub test()
  With Worksheets("Sheet2")
    .Cells(Rows.Count, 1).End(xlUp)(2).Value = _
                          Worksheets("Sheet1").Range("FULLNAME").Value
    .Cells(Rows.Count, 2).End(xlUp)(2).Value = _
                          Worksheets("Sheet1").Range("DEPT").Value
  End With
End Sub

Rick

message





- Show quoted text -

Is there a way to use the above solution to paste the values into
another sheet in another workbook. Say a workbook called test,
located on C:\test.xls, and a sheet called log in the C:\test.xls
workbook.

Thank you again, lots of help. I am sure you all are busy...I
appreciate your time.
Bull