Related:
- How to automatically update one excel worksheet from another sheet
- Excel master sheet sub sheets - Best answers
- How to auto populate data from multiple sheets to a master - Best answers
- Automatically transfer data from one sheet to another ✓ - Forum - Excel
- Automatically update master worksheet from other worksheets ✓ - Forum - Excel
- Transfer Data Between Excel Spreadsheets - How-To - Excel
- Copying data from one Excel sheet to another. ✓ - Forum - Excel
- How do you get a date to automatically update in excel? - How-To - Excel
14 replies
TrowaD
- Posts
- 2657
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- September 22, 2020
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:
To implement the code:
Tip: Have you considered drop-down lists for column H?
Let me know if you get stuck somewhere.
Best regards,
Trowa
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
TrowaD
- Posts
- 2657
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- September 22, 2020
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
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
- Posts
- 2657
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- September 22, 2020
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.
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.
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
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
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
- Posts
- 1
- Registration date
- Tuesday May 16, 2017
- Status
- Member
- Last seen
- May 16, 2017
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.
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
- Posts
- 2657
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- September 22, 2020
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
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
Soumyajyoti
- Posts
- 2
- Registration date
- Wednesday September 18, 2013
- Status
- Member
- Last seen
- September 18, 2013
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
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
- Posts
- 2657
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- September 22, 2020
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
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
mbearnstein37
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
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
- Posts
- 2657
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- September 22, 2020
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.
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
- Posts
- 4
- Registration date
- Tuesday July 14, 2015
- Status
- Member
- Last seen
- July 21, 2015
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.
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
- Posts
- 2657
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- September 22, 2020
Hi Kaushal,
Here is your code:
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
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
- Posts
- 4
- Registration date
- Tuesday July 14, 2015
- Status
- Member
- Last seen
- July 21, 2015
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
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
Acker
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?
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
- Posts
- 2657
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- September 22, 2020
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
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
- Posts
- 4
- Registration date
- Tuesday July 14, 2015
- Status
- Member
- Last seen
- July 21, 2015
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
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
- Posts
- 1
- Registration date
- Monday August 24, 2015
- Status
- Member
- Last seen
- August 24, 2015
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
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
- Posts
- 2657
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- September 22, 2020
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.
Best regards,
Trowa
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
- Posts
- 1
- Registration date
- Thursday March 2, 2017
- Status
- Member
- Last seen
- March 2, 2017
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
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
- Posts
- 2
- Registration date
- Wednesday June 14, 2017
- Status
- Member
- Last seen
- June 15, 2017
- Posts
- 2657
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- September 22, 2020
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
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
- Posts
- 2657
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- September 22, 2020
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
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
Bern
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.
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
- Posts
- 2657
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- September 22, 2020
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
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
- Posts
- 2657
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- September 22, 2020
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!
Thanks for your help!
TrowaD
- Posts
- 2657
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- September 22, 2020
Hi Bern,
Sorry to keep you waiting.
Give the following code a try:
Best regards,
Trowa
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
ns56
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)
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)
TrowaD
- Posts
- 2657
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- September 22, 2020
Hi ns56,
Try this:
Best regards,
Trowa
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
- Posts
- 4
- Registration date
- Tuesday September 5, 2017
- Status
- Member
- Last seen
- October 2, 2017
Hello TrowaD
Hope you are doing well. It will now be accessible to you.
https://accounts.google.com/ServiceLogin?service=wise&passive=1209600&continue=https://docs.google.com/spreadsheets/d/13oZBmvlre4DDvLXA343kjDST3ZLjwptyH9xcgoMrvS4/edit&followup=https://docs.google.com/spreadsheets/d/13oZBmvlre4DDvLXA343kjDST3ZLjwptyH9xcgoMrvS4/edit<mpl=sheets#gid=802247849
Thanks
Regards,
Talal Sarvana
Hope you are doing well. It will now be accessible to you.
https://accounts.google.com/ServiceLogin?service=wise&passive=1209600&continue=https://docs.google.com/spreadsheets/d/13oZBmvlre4DDvLXA343kjDST3ZLjwptyH9xcgoMrvS4/edit&followup=https://docs.google.com/spreadsheets/d/13oZBmvlre4DDvLXA343kjDST3ZLjwptyH9xcgoMrvS4/edit<mpl=sheets#gid=802247849
Thanks
Regards,
Talal Sarvana
TrowaD
- Posts
- 2657
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- September 22, 2020
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
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
- Posts
- 4
- Registration date
- Tuesday September 5, 2017
- Status
- Member
- Last seen
- October 2, 2017
- Posts
- 2657
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- September 22, 2020
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
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
- Posts
- 2657
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- September 22, 2020
- Posts
- 4
- Registration date
- Tuesday September 5, 2017
- Status
- Member
- Last seen
- October 2, 2017
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:
The following code does as described above:
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
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
- Posts
- 4
- Registration date
- Tuesday September 5, 2017
- Status
- Member
- Last seen
- October 2, 2017
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
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
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
- Posts
-
2657
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- September 22, 2020
440That 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
Problem solved.
I had to "Enable Content" Then it is working beautifully.
Sorry for the trouble.
Thanks
Davis