• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Unexpected Error in the code.

J

Jagdev Singh

Active Member
Hi Experts

I have the below code which merge data from 2 sheets in the same workbook into one sheet. It works perfectly fine in most of the instances, but throw errors when I filter the data from 2 sheets and then run the code.

Code: 
Sub MergeSheets()
Application.ScreenUpdating = False
    Sheets("Merge").Select
    Rows("2:" & Rows.Count).ClearContents
    Sheets("Client").Select
    Range("A2:X2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Merge").Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    Sheets("UW1").Select
    Range("A2:Y2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Merge").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    Sheets("UW2").Select
    Range("A2:Y2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Merge").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    Range("A1").Select
    Call cleanData
Application.ScreenUpdating = True
    MsgBox "Collation of data complete", vbInformation
End Sub

The code throws error at this juncture

Code: 
ActiveCell.Offset(1, 0).Select
    Sheets("UW2").Select

Is there any issue if we filter the data in the sheet and then try to run this code. Is there any turn-around to deal with this issue.

Regards,
JD
 
Hi Jaggi ,

You say the error is thrown at a particular juncture , but this juncture can only be one line of code , not 2 lines as you have posted.

If I assume that it is the following line which is generating the error :

ActiveCell.Offset(1, 0).Select

this generally happens if the active cell is on row 1048576.

Since this is the last row on a worksheet , trying to select a cell on the next row will always generate an error.

The reason for this is not really the line which is generating the error , but the following line :

Selection.End(xlDown).Select

If the selected cell is a blank cell , and all cells below that cell are blank , then the above line of code will take you to row 1048576.

All in all , the code you have posted is not an example of good programming , and can do with modifications.

Narayan
 
Hi Narayan

You are correct since it is repeated in the code so I thought to paste the line next to the error line. Could you please help me with the modification to fix this issue.

Regards
JD
 
Hi ,

If you can upload your workbook with the data , it will be easier to suggest a solution. As it stands , removing the line will work if the line is blank , but where the active cell does have data , the line is required so that the next copy + paste does not overwrite the last line of data.

Without the workbook , it is difficult to rewrite the code.

Narayan
 
Hi Narayan

Please find the sample file attached with the thread. The column length will vary from sheet-to-sheet.

Regards,
JD
 
Hi ,

As it stands your code does not error out ; can you describe the steps I will have to follow to generate the error ? Otherwise , uploading the file would not have served any purpose.

Narayan
 
Hi Narayan
The actual data in 3 sheets used to be in 1000’s. I need to apply filter on a particular column in all the three sheets and run this code. It works without any error most of the time, but on few instances it throws an error on a particular line.
The error always occurs when I frequently change the filters in three sheets and run the code. I think you were right about the selection line which could be the case of the error.
Regards,
JD
 
Hi ,

The code will work when there is data so that the usage :

Selection.End(xlDown).Select

results in a reasonable range.

When you apply a filter , and the filter returns no data ( there are no visible rows ) , then there is every chance that the code will error out.

The code has not been written to take into account filtered rows.

If you are going to run it in this scenario , you have to live with the problem.

Narayan
 
Hi ,

No.

You need to describe exactly what you wish to do , in enough detail , so that the appropriate code to do this can be written. The code that you have posted looks like it has been generated using the macro recorder , which is notorious for being verbose.

Normally , no one writes code in this manner , and to tweak it is not advisable.

Narayan
 
Hi Narayan

My requirement is to merge data from 3 sheets that is client, UW1 and UW2 into the sheet named Merge

Also, it should merge the filtered data as well. Say the column A in client, UW1 and UW2 is name column. If I filter the name Jagdev in all these 3 sheets and run the macro, it should merge the filtered data in the Merge sheet.

Please let me know if things are unclear.

Regards
Jaggi
 
Hi ,

Are you going to filter in all the sheets ? What happens if you filter in 2 sheets and not the third ?

Are you going to filter only on column A ?

What will happen if the filters on the different tabs are on different columns , such as column A in one sheet , column C in another and column F in the third ?

Please explain in full detail , since I don't think anyone wants such variations to be pointed out after the code has been written.

Narayan
 
Hi Narayan

The filter will be set on all the 3 sheets. There are 3 specific columns on which i will apply filter and these columns will be available on all the 3 sheets. In the sample file we can consider column A B and C. I will make the respective amendments in the original data.

Regards
Jaggi
 
Hi Narayan

To make it bit clear the filter will be set on the same column on all the three sheet say if I apply in on column A on 1st sheet then the same will be applied on 2nd and 3rd sheet as well. The same way for other filters as well.

Regards,
Jaggi
 
Hi Narayan
Thanks for the code.
The issue is it is picking only the Second row (Considering First row as heading) from Client sheet and UW1 Sheet respectively and from the UW2 sheet it is pulling the entire filtered data.
I applied the filter on Column B and ran the code. It pull the data in the merge sheet, but with above stats i.e. 1st row from Client and UW1 and entire filtered data from UW2. In short the macro missed pulling the data from Client and UW1 sheet excluding the 2nd Row.
Regards,
JD
 
Hi Narayan

I even test the sample macro you have attached in the thread. The out come is same only 1st row day is capture from both client and UW1 sheet and the entire data from UW2 sheet.

Regards,
Jaggi
 
Hi Narayan

Thanks for the macro it is working fine. Can we do something to increase its speed. Currently it is merging 1000's of entries and consumes 3-4 minutes to complete the data from 3 sheets into Merge sheet. Is it possible to reduce this time frame.

Regards,
JD
 
You must log in or register to reply here.
Back
Top