How do I use pasteSpecial

Question

I dont seem to be able to use PasteSpecial / know where to add it. I want to cut and paste some date (date, name, job-done etc) from a Job input sheet (the fields will clear after the Submit button is pressed) into another sheet, i want the destination sheet to move down a row each time so it collects all the info in 1 sheet. My macro does work and i have assigned it to the submit button but it also moves the cell format can anyone advise what i need to do ? PS I am a complete VBA beginner..!

my current code is below, I also tried using range instead of copy and paste which resolved the problem of the format moving with the data but then i could not make it move down a row on the target sheet, is range a better solution to copy and paste ? thanks in advance to anyone who can help

Sub submit()
'
' Submit Macro
'

'
    Dim wsCopy As Worksheet
    Dim wsDest As Worksheet
    Dim lCopyLastRow As Long
    Dim lDestLastRow As Long

    Set wsCopy = Workbooks("HEATHER.xlsx").Worksheets("JOB")
    Set wsDest = Workbooks("HEATHER.xlsx").Worksheets("2019")
    Range("C5:G5").Select
    Selection.Cut
    Sheets("2019").Select
    CopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "B").End(xlUp).Row
    Selection.Offset(1, 0).Select
    ActiveSheet.Paste
    ActiveWorkbook.Worksheets(1).Activate
End Sub

Instead of `ActiveSheet.Paste`, do `Selection.PasteSpecial`? However, you should really read this on [how/why to avoid relying on Select/Activate](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — David Zemens, Jul 16 '19 at 14:45
And [this](https://stackoverflow.com/questions/19351832/copy-from-one-workbook-and-paste-into-another/19352099#19352099) on how to copy values only from one workbook/worksheet to another. — David Zemens, Jul 16 '19 at 14:46

score 1 · Accepted Answer · answered Jul 16 '19 at 14:54

1

A few notes. Try avoid using .Select and .Activate where you can. You also fail to set lDestLastRow in your code. Also not sure how/ why you have lCopyLastRow.

See below code (not tested). Should be what you are looking for.

Sub submit()

    Dim wsCopy As Worksheet
    Dim wsDest As Worksheet
    Dim lCopyLastRow As Long
    Dim lDestLastRow As Long

    Set wsCopy = Workbooks("HEATHER.xlsx").Worksheets("JOB")
    Set wsDest = Workbooks("HEATHER.xlsx").Worksheets("2019")

    lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "B").End(xlUp).Row
    lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Row + 1

    wsCopy.Range("C5:G5").Copy
    wsDest.Range("A" & lDestLastRow).PasteSpecial xlValues

    wsCopy.Range("C5:G5").ClearContents
    Application.CutCopyMode = False

    ActiveWorkbook.Worksheets(1).Activate

End Sub

answered Jul 16 '19 at 14:54

Dean

2,326
3
13
32

thanks Dean, thats great and works a treat !! thanks for your quick response. Jim – jimijim Jul 16 '19 at 15:25
@jimjim it's a pleasure. please mark this as your answer to close the question.Regards – Dean Jul 16 '19 at 15:37
Hi, I wanted to use =TODAY() in the date input field (C5) , currently this get removed every time i run it, is it possible for it not to remove the formula ? – jimijim Jul 16 '19 at 15:48
@jimjim yes, adjust the line with `.ClearContents` to take that into account. – Dean Jul 16 '19 at 15:53

How do I use pasteSpecial

1 Answers1