Thread: Filter,copy and paste in excel

I 'm working on an excel that contains a macro to color the cells based on the columnS.
This part of the code works perfectly.

Now I need another macro, that could filter the rows based on each color in different columns,copy the filtered rows from the current workshhet and paste it in a destination worksheet.

The destination workbook contains different worksheets for rows filtered based on the color.
eg:
The rows with orange color(color 44) has to be pasted in the Due.Amt worksheet of the destination workbook.
The rows with green color(color 35) has to be pasted in the Expenses worksheet of the destination workbook.
and so on for the different colors(the coloumn name in the source worksheet and the worksheet name in the destination workbook will be the same, so the paste shoud be done respectively).

The destination worksheet may/may not contain datas, in that case, the paste as to be done in the next available empty row.
The previous datas should not be deleted/overwritten in the destination worksheet.

Please find the attached workbook.

Any help and suggestions will be helpful.
Thanks in advance.

Hi,

Give this a try,

Post back any issues.

.

Thanks a lot for the code.

I get the following error:

run time error 9
script out of range

I get this error in the below line:

Set Thatwb = Workbooks("C:\destination.xlsx")

I have added the "C:\" to set the path of the excel location.

Thanks in advance.

Originally Posted by vijay2482

Thanks a lot for the code.

I get the following error:

run time error 9
script out of range

I get this error in the below line:

Set Thatwb = Workbooks("C:\destination.xlsx")

I have added the "C:\" to set the path of the excel location.

Thanks in advance.

This pcs of code set the workbook "to"... this was the workbook name in your post example.You need to change the name of the destination.xlsx to whatever the real name is.

However for example if the workbook with the 4 sheets you want to copy the info to is located on your C:\ Drive and Not open when you want to run the code, then use this line in place of the line above

Set Thatwb = Workbooks.Open("C:\destination.xlsx")

Post back any issues,

.

Thanks for your reply.

I modified the code as you said.
Now I get the below error:

Run time error 1004
Auto filter method of range class failed.

Line of code that produced error:

Range("A2:P2").AutoFilter: Range("P2").AutoFilter Field:=16, Criteria1:="44"

..........

Originally Posted by vijay2482

Thanks for your reply.

I modified the code as you said.
Now I get the below error:

Run time error 1004
Auto filter method of range class failed.

Line of code that produced error:

Range("A2:P2").AutoFilter: Range("P2").AutoFilter Field:=16, Criteria1:="44"

in the sample workbook (The workbook you placed the code in) you only have one sheet and that sheet is the first sheet in the workbook correct?

If so the first row is blank and the second row has your headers and your data starts on row 3 correct?

if so when you open this sample workbook to run the code is there already a filter in place?

When you step through the code on the sample workbook does the Destination workbook open now?

Is there any data in column (P) ? Is the sheet protected?


Just trying to see what you are seeing.

.

answers to your questions:

in the sample workbook (The workbook you placed the code in) you only have one sheet and that sheet is the first sheet in the workbook correct? YES

If so the first row is blank and the second row has your headers and your data starts on row 3 correct? YES

if so when you open this sample workbook to run the code is there already a filter in place? NO. THE destination.xlsx OPENS AND i CAN FIND THE FILTERS ON THE FIELDS IN THE Due.Amt SPREADSHEET.

When you step through the code on the sample workbook does the Destination workbook open now? YES

Is there any data in column (P) ? YES
Is the sheet protected? DO YOU MEAN,WHETHER THE SHEET IS PASSWORD PROTECTED? IF SO, THE ANSWER IS "NO"

Thaks in advance for any help.

I'm not sure what is going on, it must be something with your original workbooks.

Did you happen to try opening up the destination workbook from your 1st post and open my copy of Sample from post 3 and try to run the code with just those 2 workbooks?

Was the result what you expected? If so then we need to figure out what the difference is between the posted workbooks and the originals.

.

I used the copy of sample workbook and the destination workbook from my PC.
I will try to use the copy of sample and destination my the post and compile and get back to you with the results tomorrow.

Should the name of the source file be sample.xls or copy of sample.xls?

Thanks a lot for spending your time to help.

I tested with the copy of sample and destination.xlsx files and I get the below error at:
"select method of range class failed"

at the following line:

.Range("A2").Select

Dont know where I'm going wrong.
I have attached the files.
The P column in destination containes the color index value during exceution and later produced the error and then when I saved the file I get this as the value in the cells of column P.
"NAME ?"
Please help to identify and solve this issue.
Thaks in advance.

Hi,

Add this line

Thiswb.Activate

After :

Sub Filter1()
Dim Thiswb As Workbook
Dim Thatwb As Workbook
Dim ThisLrow As Long
Dim ThatLrow As Long
Dim R1 As Range, R2 As Range, R3 As Range, R4 As Range
Dim WKS As Worksheet
Application.ScreenUpdating = False
 
Set Thiswb = ThisWorkbook
Set Thatwb = Workbooks.Open("C:\destination.xlsx")
 
'PLACE NEW CODE HERE

.

Thanks a lot Mike.

It works perfect..

I have a question:
Should I use the below lines in all the sub's(Filter2,Filter3,Filter4) or only in Filter1 sub?

Set Thatwb = Workbooks.Open("C:\destination.xlsx")
Thiswb.Activate

One more question:
Where there are n number of sheets in the excel wotkbook and I I want to run the same Filter1 sub once for each sheet, do I need to make any changes in the existing code?
Please find the attached sample file with 3 sheets.

Thanks for assisting so far...

Should I use the below lines in all the sub's(Filter2,Filter3,Filter4)?

No, You only need to use Activate the one time, Because we changed the code to open the destination workbook when that takes place that makes the destination workbook the activeworkbook and thats why the code wasn't working. Now that we activated the sample workbook the code should loop through with out an issue...


Where there are n number of sheets in the excel wotkbook and I I want to run the same Filter1 sub once for each sheet, do I need to make any changes in the existing code?
I will need to make some additional changes for this.

I'll post back Shortly.

.