Run-time error 1004 in macro for merging data across tabs

[Rajni]

HI

I'm trying to merge data from 3 tabs into a new tab. For the first two tabs
it is correctly copying rows and pasting in new tab (alldefects) but for tab3 it is giving this error.

While debugging I could see that its selecting huge no. of rows instead of
only single row (as Tab3 has only 1 row for now).

Error is >>
Run-time error '1004'
The information cannot be pasted because the Copy area and the Paste area are
not the sa,e size and shape. Try one of the following.
** Click a single cell, and then paste.
** Select a rectangle that's the sa,e size and shape, and then paste.

Code for Tab3 (similar code is used for all three tabs) is >>
Query3sheet.Activate
' check if data is there to copy
if ActiveSheet.Range("A2") <> "" then
'select all rows with data till end
ActiveSheet.Range("A2").Select
ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
Selection.copy
AllDefectssheet.Activate
'get the last cell in this tab and paste....
LastCell = ActiveCell.SpecialCells(xlLastCell).Row
Range("A" + Trim(Str(LastCell+1))).Select
Activesheet.paste ' Error coming here.....
end if

 

[Manohar M G]

Hi..

Can you explain briefly.. what you need it.. what is tab means.. cell or row..

 

[Rajni]

I have three excel tabs 'Query1sheet', 'Query2sheet' & 'Query3sheet'. I want to copy rows from each tab and paste to 'AllDefectssheet' through a macro. Above code is to copy data from 'Query3sheet' to 'AllDefectssheet'. Instead of copying 1 row it is also copying huge number of blank rows from that Query3sheet.

 

[Manohar M G]

Hi,

Just Check this macro... if u got any problem let me know..

if ActiveSheet.Range("A2") <> "" then
'select all rows with data till end
ActiveSheet.Range("A2").Select
ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
Selection.copy
AllDefectssheet.Activate
'get the last cell in this tab and paste....
LastCell = ActiveCell.SpecialCells(xlLastCell).Row
Row_no = ("A" + Trim(Str(LastCell+1)))
ActiveSheet.Range(Row_no).Select
Activesheet.paste

 

[Rajni]

I tried with the changes you suggested but its not working.

Just to give you background the same code is working for other two sheets but somehow in Query3sheet its selecting blank rows too where as it has only 1 row.

I know the problem is in the following statements which are selecting rows with data -
ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
ActiveSheet.Range(Selection, Selection.End(xlDown)).Select

Need help to select only the data rows and not blank rows at the end of it.

 

[Manohar M G]

Hi.

Ya.. u r right... what u r written the code it will select till the end. if it is a blank r not it will not consider... So, u have to change the macro.. u should not add that 2 lines...

 

[Rajni]

I got in some forum that those two lines select the non blank rows till end. If the sheet has 10 rows it will select all 10. This is what is happeneing in my other two sheets (Qyery1sheet and Qyery2sheet with the same path of code).

1. Do you know the way to select only 'non blank' rows through this current approach?
2. Do you know any other way where i can select the rows till end without blank rows?

 

[Manohar M G]

1. Do you know the way to select only 'non blank' rows through this current approach?
ActiveSheet.Cells(row,column).Value <> ""

It will check if the condition is non blank...

 

[Rajni]

What if I have 100 rows to copy and this count will always vary..

 

[Manohar M G]

Use "Do While loop" or "For loop"..

1. First Check row 1 if it a blank leave it go to 2nd row. or. if it is any value copy it .
2. when u copy 1st row and then move on to the 2nd row...