VBA copy and paste as values

[djossh]

Hi.. the code below are working fine except that I need to have it in values not in formula. can anyone help..Thanks


hereis my code:

Private Sub okbut_Click()


Dim startdate As Date, enddate As Date
Dim rng As Range, destRow As Long
Dim shtSrc As Worksheet, shtDest As Worksheet
Dim c As Range


Set shtSrc = Sheets("SUMMARY")
Set shtDest = Sheets("Transmittal")


destRow = 20 'start copying to this row


startdate = TRANSMIT.transfrom.Value
enddate = TRANSMIT.transto.Value


'don't scan the entire column...
Set rng = Application.Intersect(shtSrc.Range("D:D"), shtSrc.UsedRange)


For Each c In rng.Cells
If c.Value >= startdate And c.Value <= enddate Then

c.Offset(0, 0).Resize(1, 1).Copy shtDest.Cells(destRow, 1)
c.Offset(0, 1).Resize(1, 1).Copy shtDest.Cells(destRow, 2)
c.Offset(0, -2).Resize(1, 1).Copy shtDest.Cells(destRow, 3)
c.Offset(0, -1).Resize(1, 1).Copy shtDest.Cells(destRow, 4)
c.Offset(0, 4).Resize(1, 1).Copy shtDest.Cells(destRow, 5)
c.Offset(0, 11).Resize(1, 1).Copy shtDest.Cells(destRow, 6)

destRow = destRow + 1


End If
Next




End Sub

 

[vcoolio]

Hello Djossh,

Try changing this block of code:-

c.Offset(0, 0).Resize(1, 1).Copy shtDest.Cells(destRow, 1)
            c.Offset(0, 1).Resize(1, 1).Copy shtDest.Cells(destRow, 2)
            c.Offset(0, -2).Resize(1, 1).Copy shtDest.Cells(destRow, 3)
            c.Offset(0, -1).Resize(1, 1).Copy shtDest.Cells(destRow, 4)
            c.Offset(0, 4).Resize(1, 1).Copy shtDest.Cells(destRow, 5)
            c.Offset(0, 11).Resize(1, 1).Copy shtDest.Cells(destRow, 6)

to

c.Offset(0, 0).Resize(1, 1).Copy
shtDest.Cells(destRow, 1).PasteSpecial xlValues

c.Offset(0, 1).Resize(1, 1).Copy
shtDest.Cells(destRow, 2).PasteSpecial xlValues

c.Offset(0, -2).Resize(1, 1).Copy
shtDest.Cells(destRow, 3).PasteSpecial xlValues

c.Offset(0, -1).Resize(1, 1).Copy
shtDest.Cells(destRow, 4).PasteSpecial xlValues

c.Offset(0, 4).Resize(1, 1).Copy
shtDest.Cells(destRow, 5).PasteSpecial xlValues

c.Offset(0, 11).Resize(1, 1).Copy
shtDest.Cells(destRow, 6).PasteSpecial xlValues

I hope that this helps.

Cheerio,
vcoolio.

 

[Logit]

.
Untested here ... see if this works :

shtDest.Cells(destRow, 1).value = c.Offset(0, 0).Resize(1, 1).value
shtDest.Cells(destRow, 2).value = c.Offset(0, 1).Resize(1, 1).value
shtDest.Cells(destRow, 3) = c.Offset(0, -2).Resize(1, 1).value
shtDest.Cells(destRow, 4) = c.Offset(0, -1).Resize(1, 1).value
shtDest.Cells(destRow, 5) = c.Offset(0, 4).Resize(1, 1).value
shtDest.Cells(destRow, 6) = c.Offset(0, 11).Resize(1, 1).value

Of course you'll still need the code previous to and following the above suggested change.

 

[djossh]

Thank You so much.. that was perfect....

 

[djossh]

Thank You so much... that was perfect

 

[djossh]

Thanks...

 

[vcoolio]

You're welcome djossh. I'm glad that we were able to help.

Cheerio,
vcoolio.

 

[Logit]

.
You are welcome.