Autopopulating multiple sheets from one main sheet [Solved/Closed]

Mikey - Feb 20, 2013 at 05:39 PM - Latest reply: TrowaD 2286 Posts Sunday September 12, 2010Registration dateModeratorStatus November 7, 2017 Last seen
- Oct 5, 2017 at 11:35 AM
Hello,



I'm making an accounting workbook for an organization I am the treasurer for, and I've run into a problem.
On the first sheet "Account" I input all of the transactions that the club has, on a table that goes from C6-H6, and on down for every entry. One of the columns in the table is labeled "Sector" and refers to which part of the club the expense or revenue came from. There are 7 sectors of the club and they each have a sheet in this workbook.
What I would like to do, is have each individual sector's sheet automatically populate with data when it's inputted onto the main "Account" sheet, and have it based on the Sector column in the table. So basically, if the sector says "General", I would like for the entire row to be transferred to the sheet "General". And so on for each individual sector.
That way I don't have to copy and paste the hundreds of transactions to individual pages manually.
Any tips or suggestions?

I greatly appreciate any and all help!
Thanks

Michael
See more 

45 replies

TrowaD 2286 Posts Sunday September 12, 2010Registration dateContributorStatus November 7, 2017 Last seen - Feb 21, 2013 at 10:19 AM
+4
Helpful
3
Hi Michael,

For the code below I created 2 sector's "General" and "1ste Group".
Adjust and add to the code to suit your needs.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("H:H")) Is Nothing Then Exit Sub

If Target.Value = "General" Then
    Range(Range("C" & Target.Row), Range("H" & Target.Row)).Copy _
    Sheets("General").Range("C" & Rows.Count).End(xlUp).Offset(1, 0)
    
ElseIf Target.Value = "1ste Group" Then
    Range(Range("C" & Target.Row), Range("H" & Target.Row)).Copy _
    Sheets("1ste Group").Range("C" & Rows.Count).End(xlUp).Offset(1, 0)
    
End If

End Sub


To implement the code:
Right-click on the "Account" tab and select View code.
Paste the code in the big white field.

Tip: Have you considered drop down lists for column H?

Let me know if you get stuck somewhere.

Best regards,
Trowa
Was this answer helpful?  
Hi TrowaD,

Thanks for your code.

I used the code segment successfully,. But, on reopening the file after saving with macro enabled, the code does not seem to run!

What should I do?

Davis
TrowaD 2286 Posts Sunday September 12, 2010Registration dateModeratorStatus November 7, 2017 Last seen - Apr 26, 2016 at 11:38 AM
Hi Davis,

That is strange, saving your file shouldn't alter the way the code works in any way.

I wouldn't mind taking a look at your file to see what is going on.

Use a free file sharing site like www.speedyshare.com or ge.tt to upload your file and then post back the download link. Always be careful with sensitive information.

I'll get back to you next week, though.

Best regards,
Trowa
Hi Trowa

Problem solved.
I had to "Enable Content" Then it is working beautifully.

Sorry for the trouble.

Thanks
Davis
+1
Helpful
Thank you so much, it worked perfectly! My day just got 10x better thanks to you!
TrowaD 2286 Posts Sunday September 12, 2010Registration dateModeratorStatus November 7, 2017 Last seen - Jul 21, 2015 at 11:53 AM
+1
Helpful
Hi Kaushlendra,

The most efficient way would be to use an extra column to make each row unique. The row can then be found on the sub sheets. So if you want to update your change, you can confirm the entry in column A. This will activate the code which will look for the row using the value from the extra column. If the row is found, then it will be replaced. Otherwise the row will be added.

Note that the extra column can be hidden, placed out of sight (like column ZZ or something) or the text can be made white to not disturb the format of the sheet.

The choice is yours!

Best regards,
Trowa
TrowaD 2286 Posts Sunday September 12, 2010Registration dateModeratorStatus November 7, 2017 Last seen - Mar 6, 2017 at 12:07 PM
+1
Helpful
2
Hi Suzanne,

I'm still here :).

Maybe you implemented the code in the wrong place.
Right-click the "all" sheets tab, select View Code and paste the code in the big white field.

The other thing that I notice, is that you have Column B used for the sheet names, while the code looks only at column G for any changes.
If Intersect(Target, Columns("G:G")) Is Nothing Then Exit Sub

Change "G:G" to "B:B" to make the code look at column B.


You also say you want to copy columns A-F, but in the code it will copy A-G.
Range(Range("A" & Target.Row), Range("G" & Target.Row)).Copy

Change the "G" into "F".


The destination for WellsFargo is different then the rest. It will not be pasted in column A but C. If this is supposed to be A then
Sheets("WellsFargo").Range("C" & Rows.Count).End(xlUp).Offset(1, 0)

change the "C" into an "A".
And in that case all the code blocks are the same, which means you could consider the code posted in the 14th post in this thread. Which will remove the need for any additional code blocks.



Does this solve your query?

Best regards,
Trowa
jwchestnut2 1 Posts Tuesday May 16, 2017Registration date May 16, 2017 Last seen - May 16, 2017 at 01:07 PM
Trowa,

I have a similar issue I'm working on that I could use some help on.

I've got a spreadsheet that I'm using as a punch-list to track open issues on a project. These individual punches are tracked by 3 categories, Mechanical, E&I, HSE, and then are listed as Open or Closed, plus a few others. I'm trying to populate sheets labeled "Mechanical", "E&I", and "HSE" but only with "open" items. I also need it to update every time an item changes from open to close, meaning it's removed from the sheet.

I used the code Suzanne submitted above with the changes you suggested and it's sort of working. I'm occasionally getting a type mismatch error, have no idea how to prevent duplicate entries, no idea how to only pull open items, and then of course remove items that change from open to closed.
TrowaD 2286 Posts Sunday September 12, 2010Registration dateModeratorStatus November 7, 2017 Last seen - May 18, 2017 at 11:22 AM
Hi jwchestnut2,

Ok, for that request I need the following info:

1) Which column is used for the labels "Mechanical", "E&I" and "HSE"?
2) Which column is used for 'Open' and 'Closed'?
3) And finally which column shows unique values? This is used to find an entry which has already been copied, to prevent duplicate entries and to remove entries where the status is changed to 'Closed'.

Best regards,
Trowa
Respond to TrowaD
Soumyajyoti 2 Posts Wednesday September 18, 2013Registration date September 18, 2013 Last seen - Sep 18, 2013 at 02:21 AM
0
Helpful
Trowa, Michael -

This post really helped me at some extend. Thank you! :)

I'm still getting some bug to implement the same on my requirements. Please help me if possible ..

I'm having a 'Master' sheet. Column A of 'Master' is Type. I have drop down list for Type. Type can be - Email, Page, Abend and Ticket. Now depending on the type entered in Master I want the data to be auto populated at respective child sheet of same name. From Master, column A to column K should be copied to column A to column K at respective sheets.

Using below query I did able to accomplish that. But I'm seeing two bugs -

1) When I select value from drop down at col A (this is the first column in the sheet) in "Master", a row is being populated at respective child sheet. But col B to col K in child sheet is remaining blank even after I enter the values at col B to K . I can't move col A at last as per requirement. I want col B to col K in child sheet also to be populated once I enter value for the same in Master.

2) What if someone just copy and paste the values (from his local copy) in Master shit (in compatible format)? Even though the Type is correct, it is not being populated in respective child sheet.

Please help me to rectify the same. Below is the query I used -

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("A:A")) Is Nothing Then Exit Sub

If Target.Value = "Page" Then
Range(Range("A" & Target.Row), Range("K" & Target.Row)).Copy _
Sheets("Page").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

End If

If Target.Value = "Abend" Then
Range(Range("A" & Target.Row), Range("K" & Target.Row)).Copy _
Sheets("Abend").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

End If

If Target.Value = "Email" Then
Range(Range("A" & Target.Row), Range("K" & Target.Row)).Copy _
Sheets("Email").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

End If

If Target.Value = "Ticket" Then
Range(Range("A" & Target.Row), Range("K" & Target.Row)).Copy _
Sheets("Ticket").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

End If

End Sub
TrowaD 2286 Posts Sunday September 12, 2010Registration dateContributorStatus November 7, 2017 Last seen - Sep 19, 2013 at 11:10 AM
0
Helpful
1
Hi Soumyajyoti,

1) Not sure what goes wrong here since I can't recreate the error. When I fill columns B to K with data and then use a dropdown list in column A to select Page, columns A to K are copied to the first available row on the Page sheet. Just as you want, right?

2) Target is referred to as the range that is changed. So you basically can't change more then one cell or you have to adjust the code to make excel only look at the column A part of the pasted range.
Change "Target.value ="
Into "Cells(Target.Row, "A").Value"

Best regards,
Trowa
Hi Trowa,

I am trying to do the exact same thing that you helped Michael and Soumyajyoti with. My table extends from column A to AN, and the values that I would like to determine the population of new tabs lie in column C. I copied your code and pasted it into the tab that contain all of the input data like you said in your original comment to Michael, and I assume I adjusted the column and value name references accordingly, but it is not working. My biggest issue is that even when I save the macro, I cannot view it in my macros list when I press ALT+F8. This has been a problem anytime I have tried to use VBA, so any insight is appreciated. I am running Excel 2010 on a PC.

Thanks,
Matt
TrowaD 2286 Posts Sunday September 12, 2010Registration dateModeratorStatus November 7, 2017 Last seen - Jul 9, 2015 at 11:08 AM
0
Helpful
1
Hi Matt,

The code provided will run automatically whenever a change is made to column H in the Account sheet.

The code starts with Private Sub Worksheet, thus is placed in a sheet and can not be manually activated. Only codes placed in a Module can be found in the macro list when pressing Alt+F8.

Hopefully this makes some sense.

Let me know if this solves your query or you would like more assistance.

Consider the option to post the code you are trying to get to work.

Best regards,
Trowa
Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
kaushal1234 4 Posts Tuesday July 14, 2015Registration date July 21, 2015 Last seen - Jul 16, 2015 at 08:32 AM
Hi TrowaD
I am new to macro programming.
I have a similar requirement and used the same code as suggested by you. I need to populate the multiple sheets from master sheet based on 1st column. Each sheet would get updated as whenever the new entry is done in the master sheet. The header is same for all the sheets.
When I use the below code , it doesnot run

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("H:H")) Is Nothing Then Exit Sub

If Target.Value = "General" Then
Range(Range("C" & Target.Row), Range("H" & Target.Row)).Copy _
Sheets("General").Range("C" & Rows.Count).End(xlUp).Offset(1, 0)

ElseIf Target.Value = "1ste Group" Then
Range(Range("C" & Target.Row), Range("H" & Target.Row)).Copy _
Sheets("1ste Group").Range("C" & Rows.Count).End(xlUp).Offset(1, 0)

End If

End Sub

You may download the file from below link:
https://www.dropbox.com/s/f7a9q46k1nkcms9/master%20file.xlsx?dl=0

Kindly let me know what could be the possible solution for it. Thanks is advance.
TrowaD 2286 Posts Sunday September 12, 2010Registration dateModeratorStatus November 7, 2017 Last seen - Jul 16, 2015 at 11:04 AM
0
Helpful
2
Hi Kaushal,

Here is your code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("A:A")) Is Nothing Then Exit Sub

Target.EntireRow.Copy Sheets(Target.Value).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

End Sub


To implement the code:
Right-click on the "mastercopy" tab and select View code.
Paste the code in the big white field.

After doing that, make a change in column A in the "mastercopy" sheet and the entire row will be copied to the first available row of the sheet mentioned in column A. So make sure the sheet name in column A is the last value you enter for that row.

Best regards,
Trowa
kaushal1234 4 Posts Tuesday July 14, 2015Registration date July 21, 2015 Last seen - Jul 17, 2015 at 01:21 AM
Hi TrowaD

Your code is of great help and it works fine. Now my team has asked for one more functionality to add to the existing code. Once the row in inserted in the sheet, we want that whenever any row is UPDATED in the master sheet, the same row should get updated in the individual sheet as well. Is that possible?

At present a new entry is sent to the individual sheet for every update of the existing rows in the master sheet. Please note that only those rows would be updated which have 'O' under header Open/Executed( Col O)

Can we have something like update/refresh option in the sheet for which the update can be done.

Please suggest a solution to it. Thanks for help in advance. You have been of great h
elp to me.

Regards,
Kaushlendra
TrowaD,

I am attempting to use the code you supplied. I am trying to set it up to copy what I have entered in an entire row to another tab based on what I type in a specific column.

If I type the number 525 in column C I want whatever row I am on to then copy or be moved to the sheet that is labeled 525.

How must a change the code you gave to make this happen?
TrowaD 2286 Posts Sunday September 12, 2010Registration dateModeratorStatus November 7, 2017 Last seen - Jul 20, 2015 at 11:27 AM
0
Helpful
1
Hi Kaushlendra,

When a change is made to a row, the originally matching rows aren't the same anymore. So how do we know which row needs to be replaced?

If your Mastercopy sheet has all the correct and latest data, then we could run a code when selecting one of the sub sheets.
For example: when selecting sheet A, all data on that sheet except for the headers will be deleted. Then all the rows from Mastercopy sheet with an A in column A will be copied to sheet A.

Is that something that could work for you and your team?

Also the Open/Executed header is in column N in the file you send me, did something change?

Best regards,
Trowa
kaushal1234 4 Posts Tuesday July 14, 2015Registration date July 21, 2015 Last seen - Jul 21, 2015 at 04:52 AM
Hi TrowaD
Thanks for the reply.
I think you have suggested a right approach. We can actually delete all the rows from the sheet A and then copy all the rows to the sheet A from mastersheet. But will it work for all the sheets based on column A? It would be great if you can you help me with the code.

IS there any other approach also?

Just to let you know, open/executed header is 'N'. I had mistakenly written it wrong. Sorry for the inconvenience.

Thanks for your all help and guidance.

Regards
Kaushlendra
cmjay 1 Posts Monday August 24, 2015Registration date August 24, 2015 Last seen - Aug 24, 2015 at 01:08 PM
0
Helpful
Hi Trowa,

I'm trying to do something similar in a workbook (and eventually a google sheet). I have salesperson data in a mastersheet that then needs to be directed to sub-sheets for each individual salesperson.

If the salesperson listed in the master sheet in column B is "Salesperson 1", I would like the entire row to transfer over to the sub-sheet "Salesperson 1".

Please let me know if you have any questions; I really appreciate the help!

Best,

Chris
TrowaD 2286 Posts Sunday September 12, 2010Registration dateModeratorStatus November 7, 2017 Last seen - Aug 25, 2015 at 11:41 AM
0
Helpful
Hi cmjay,

Try the code below. Keep in mind that once implemented (right-click master sheet tab > View code > paste code in big white field) the code will run automatically once an entry is made in column B. So make the salesperson the last entry you make for that row.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("B:B")) Is Nothing Then Exit Sub
target.entirerow.Copy Sheets(Target.Value).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End Sub


Best regards,
Trowa
sleigh911 1 Posts Thursday March 2, 2017Registration date March 2, 2017 Last seen - Mar 2, 2017 at 07:44 PM
0
Helpful
13
Hello Trowa,
I hope you're still around answering questions! I tried to use your code above, and I have no idea why it's not working... :(
Here is what I typed into the (activated) VB box:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("G:G")) Is Nothing Then Exit Sub

If Target.Value = "Amex" Then
Range(Range("A" & Target.Row), Range("G" & Target.Row)).Copy _
Sheets("Amex").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

ElseIf Target.Value = "Visa" Then
Range(Range("A" & Target.Row), Range("G" & Target.Row)).Copy _
Sheets("Visa").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

ElseIf Target.Value = "Mastercard" Then
Range(Range("A" & Target.Row), Range("G" & Target.Row)).Copy _
Sheets("Mastercard").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

ElseIf Target.Value = "WellsFargo" Then
Range(Range("A" & Target.Row), Range("G" & Target.Row)).Copy _
Sheets("WellsFargo").Range("C" & Rows.Count).End(xlUp).Offset(1, 0)

End If

My sheet called "all" contains:
Column A: Date
B: Pymt (which would be a check#, Amex, Visa, etc)
C: Vendor
D: Notes
E: Job
F: Category (Materials, commissions, etc)

I want a verbatim copy of the sheet "all", columns A-F on the next line on the respective sheets Amex, Visa, (or whatever). I've made a database in the past, but I don't want to spend a year trying to remember to not delete or insert a row.

What did I do wrong on my code?

Thanks for any help you can provide!
Suzanne
aglo25 2 Posts Wednesday June 14, 2017Registration date June 15, 2017 Last seen > TrowaD 2286 Posts Sunday September 12, 2010Registration dateModeratorStatus November 7, 2017 Last seen - Jun 15, 2017 at 01:42 PM
Trowa,

I couldnt get it to work.

This is my sample data columns

Date Carrier Trailer# Loaded/Empty Seal# IMPO/EXPO Notes

I want to based it off the Carrier. Im trying to use this code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("B:B")) Is Nothing Then Exit Sub
target.entirerow.Copy Sheets(Target.Value).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End Sub

Since the carrier Values are going to be more than 10 different carriers

I tried uploading the file but it wouldnt let me

Thanks,
Olga
TrowaD 2286 Posts Sunday September 12, 2010Registration dateModeratorStatus November 7, 2017 Last seen - Jun 19, 2017 at 10:48 AM
Hi Olga,

Your posts got deleted because 'http://ge.tt' is blacklisted. I didn't realize it was. Try using www.speedyshare.com or another free filesharing site.

If the sheet names in column B match the sheet names in your workbook, then I really need to take a look at your file to see what is going on.

Best regards,
Trowa
I'm trying to create a workbook similar to what the others are working on. I want to copy each row (column A-F) into new worksheets based on column C (which has a dropdown menu), and I'd like it to update when new lines are added, or lines are changed. Column A (hidden) has unique entries.

The dropdown menu has 7 options (AS, BB, JS, AS BB, AS JS, BB JS, AS BB JS). Is it possible to copy any items that have AS alone or AS in combination with other initials into one sheet, and the same for the other initials? So, there would only be 3 destinations (AS or BB or JS). If the initials are in combination, I'd like the row to be moved to multiple destinations.
TrowaD 2286 Posts Sunday September 12, 2010Registration dateModeratorStatus November 7, 2017 Last seen - Aug 1, 2017 at 12:04 PM
Hi Bern,

So the sub sheets have the same header as the main sheet?

Which cell in the row do you enter last (this doesn't have to be F)? Keep in mind that when you make a change and want to update the other sheet(s), you will have to re-confirm that cells contents to make the code do it's work.

Best regards,
Trowa
Bern > TrowaD 2286 Posts Sunday September 12, 2010Registration dateModeratorStatus November 7, 2017 Last seen - Aug 1, 2017 at 01:20 PM
Yes, they all have the same column headers. I will fill out column D last. That is the column that has the initials in it, as a dropdown menu. Sorry, in the previous post I had C listed as the column with initials, but I forgot to include the hidden column.

Thanks for your help!
Respond to sleigh911
TrowaD 2286 Posts Sunday September 12, 2010Registration dateModeratorStatus November 7, 2017 Last seen - Aug 15, 2017 at 11:18 AM
0
Helpful
1
Hi Bern,

Sorry to keep you waiting.

Give the following code a try:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("D")) Is Nothing Then Exit Sub

Dim mFind As Range

Target.EntireRow.Copy

If Target.Value Like "*AS*" Then
    Set mFind = Sheets("AS").Columns("A").Find(Target.Offset(0, -3))
    If mFind Is Nothing Then
        Sheets("AS").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
    Else
        mFind.PasteSpecial
    End If
End If

If Target.Value Like "*BB*" Then
    Set mFind = Sheets("BB").Columns("A").Find(Target.Offset(0, -3))
    If mFind Is Nothing Then
        Sheets("BB").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
    Else
        mFind.PasteSpecial
    End If
End If

If Target.Value Like "*JS*" Then
    Set mFind = Sheets("JS").Columns("A").Find(Target.Offset(0, -3))
    If mFind Is Nothing Then
        Sheets("JS").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
    Else
        mFind.PasteSpecial
    End If
End If

Application.CutCopyMode = False
End Sub


Best regards,
Trowa
Hello,

I'm also very new to macro, so I don't know what I'm supposed to be adjusting!

My main sheet is titled "Project Timeline" and my other sheets are "Marketing", "Training" etc. the column in "Project Timeline" i've used is G, not H, so I adjusted that. But I'm not sure what else I'm supposed to adjust!


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("H:H")) Is Nothing Then Exit Sub

If Target.Value = "General" Then
Range(Range("C" & Target.Row), Range("H" & Target.Row)).Copy _
Sheets("General").Range("C" & Rows.Count).End(xlUp).Offset(1, 0)
Respond to TrowaD
TrowaD 2286 Posts Sunday September 12, 2010Registration dateModeratorStatus November 7, 2017 Last seen - Aug 31, 2017 at 11:07 AM
0
Helpful
7
Hi ns56,

Try this:
Private Sub Worksheet_Change(ByVal Target As Range) 
If Intersect(Target, Columns("G:G")) Is Nothing Then Exit Sub 

target.entirerow.Copy _ 
Sheets(target.value).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

Best regards,
Trowa
talalsarvana 4 Posts Tuesday September 5, 2017Registration date October 2, 2017 Last seen - Sep 26, 2017 at 03:58 AM
Hello TrowaD

Hope you are doing well. It will now be accessible to you.

https://docs.google.com/spreadsheets/d/13oZBmvlre4DDvLXA343kjDST3ZLjwptyH9xcgoMrvS4/edit#gid=802247849

Thanks

Regards,
Talal Sarvana
TrowaD 2286 Posts Sunday September 12, 2010Registration dateModeratorStatus November 7, 2017 Last seen - Sep 28, 2017 at 11:14 AM
Hi Talal,

I'm fine, thanks.

But as requested in my previous post, please be so kind to use an alternate file sharing method. Also make sure your query is clearly explained either in your file or in your next post.

Best regards,
Trowa
talalsarvana 4 Posts Tuesday September 5, 2017Registration date October 2, 2017 Last seen > TrowaD 2286 Posts Sunday September 12, 2010Registration dateModeratorStatus November 7, 2017 Last seen - Oct 4, 2017 at 08:51 AM
Hi TrowaD

I am linking the 4shared file please have a look at it. hope it works this time.

https://www.4shared.com/office/3_h508Y0ca/Details.html

my data entry sheet is production I want it to be transferred to respective machine sheet as I enter the machine number in column E.

Thanks

Regards
Talal Sarvana
TrowaD 2286 Posts Sunday September 12, 2010Registration dateModeratorStatus November 7, 2017 Last seen > talalsarvana 4 Posts Tuesday September 5, 2017Registration date October 2, 2017 Last seen - Oct 5, 2017 at 11:35 AM
Hi Talal,

Sorry for the inconvenience, but I like a file sharing site without any form of login method.

Could you give the following site a chance:
http://www.speedshare.eu/

But I might not need the file if the following is correct:
  • Column E is used to enter Machine numbers.
  • These Machine numbers correspond to already created sheets.
  • Once a Machine number is confirmed, you want the entire row copied to the first available row of the respective sheet.


The following code does as described above:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim mSheet As String

If Intersect(Target, Columns("E")) Is Nothing Then Exit Sub

mSheet = Target.Value

Target.EntireRow.Copy _
Sheets(mSheet).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End Sub


To implement the code, right-click your main sheets tab and select view code. Paste the code in the big white field.

Hopefully this points you in the right direction, as I wouldn't want you to have to wait any longer then necessary. Thanks for your patience.

Best regards,
Trowa
talalsarvana 4 Posts Tuesday September 5, 2017Registration date October 2, 2017 Last seen - Oct 2, 2017 at 06:07 AM
Hi TrowaD,

Thanks for the reply.

I am sharing the link of 4Shared which is as follows:

https://www.4shared.com/office/3_h508Y0ca/Details.html

I want to transfer the data from Production Sheet to the respected machine Sheet as i enter the machine number in Column E. If there is any other way of doing it please let me know that also.

Thanks alot.

Regards
Talal Sarvana
Respond to TrowaD