Copy Data

[Mas]

Hi All,

I have several worksheets in my workbook.
i want to copy filtered data(if field 4 ="N") from each
sheet based on the sheet name (if the sheet name
contains "02") to last sheet (sheet45)

how can I do this using code?

Thanks
Mas

 

[Tom Ogilvy]

Sub CurMonth()
Dim rng as Range, sh as Worksheet
set rng = Sheets("CasCrd").Rows("2:2")
Sheets("CasCrd").Range(rng, rng.End(xlDown)).Delete Shift:=xlUp

For Each Sh In WorkSheets
If Instr(1,Sh.Name, Right( _
Sheets("Interface").[C2],2),vbTextCompare) > 0 then
sh.Range("A1").AutoFilter Field:=4, Criteria1:="N"
sh.Autofilter.Range.Resize(,42).Offset(1, 0).Copy _
Destination:=Sheets("CasCrd"). _
Range("A65536").End(xlUp).Offset(1, 0)
Sh.AutoFilterMode = False
End If
Next
End Sub

If it doesn't copy the data you expect, then you need to check if what

Right(Sheets("Interface").[C2],2)

produces and if that value is found in any of the sheet names.

 

[Mas]

Hi Tom,

Thanks for your reply.

It dont work?!!!

my Right(Sheets("Interface").[C2],2)returns "04"
and I have two sheets named "M04Ca" and "M04Cr"
I am expecting both these sheets to filter and copy data
to last sheet.

thanks again

Mas

-----Original Message-----
Sub CurMonth()
Dim rng as Range, sh as Worksheet
set rng = Sheets("CasCrd").Rows("2:2")
Sheets("CasCrd").Range(rng, rng.End(xlDown)).Delete Shift:=xlUp

For Each Sh In WorkSheets
If Instr(1,Sh.Name, Right( _
Sheets("Interface").[C2],2),vbTextCompare) > 0 then
sh.Range("A1").AutoFilter Field:=4, Criteria1:="N"
sh.Autofilter.Range.Resize(,42).Offset(1, 0).Copy _
Destination:=Sheets("CasCrd"). _
Range("A65536").End(xlUp).Offset(1, 0)
Sh.AutoFilterMode = False
End If
Next
End Sub

If it doesn't copy the data you expect, then you need to check if what

Right(Sheets("Interface").[C2],2)

produces and if that value is found in any of the sheet names.

--
Regards,
Tom Ogilvy

Hi All,

I have several worksheets in my workbook.
i want to copy filtered data(if field 4 ="N") from each
sheet based on the sheet name (if the sheet name
contains "02") to last sheet (sheet45)

how can I do this using code?

Thanks
Mas

.

 

[Tom Ogilvy]

The code worked fine for me. Are you sure the names of the Sheets have Zero
4 and not the letter O.

--
Regards
Tom Ogilvy

Hi Tom,

Thanks for your reply.

It dont work?!!!

my Right(Sheets("Interface").[C2],2)returns "04"
and I have two sheets named "M04Ca" and "M04Cr"
I am expecting both these sheets to filter and copy data
to last sheet.

thanks again

Mas

-----Original Message-----
Sub CurMonth()
Dim rng as Range, sh as Worksheet
set rng = Sheets("CasCrd").Rows("2:2")
Sheets("CasCrd").Range(rng, rng.End(xlDown)).Delete Shift:=xlUp

For Each Sh In WorkSheets
If Instr(1,Sh.Name, Right( _
Sheets("Interface").[C2],2),vbTextCompare) > 0 then
sh.Range("A1").AutoFilter Field:=4, Criteria1:="N"
sh.Autofilter.Range.Resize(,42).Offset(1, 0).Copy _
Destination:=Sheets("CasCrd"). _
Range("A65536").End(xlUp).Offset(1, 0)
Sh.AutoFilterMode = False
End If
Next
End Sub

If it doesn't copy the data you expect, then you need to check if what

Right(Sheets("Interface").[C2],2)

produces and if that value is found in any of the sheet names.

--
Regards,
Tom Ogilvy

Hi All,

I have several worksheets in my workbook.
i want to copy filtered data(if field 4 ="N") from each
sheet based on the sheet name (if the sheet name
contains "02") to last sheet (sheet45)

how can I do this using code?

Thanks
Mas

.