Copy from Sheet1&paste in next blank row of Sheet 2

[rlsublime]

Hello:

I am trying to edit the following code, specifically this line:

Range("Table3[Column1]" & Range("Table3[#All]").End(xlUp).Row + 1).Select

I am trying to get the data range from sheet 1 to copy and paste into the row that is blank after the last entry within the table in sheet 2. Thanks

Code Below

Sheets("Data").Select
Range("A3:AD15").Select
Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Selection.Copy
Sheets("Consolidated Data").Select

Range("Table3[Column1]" & Range("Table3[#All]").End(xlUp).Row + 1).Select

Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

[Simon Lloyd]

Just do

Please Login or Register  to view this content.

and forget the rest of the code below Selection.Copy

[rlsublime]

Thanks Simon:

I tried the following but still keep getting an error:

Sheets("Data").Select
Range("A3:AD15").Select
Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Selection.Copy
Selection.Copy Destination:=Sheets("Consolidated Data").Range("A" & Rows.Count).Offset(1, 0)

Thanks for your help

[Simon Lloyd]

remove the first Selection.Copy
and change the last selection.copy line for this

Please Login or Register  to view this content.

[rlsublime]

The following still did not work. I am trying to paste to a table range, specifically , Table 3 in sheet 2. Would I need to include the table 3 name in the range. Thanks

Sheets("Data").Select
Range("A3:AD15").Select
Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Selection.Copy Destination:=Sheets("Consolidated Data").Range("A" & Rows.Count).Offset(1, 0)

[Simon Lloyd]

check my edit above

[rlsublime]

It still did not work when i took out the first selection.copy. I am trying to paste to a table range, specifically , Table 3 in sheet 2. Would I need to include the table 3 name in the range. Thanks

Sheets("Data").Select
Range("A3:AD15").Select
Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Selection.Copy Destination:=Sheets("Consolidated Data").Range("A" & Rows.Count).Offset(1, 0)

[rlsublime]

I even tried the following but it did not work. Thanks

Selection.Copy Destination:=Sheets("Consolidated Data").Range("Table3[Column 1]" & Rows.Count).Offset(1, 0)

[shaukat]

this code works very good, just change the rows and column numbers:

Please Login or Register  to view this content.

[rlsublime]

If i have a table in sheet two, how would I add the data to the table? Thanks.

[Simon Lloyd]

It still did not work when i took out the first selection.copy. I am trying to paste to a table range, specifically , Table 3 in sheet 2. Would I need to include the table 3 name in the range. Thanks

Sheets("Data").Select
Range("A3:AD15").Select
Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Selection.Copy Destination:=Sheets("Consolidated Data").Range("A" & Rows.Count).Offset(1, 0)

I even tried the following but it did not work. Thanks

Selection.Copy Destination:=Sheets("Consolidated Data").Range("Table3[Column 1]" & Rows.Count).Offset(1, 0)

Thats probably because you didn't look at my edit as i indicated

Please Login or Register  to view this content.

anyway why not post a sample workbook, it would be easier

[shaukat]

just modify B2:E range in sheet1 which is source. and modify G column in sheet2 which is destination

[rlsublime]

Here is the file Simon. I am trying to add the data in sheet1 to the table in sheet2 so that the pivot in sheet 3 will be autoupdated.

Thanks a lot

[shaukat]

go here and add to his reputation
http://www.excelforum.com/excel-prog...ode-error.html

[shaukat]

use this code for your case:

Please Login or Register  to view this content.

[Simon Lloyd]

In your example this should work

Please Login or Register  to view this content.

What you have to remember is that a table (or list as you have it) isn't empty even though it appears to have no content.

[rlsublime]

the code you provided just pasted all the data under the table in sheet 2. It still did not paste inside the table.