0

I would like to copy only values (without formulas and formatting) while im merging many Worksheets to one bulksheet but i only know PasteSpecial method and don't know how to apply it to that.

myRange.Copy Destination:=Sheets("Combined").Range("A1").Offset(lastRow).Pastespecial xlPastevalues? Or maybe somehow change range type to value?

I don't know VBA. Only using simple macros on a daily basis. Would be grateful for any help.

Sub Combine()
Dim jCt As Integer
Dim ws As Worksheets
Dim myRange As Range
Dim lastRow As Long
lastRow = 1

Worksheets.Add
Sheets(1).Name = "Combined"

For jCt = 2 To Sheets.Count

    Set myRange = Sheets(jCt).Range(Sheets(jCt).Cells(3, 1), Sheets(jCt).Range("A1").SpecialCells(xlCellTypeLastCell))
    Debug.Print Sheets(jCt).Name, myRange.Address

    myRange.Copy Destination:=Sheets("Combined").Range("A1").Offset(lastRow) 

    lastRow = lastRow + myRange.Rows.Count 


Next
End Sub
braX
  • 11,506
  • 5
  • 20
  • 33
Lukas
  • 11
  • 6
  • You need to make it in two lines. Replace `myRange.Copy Destination:=Sheets("Combined").Range("A1").Offset(lastRow)` with line 1 `myRange.Copy`, line 2 `Sheets("Combined").Range("A1").Offset(lastRow).Pastespecial xlPastevalues` – Vitaliy Prushak Jan 21 '20 at 10:48
  • Does this answer your question? [Excel VBA Copy Paste Values only( xlPasteValues )](https://stackoverflow.com/questions/23937262/excel-vba-copy-paste-values-only-xlpastevalues) – Plutian Jan 21 '20 at 10:49
  • I'm getting sytax error while compiling – Lukas Jan 21 '20 at 10:56
  • @Plutian No, because still i getting errors even if everything should be okay – Lukas Jan 21 '20 at 11:03
  • `myRange.Copy 'line 1' Destination:=Sheets("Combined").Range("A1").Offset(lastRow).PasteSpecial xlPasteValues 'line2' ` can someone tell me what is wrong with that? – Lukas Jan 21 '20 at 11:04
  • You do not need the `Destination:=` part. This is only used if you do a direct copy paste action. If you `.copy` without the destination, the data will be held in your clipboard until you `.paste` or `.pastespecial` on a separate line. – Plutian Jan 21 '20 at 11:09
  • Great! Thank's a lot. – Lukas Jan 21 '20 at 11:39

0 Answers0