Excel VBA How to copy data only from one sheet and paste values only into another sheet
Posted on 2010-01-08
This may be simple to some of you experts (which is why I"m here ;o) but as beginner I'm struggling. I'm trying to copy Data only (no Header (first row) and no total row (last row) from a second table within the same workbook. My broken code below copies over the data with header and last row, and in addition, which I can't figure out why it shift's down one and over one data block of data and copies the data again, and again, etc. So two things I'm looking to accomplish, 1) to copy data only, and 2) so perform just one paste values only data activity.
Much thanks for the help ! !
Here's the code broken I'm using
Dim rTable As Range
Dim lHeadersRows As Long
Dim intRow As Long
Set rTable = Sheet2.ListObjects("TblData2").Range.SpecialCells(xlCellTypeVisible)
lHeadersRows = rTable.ListHeaderRows
If lHeadersRows > 0 Then
Set rTable = rTable.Resize(rTable.Rows.Count - lHeadersRows)
Set rTable = rTable.Offset(1)
intRow = Sheet3.Cells(Sheet3.Rows.Count, 1).End(xlUp).Row + 1