CODEWITHSUNDEEP
vbaexcelcopy-paste
Faisal Khan
Faisal Khan

Reputation: 17

Paste values using vba

I have a code which selects all the data from a specific sheet in workbook A (names as 'fn' in the code) and copies it in a specific sheet in workbook B. What I want to do is to only PASTE VALUES, i.e. paste the values copied from the copy sheet while maintaining/converting (to) the format of the table in the past sheet, here is my code - 

Set copySheet = copyBook.Sheets(fn) 'Points to the sheet in the raw data file
Set pasteBook = ThisWorkbook 'Sets the file where we are supposed to paste to as the template file
Application.DisplayAlerts = False
Set pasteSheet = pasteBook.Sheets("Transaction") 'Points to the Transaction sheet on the template
lastRow = copySheet.Cells(Rows.Count, 1).End(xlUp).row 'Computes the last row on the raw data file


'Copies raw data to tab/sheet in template
copySheet.Activate
Application.ScreenUpdating = False
For column = 1 To 27
    For row = 2 To lastRow
    cp = copySheet.Cells(row, column)
    pasteSheet.Cells(row + 211, column).Value = cp
    Next
Next
Application.ScreenUpdating = True


copyBook.Close 'Closes the raw data file
End If

NOTE - I would really prefer that I just make a minor change to the code above, not make another code entirely. Thanks!

Upvotes: 0

Views: 727

Answers (3)

Divyesh M.
Divyesh M.

Reputation: 66

Update your loop with this:

For column = 1 To 27
    For row = 2 To lastRow
        copySheet.Cells(row, column).copy Destination:=pasteSheet.Cells(row + 211, column)
    Next
Next

Upvotes: 0

peege
peege

Reputation: 2477

Try this. You can also skip activating the sheet, because you are just copying the data.

Application.ScreenUpdating = False
For column = 1 To 27
    For row = 2 To lastRow

    pasteSheet.Cells(row + 211, column) = copySheet.Cells(row, column).Value

    Next
Next

Upvotes: 0

BrinkDaDrink
BrinkDaDrink

Reputation: 1798

 cp = copySheet.Cells(row, column)
pasteSheet.Cells(row + 211, column).Value = cp

Change to

copySheet.Cells(row, column).Select  
Selection.Copy 
pasteSheet.Cells(row + 211, column).Select 
Selection.PasteSpecial Paste:=xlPasteValues 

Application.CutCopyMode = False

Application.CutCopyMode here being set to false just takes your program out of copy mode.

Upvotes: 0

Related Questions