How to copy rows with values into another worksheet?

[rlekkala]

I have a worksheet that has rows. I want to be able to copy all rows excluding the first that have values (lets say in column A) and paste them in to the new sheet from row 2
Is there a macros that can do this?

 

[lenze]

You can write a macro to to this. There are several examples on the board, but why not just use AutoFilter for non-blanks in Column "A" and copy to the new sheet?

lenze

 

[rlekkala]

You can write a macro to to this. There are several examples on the board, but why not just use AutoFilter for non-blanks in Column "A" and copy to the new sheet?

lenze

The new sheet is synchronized with SharePoint list. The rows in the new sheet are also in table format (create table done) that actually allowed us to synch the table with sharepoint list.
Adding whole sheet is not possible as the top row cannot be updated due to the synch.
Also adding whole sheet is expanding the table to whole sheet with unnecessary columns and rows
the table as of now is Auto expandable when the rows are copied

 

[rlekkala]

Ok, the below code copies rows with values only into an other sheet.
Sub exa()
Dim lLRow As Long

With Sheet1
lLRow = .Range("A" & Rows.Count).End(xlUp).Row

If Not lLRow > 1 Then Exit Sub

.Range("A2:A" & lLRow).SpecialCells(xlCellTypeVisible).EntireRow.Copy _
ThisWorkbook.Worksheets("Sheet2").Cells(2, 1)

End With
End Sub

But Copying entire row is expanding the table on he other sheet across columns. how do I limit to copy only the columns that have values? or choose a range of columns that need to be copied?