Copy all the cells automatically to another sheet

shaahmed

New Member
Joined
Nov 26, 2016
Messages
6
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Hi

When do you want the copy event to take place, are you simply filling out say a master sheet then you want to run a macro to filter out the data to the relevent sheets.

?

Dave
 
Upvote 0
also, where should the data go in the month sheets?

dave
 
Upvote 0
in my example below
i have sheet 5 as where i have written the data
then i have a sheet called january

Code:
Sheets("sheet5").Activate
    ActiveSheet.Range("A:D").AutoFilter Field:=1, Criteria1:="january"
    ActiveSheet.AutoFilter.Range.Copy
    Sheets(Range("a1").Text).Activate
    ActiveSheet.Paste
    Sheets("sheet5").Activate
    ActiveSheet.Range("A:D").AutoFilter

this will filter january copy and paste to sheet january.

this all assumes you do not have header rows.

if this is sort of on the right lines, we can make a loop to cycle the 12 months.

dave
 
Upvote 0
sorry, try this one

Code:
Sub months_to_sheets()

Sheets("sheet5").Activate
    ActiveSheet.Range("A:D").AutoFilter Field:=1, Criteria1:="january"
    ActiveSheet.AutoFilter.Range.Copy
    Sheets(Range("a1").Text).Activate
    ActiveSheet.Paste
    Sheets("sheet5").Activate
    ActiveSheet.Range("A:D").AutoFilter
End Sub
 
Upvote 0
this will loop
Code:
Sub months_to_sheets()
Dim sht As Worksheet
Set sht = Sheets("sheet5")
Dim months(1 To 3) As String
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"
For i = 1 To 12

sht.Activate
    ActiveSheet.Range("A:D").AutoFilter Field:=1, Criteria1:=months(i)
    ActiveSheet.AutoFilter.Range.Copy
    Sheets(months(i)).Activate
    ActiveSheet.Paste
    sht.Activate
    ActiveSheet.Range("A:D").AutoFilter
Next i
End Sub
 
Upvote 0
Try this:
Run this script from your Master sheet:
Code:
Sub Test()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim Lastrowa As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
On Error GoTo M
    For i = 1 To Lastrow
        Lastrowa = Sheets(Cells(i, 1).Value).Cells(Rows.Count, "A").End(xlUp).Row + 1
        Rows(i).Copy Destination:=Sheets(Cells(i, 1).Value).Rows(Lastrowa)
    Next
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "No Such Sheet Exist"
Application.ScreenUpdating = True
End Sub
 
Upvote 0
sorry I am a binger in excel please help me

this is my first page .. please explain were I put the above code...
MonthID NoNameDateReceipt NoCAIJ E.IncomeExpenseRemarks
NovemberKHFA0001Abdul Vasih 06/11/2015236/1358410.0002.000 2015/16
NovemberKHFA0002Adil S16/12/20162xx/125462.0001.000 2016/17
NovemberKHFA0003Afsal 18/12/20162sxd1245/*15.00010.000 2018/19
DecemberKHFA0004Aseem 15/12/20161245/s52.00020.000 2020/2001
DecemberKHFA0005Baber03/12/2016rt12532.0001.000 20124
DecemberKHFA0006Kabeer 02/01/2016sdr1235.0002.000 321321
DecemberKHFA0007Khamarudeen11/04/2016tre2542.0001.000 zscsfcx
<colgroup><col width="103" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3766;"> <col width="101" style="width: 76pt; mso-width-source: userset; mso-width-alt: 3693;"> <col width="172" style="width: 129pt; mso-width-source: userset; mso-width-alt: 6290;"> <col width="101" style="width: 76pt; mso-width-source: userset; mso-width-alt: 3693;"> <col width="103" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3766;"> <col width="103" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3766;" span="4"> <col width="133" style="width: 100pt; mso-width-source: userset; mso-width-alt: 4864;"> <tbody> </tbody>

this is my month page

ID NoDATERECEIPT NO.DONOR'S NAMECAIJIncome ExpenseTOTALYearRemarks
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:
<colgroup><col width="101" style="width: 76pt; mso-width-source: userset; mso-width-alt: 3693;"> <col width="90" style="width: 68pt; mso-width-source: userset; mso-width-alt: 3291;"> <col width="97" style="width: 73pt; mso-width-source: userset; mso-width-alt: 3547;"> <col width="242" style="width: 182pt; mso-width-source: userset; mso-width-alt: 8850;"> <col width="96" style="width: 72pt; mso-width-source: userset; mso-width-alt: 3510;" span="4"> <col width="93" style="width: 70pt; mso-width-source: userset; mso-width-alt: 3401;"> <col width="99" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3620;"> <col width="85" style="width: 64pt; mso-width-source: userset; mso-width-alt: 3108;"> <tbody> </tbody>
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top