Copy all the cells automatically to another sheet

[shaahmed]

I need help. I need to figure out how to pull <vb_highlight>data</vb_highlight> from one spreadsheet and get all of the information in the row only if it contains a <vb_highlight>specific</vb_highlight> word in a different worksheet. How can I do this?
eg: first sheet named as entry and I enter A1 = month B1= name C1=Age D1=salary and I have there 12 month worksheet name as January, February, March, etc. I need the first page entry month column is January so I need all other cells that means B,C,D are copy to worksheet January if there is February so copy to Worksheet February



Please help me

 

[My Aswer Is This]

None of the above codes will work because you did not explain that the layout in your original sheet is not the same as in your monthly sheets.

Why all the columns of data in your master sheet is laid out one way but in your months sheets it's laid out another way makes things more difficult. And in neither of your images does it shown The column letters.


For example in image (1) we may assume Month is in column "A"
and in your month sheet it appears as if Month would go into column "B"


But again this is just an assumption which is something that may not be true.

You should have given more details in your original post.

 

[shaahmed]

hello

sorry I am a binger in excel please help me

this is my first page .. please explain were I put the above code...

Month	ID No	Name	Date	Receipt No	CA	IJ	E.Income	Expense	Remarks
November	KHFA0001	Abdul Vasih	06/11/2015	236/13584	10.000	2.000			2015/16
November	KHFA0002	Adil S	16/12/2016	2xx/12546	2.000	1.000			2016/17
November	KHFA0003	Afsal	18/12/2016	2sxd1245/*	15.000	10.000			2018/19
December	KHFA0004	Aseem	15/12/2016	1245/s	52.000	20.000			2020/2001
December	KHFA0005	Baber	03/12/2016	rt1253	2.000	1.000			20124
December	KHFA0006	Kabeer	02/01/2016	sdr123	5.000	2.000			321321
December	KHFA0007	Khamarudeen	11/04/2016	tre254	2.000	1.000			zscsfcx

<tbody>
</tbody>

this is my month page

ID No	DATE	RECEIPT NO.	DONOR'S NAME	CA	IJ	Income	Expense	TOTAL	Year	Remarks
	TOTAL		0.000				Office Use
TOTAL COLLECTED		TOTAL EXPENSE		SIGNATURE
NAME & SIGNATURE.	CASH SEND TO CENTER		CASH RECEIVED FROM CENTER		SIGNATURE
DATE:	BALANCE CASH IN HAND		BALANCE CASH IN HAND		DATE:

<tbody>
</tbody>

 

[MARK858]

Try the code below, change the name of the sheet Master to the name of your sheet in the first image (in future include sheet names and ranges).
The code assumes that both images start in cell "A1".


To insert the code press ALT + F11, if the window is blank paste the code there (if not click Inset - Module).

Please note that if you are copying more than 7 rows it will overwrite data.

Sub Filterit()
Dim j As Long, myCrit As String
Application.ScreenUpdating = False
For j = 1 To 12
myCrit = MonthName(j)
    With Sheets("Master").Range("A1:J" & Sheets("Master").Range("A" & Rows.Count).End(xlUp).Row)
        .AutoFilter Field:=1, Criteria1:=myCrit
        
        On Error Resume Next
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy _
                Sheets(myCrit).Range("A:A").SpecialCells(xlCellTypeBlanks).Cells(1)
        On Error GoTo 0
        .AutoFilter
    
    End With
Next
Application.ScreenUpdating = True
End Sub

 

[MARK858]

If it is only columns A to D change

With Sheets("Master").Range("A1:J" & Sheets("Master").Range("A" & Rows.Count).End(xlUp).Row)

to

With Sheets("Master").Range("A1:D" & Sheets("Master").Range("A" & Rows.Count).End(xlUp).Row)

I also now notice that your headings in the "Months" sheets are in a different order to what I am calling the "Master" sheet.
Can you not change the order of either to match as that is going to make it more difficult to code (and without using another method slower)?

 

[My Aswer Is This]

That is the same problem I pointed out in post #11

If it is only columns A to D change

With Sheets("Master").Range("A1:J" & Sheets("Master").Range("A" & Rows.Count).End(xlUp).Row)

to

With Sheets("Master").Range("A1:D" & Sheets("Master").Range("A" & Rows.Count).End(xlUp).Row)

I also now notice that your headings in the "Months" sheets are in a different order to what I am calling the "Master" sheet.
Can you not change the order of either to match as that is going to make it more difficult to code (and without using another method slower)?

 

[MARK858]

Yes I know I read over it too quickly... my bad

 

[MARK858]

If you must keep the same order then maybe the below (although I would if it was more data I would use the Dictionary or an array but seeing as it can only be 7 rows max per sheet it probably isn't worthwhile) then try the below.

Sub Filterit()
    Dim j As Long, myCrit As String
    Application.ScreenUpdating = False
    For j = 1 To 12
        myCrit = MonthName(j)
        With Sheets("Master").Range("A1:J" & Sheets("Master").Range("A" & Rows.Count).End(xlUp).Row)
            .AutoFilter Field:=1, Criteria1:=myCrit

            On Error Resume Next
            With .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)

                .Columns(2).Copy _
                        Sheets(myCrit).Range("A:A").SpecialCells(xlCellTypeBlanks).Cells(1)
                .Columns(3).Copy _
                        Sheets(myCrit).Range("D:D").SpecialCells(xlCellTypeBlanks).Cells(1)
                .Columns(4).Copy _
                        Sheets(myCrit).Range("B:B").SpecialCells(xlCellTypeBlanks).Cells(1)
                .Columns(5).Copy _
                        Sheets(myCrit).Range("C:C").SpecialCells(xlCellTypeBlanks).Cells(1)


                On Error GoTo 0
            End With
            .AutoFilter

        End With
    Next
    Application.ScreenUpdating = True

End Sub

 

[Rick Rothstein]

months(1) = "january"
months(2) = "february"
months(3) = "march"
months(4) = "april"
months(5) = "may"
months(6) = "june"
months(7) = "july"
months(8) = "august"
months(9) = "september"
months(10) = "october"
months(11) = "november"
months(12) = "december"

Just pointing out for those who may find this interesting... if you are willing to le the "months" variable be a Variant, this single line of code will create the above array of values (in proper case rather than lower case)...

months = [TRANSPOSE(IF(A1:A12=CHAR(1),"",TEXT(28*ROW(1:12),"mmmm")))]

 

[SQUIDD]

Nice job rick, i will try to remember this on for the future.

Dave

 

[Rick Rothstein]

Nice job rick, i will try to remember this on for the future.

Actually, remember this one instead (same concept, but slightly shorter)...

months = [TRANSPOSE(IF(ROW(1:12),TEXT(28*ROW(1:12),"mmmm")))]