Related:
- Automatically moving data
- Automatically transfer data from one sheet to another in excel - How-To - Excel
- How To Auto Transfer Data from Master to Sub Sheets in Excel - How-To - Excel
- Automatically transfer data from one sheet to another ✓ - Forum - Excel
- Automatically transfer data from one sheet to another ✓ - Forum - Excel
- Macro for moving data to another worksheet ✓ - Forum - Programming
10 replies
vcoolio
- Posts
- 1278
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- February 16, 2021
Hello Matt,
Could you please upload a sample of your work book (be careful with any sensitive data) to a free file sharing site such as DropBox, ge.tt or SpeedyShare and then post the link to the file back here so that we can properly assess your query.
Cheerio,
vcoolio.
Could you please upload a sample of your work book (be careful with any sensitive data) to a free file sharing site such as DropBox, ge.tt or SpeedyShare and then post the link to the file back here so that we can properly assess your query.
Cheerio,
vcoolio.
vcoolio
- Posts
- 1278
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- February 16, 2021
Hello Matt,
The following code should do the job for you:-
The code finds the sector number in Column L and then transfers the relevant row of data to the individual sheet.
The code also refreshes the "Sector" sheets so that you do not have duplication and can keep all data in the Master sheet (or would you rather have the "used" data in the Master sheet cleared?).
Following is the link to my test work book (based on the one you supplied). Click on the transfer button to see the code at work. Give it a few seconds to execute as you have a large amount of data to deal with:-
https://www.dropbox.com/s/ni8nq4fhldn4qan/Matt3502%28Case%20statement%2C%20master%20sht%20to%20multi%20shts%29.xlsm?dl=0
Unhide any hidden sheets in your actual work book.
I have fiddled with your formatting a little in the "Sector" sheets.
I hope that this helps.
Cheerio,
vcoolio.
The following code should do the job for you:-
Sub TransferData()
Application.ScreenUpdating = False
Dim sh As Worksheet
Dim ws As Worksheet
Dim lrow As Long
Dim rng As Range
Set ws = Sheets("Master ROAD LIST")
lrow = ws.Cells(Rows.Count, 1).End(xlUp).Row
For Each sh In Worksheets
If sh.Name <> "Master ROAD LIST" And sh.Name <> "Cul-de Sac" And sh.Name <> "ss9" Then
sh.UsedRange.Offset(1).ClearContents
End If
Next
For Each rng In ws.Range("L3:L" & lrow)
Text = Mid(rng.Value, 1)
Select Case Text
Case Is = "1"
rng.EntireRow.Copy
Sheets("SECTOR 1").Cells(Rows.Count, 1).End(3)(2).PasteSpecial xlPasteValues
Case Is = "2"
rng.EntireRow.Copy
Sheets("SECTOR 2").Cells(Rows.Count, 1).End(3)(2).PasteSpecial xlPasteValues
Case Is = "3"
rng.EntireRow.Copy
Sheets("SECTOR 3").Cells(Rows.Count, 1).End(3)(2).PasteSpecial xlPasteValues
Case Is = "4"
rng.EntireRow.Copy
Sheets("SECTOR 4").Cells(Rows.Count, 1).End(3)(2).PasteSpecial xlPasteValues
Case Is = "5"
rng.EntireRow.Copy
Sheets("SECTOR 5").Cells(Rows.Count, 1).End(3)(2).PasteSpecial xlPasteValues
Case Is = "6"
rng.EntireRow.Copy
Sheets("SECTOR 6").Cells(Rows.Count, 1).End(3)(2).PasteSpecial xlPasteValues
End Select
Next
For Each sh In Worksheets
If sh.Name <> "Master ROAD LIST" Then
sh.Columns.AutoFit
End If
Next
MsgBox "Data Transfer complete!", vbExclamation
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
The code finds the sector number in Column L and then transfers the relevant row of data to the individual sheet.
The code also refreshes the "Sector" sheets so that you do not have duplication and can keep all data in the Master sheet (or would you rather have the "used" data in the Master sheet cleared?).
Following is the link to my test work book (based on the one you supplied). Click on the transfer button to see the code at work. Give it a few seconds to execute as you have a large amount of data to deal with:-
https://www.dropbox.com/s/ni8nq4fhldn4qan/Matt3502%28Case%20statement%2C%20master%20sht%20to%20multi%20shts%29.xlsm?dl=0
Unhide any hidden sheets in your actual work book.
I have fiddled with your formatting a little in the "Sector" sheets.
I hope that this helps.
Cheerio,
vcoolio.
vcoolio
- Posts
- 1278
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- February 16, 2021
Hello again Matt,
Sorry, I should have thought to use the autofilter seeing that you have a large amount of data to deal with. Hence the following code is much quicker:-
Following is the updated link to the test work book:-
https://www.dropbox.com/s/ni8nq4fhldn4qan/Matt3502%28Case%20statement%2C%20master%20sht%20to%20multi%20shts%29.xlsm?dl=0
Cheerio,
vcoolio.
Sorry, I should have thought to use the autofilter seeing that you have a large amount of data to deal with. Hence the following code is much quicker:-
Option Explicit
Sub MoveStuff()
Dim ar As Variant, i As Integer
ar = [{"SECTOR 1","SECTOR 2","SECTOR 3","SECTOR 4","SECTOR 5","SECTOR 6";1,2,3,4,5,6}]
Application.ScreenUpdating = False
For i = 1 To UBound(ar, 2)
Sheets(ar(1, i)).UsedRange.Offset(1).ClearContents
With Sheet1
.AutoFilterMode = False
With Range("L2", Range("L" & Rows.Count).End(xlUp))
.AutoFilter 1, ar(2, i)
.Offset(1).EntireRow.Copy
Sheets(ar(1, i)).Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial xlPasteValues
ActiveSheet.AutoFilterMode = False
Sheets(ar(1, i)).Columns.AutoFit
End With
End With
Next i
Application.ScreenUpdating = True
Application.CutCopyMode = False
MsgBox "All done!", vbExclamation
End Sub
Following is the updated link to the test work book:-
https://www.dropbox.com/s/ni8nq4fhldn4qan/Matt3502%28Case%20statement%2C%20master%20sht%20to%20multi%20shts%29.xlsm?dl=0
Cheerio,
vcoolio.
DonJuan692006
- Posts
- 2
- Registration date
- Thursday May 26, 2016
- Status
- Member
- Last seen
- May 26, 2016
vcoolio,
I'm trying to do basically the same thing as Matt, but I'm getting an error running the above macro. It's saying "Compile error: Variable not defined" and when the debugger comes up, it's highlighting the "Sub MoveStuff()" line.
Ryan
I'm trying to do basically the same thing as Matt, but I'm getting an error running the above macro. It's saying "Compile error: Variable not defined" and when the debugger comes up, it's highlighting the "Sub MoveStuff()" line.
Ryan
vcoolio
- Posts
- 1278
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- February 16, 2021
- Posts
- 2
- Registration date
- Thursday May 26, 2016
- Status
- Member
- Last seen
- May 26, 2016
Hello Ryan,
The sub routine name is high-lighted because Excel has discovered a variable problem and has completely stopped the code from working. When this happens, does any line (or part of a line) high-light in blue or does any line turn red? Have you added to the code?
Ryan, please start a new thread with the issue as this thread is quite long now and is going to become longer as we continue to try and sort out Matt's issue. So it could become quite messy trying to solve two different issues in one thread (plus HAL has taken over Matt's life!)
Upload a sample of your work book (with the code applied) to a free file sharing site such as DropBox, ge.tt or SpeedyShare and then post the link to your file back in your new thread. Be careful with any sensitive data.
Also, give us a brief on what you would like to do and the expected result.
See you over at the new thread,
Cheerio,
vcoolio.
The sub routine name is high-lighted because Excel has discovered a variable problem and has completely stopped the code from working. When this happens, does any line (or part of a line) high-light in blue or does any line turn red? Have you added to the code?
Ryan, please start a new thread with the issue as this thread is quite long now and is going to become longer as we continue to try and sort out Matt's issue. So it could become quite messy trying to solve two different issues in one thread (plus HAL has taken over Matt's life!)
Upload a sample of your work book (with the code applied) to a free file sharing site such as DropBox, ge.tt or SpeedyShare and then post the link to your file back in your new thread. Be careful with any sensitive data.
Also, give us a brief on what you would like to do and the expected result.
See you over at the new thread,
Cheerio,
vcoolio.
vcoolio
- Posts
- 1278
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- February 16, 2021
- Posts
- 2
- Registration date
- Thursday May 26, 2016
- Status
- Member
- Last seen
- May 26, 2016
Hello Ryan,
I just saw your new thread. I'll have a look at it tonight for you but please still upload a sample of your work book to the thread.
Cheerio,
vcoolio.
I just saw your new thread. I'll have a look at it tonight for you but please still upload a sample of your work book to the thread.
Cheerio,
vcoolio.
vcoolio
- Posts
- 1278
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- February 16, 2021
Hello Matt,
The macros reside in standard modules in the VB Editor. To find them, press Alt + F11. This will take you to the VBE. Over to the left, you'll see the Project Explorer. You'll see "Modules" at the bottom of the sheet list. Select either to see them in the big white code field.
Good luck and thanks for the kind remarks. I'm glad that I was able to help.
Cheerio,
vcoolio.
The macros reside in standard modules in the VB Editor. To find them, press Alt + F11. This will take you to the VBE. Over to the left, you'll see the Project Explorer. You'll see "Modules" at the bottom of the sheet list. Select either to see them in the big white code field.
Good luck and thanks for the kind remarks. I'm glad that I was able to help.
Cheerio,
vcoolio.
Matt3502
One last question, again asked out of ignorance Vcoolio. If I it the transfer button, all the data populates into the respective sheet. If I hit the Move Data ( auto filter, quicker). It seems that on the Sector sheets the rows are off.The Master road list has a row "K" while the sector sheets do not, so the data does not "line up"?
Is there a way to resolve the difference?
I really thank you for all of your help. The time and energy yo have spent on this makes a volunteer project I am doing so much better!
Is there a way to resolve the difference?
I really thank you for all of your help. The time and energy yo have spent on this makes a volunteer project I am doing so much better!
vcoolio
- Posts
- 1278
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- February 16, 2021
Hello Matt,
Its a little quirk of Autofilter. In your work book, Column K is hidden in the Master sheet. Autofilter is very precise and will only deal with what is visible whereas a Case Statement (the first code above) will iterate through everything which is why it takes a lot longer to execute.
Unhide Column K and you'll see that all works as it should. Save your work book so that Column K remains unhidden.
Following is the link to the test work book again with Column K unhidden:-
https://www.dropbox.com/s/ni8nq4fhldn4qan/Matt3502%28Case%20statement%2C%20master%20sht%20to%20multi%20shts%29.xlsm?dl=0
Cheerio and good luck with your volunteer project.
vcoolio.
Its a little quirk of Autofilter. In your work book, Column K is hidden in the Master sheet. Autofilter is very precise and will only deal with what is visible whereas a Case Statement (the first code above) will iterate through everything which is why it takes a lot longer to execute.
Unhide Column K and you'll see that all works as it should. Save your work book so that Column K remains unhidden.
Following is the link to the test work book again with Column K unhidden:-
https://www.dropbox.com/s/ni8nq4fhldn4qan/Matt3502%28Case%20statement%2C%20master%20sht%20to%20multi%20shts%29.xlsm?dl=0
Cheerio and good luck with your volunteer project.
vcoolio.
vcoolio
- Posts
- 1278
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- February 16, 2021
Hello Matt,,
You're welcome. Glad that I could help.
Again, good luck with the volunteer project.
Cheerio,
vcoolio.
You're welcome. Glad that I could help.
Again, good luck with the volunteer project.
Cheerio,
vcoolio.
Matt3502
Good Afternoon vcoolio,
I hope all is well. Once again I am extremely grateful for the help you gave me with my volunteer project. An issue has come up, and I would be very thankful to you if I can get a little guidance. As you know, our original spread sheet was written in Excel 2010. Now it appears that some of the computers who will use it will have Office 2016. Excel 2016 does not "play well" with the macros you created. The "transfer" macro works most of the time but the "move stuff" macro does not really do anything. Is there something that I can do to make everything work together? I hate to impose on you, but any help you can give me would be great, especially since your work has help our spreadsheet become the standard for what we do as voluteers in our town.
Cheers and Thanks,
Matt
I hope all is well. Once again I am extremely grateful for the help you gave me with my volunteer project. An issue has come up, and I would be very thankful to you if I can get a little guidance. As you know, our original spread sheet was written in Excel 2010. Now it appears that some of the computers who will use it will have Office 2016. Excel 2016 does not "play well" with the macros you created. The "transfer" macro works most of the time but the "move stuff" macro does not really do anything. Is there something that I can do to make everything work together? I hate to impose on you, but any help you can give me would be great, especially since your work has help our spreadsheet become the standard for what we do as voluteers in our town.
Cheers and Thanks,
Matt
vcoolio
- Posts
- 1278
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- February 16, 2021
Hello Matt,
I suppose it had to happen sooner or later (i.e. the move to Excel 2016)!
From your last post, I assume that you are not receiving any error messages also.
This is a real head scratcher type scenario as I have heard many similar stories to yours. Its usually a compatibility issue and the same thing seems to happen when people use Excel for Macs. Its a hard one to sort out especially when a network is being used (and I assume that you are).
I'll have a look at it for you tomorrow but in the meantime, in the main spread sheet on each computer, click on the File button at the top left of the sheet, select "Options" and then select "Trust Center" in the window that appears. In the next window that appears, click on the Trust Center settings button and then select "Trusted Documents" in the following window.
Here, make sure that the "Allow documents on a network to be trusted" box is ticked (do this on each machine).
Another method (although tedious) is to actually re-write your preferred macro line by line EXACTLY as it is now into the VBE of one machine that has 2016 installed, save it and then try it again. Once saved, go to the VBE (Visual Basic Editor) in the Developer tab (or just press Alt + F11 to take you there) select Tools--->References. A VBA References window will open. Here, make sure that the box beside Microsoft Office 16.0 Object Library is ticked and, if it is there, tick the box beside Microsoft Office 14.0 Object Library.
(14.0 is for Excel 2010, 15 is for 2013, 16 is for 2016).
Before closing the VBA References window, click on the save icon in the tool bar over to the left (little blue box shaped thing).
I'm also hoping that fellow Moderator ac3mark will pay a visit here as this type of thing is "right up his alley" as a Master Programmer and his input could enlighten us all.
Let us know how it works out.
Cheerio,
vcoolio.
I suppose it had to happen sooner or later (i.e. the move to Excel 2016)!
From your last post, I assume that you are not receiving any error messages also.
This is a real head scratcher type scenario as I have heard many similar stories to yours. Its usually a compatibility issue and the same thing seems to happen when people use Excel for Macs. Its a hard one to sort out especially when a network is being used (and I assume that you are).
I'll have a look at it for you tomorrow but in the meantime, in the main spread sheet on each computer, click on the File button at the top left of the sheet, select "Options" and then select "Trust Center" in the window that appears. In the next window that appears, click on the Trust Center settings button and then select "Trusted Documents" in the following window.
Here, make sure that the "Allow documents on a network to be trusted" box is ticked (do this on each machine).
Another method (although tedious) is to actually re-write your preferred macro line by line EXACTLY as it is now into the VBE of one machine that has 2016 installed, save it and then try it again. Once saved, go to the VBE (Visual Basic Editor) in the Developer tab (or just press Alt + F11 to take you there) select Tools--->References. A VBA References window will open. Here, make sure that the box beside Microsoft Office 16.0 Object Library is ticked and, if it is there, tick the box beside Microsoft Office 14.0 Object Library.
(14.0 is for Excel 2010, 15 is for 2013, 16 is for 2016).
Before closing the VBA References window, click on the save icon in the tool bar over to the left (little blue box shaped thing).
I'm also hoping that fellow Moderator ac3mark will pay a visit here as this type of thing is "right up his alley" as a Master Programmer and his input could enlighten us all.
Let us know how it works out.
Cheerio,
vcoolio.
Matt3502
Vcoolio,
Thanks for getting back to me so quickly. I am of the generation who thinks that lurking inside every computer is HAL from 2001, and it does what it wants when it wants. Right now this program is being used on computers that are NOT on a net, but will be at some point. I will take a look at the one I have here and check the trust certificates as you suggest. I am looking forward to hearing what you have to say tomorrow. THANKS AGAIN FOR ALL YOUR HELP!!
Thanks for getting back to me so quickly. I am of the generation who thinks that lurking inside every computer is HAL from 2001, and it does what it wants when it wants. Right now this program is being used on computers that are NOT on a net, but will be at some point. I will take a look at the one I have here and check the trust certificates as you suggest. I am looking forward to hearing what you have to say tomorrow. THANKS AGAIN FOR ALL YOUR HELP!!
vcoolio
- Posts
- 1278
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- February 16, 2021
Hello Matt,
Perhaps HAL could sort this one out with his(its?) calm and evil mannerism!
I'm still none the wiser but I have read a few articles and my suggestion of actually re-writing the code in the 2016 version (letter for letter, word for word etc.) may just save the day according to one article I read.
Still check the trust settings regardless.
ac3mark is having a look at it also. I'm just waiting for any insights that he may have.
Cheerio,
vcoolio.
Perhaps HAL could sort this one out with his(its?) calm and evil mannerism!
I'm still none the wiser but I have read a few articles and my suggestion of actually re-writing the code in the 2016 version (letter for letter, word for word etc.) may just save the day according to one article I read.
Still check the trust settings regardless.
ac3mark is having a look at it also. I'm just waiting for any insights that he may have.
Cheerio,
vcoolio.
Matt3502
At the rate I am going, HAL may shut off my oxygen supply (aka the IT guy). I did follow your advice and check the trust settings, and I went as far as telling it to accept the macros,(despite the warning about code). It did not affect the performance. One issue I am faced with the fact that other users of this spread sheet are on 2010. From what I was told, HAL (aka the IT guy) has no plans to upgrade the other folks.
vcoolio
- Posts
- 1278
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- February 16, 2021
Hello Matt,
Does this mean that all will be OK now?
Does your HAL have any insights into the annoying little issue or is he just a "set up and install" type of HAL?
Cheerio,
vcoolio.
Does this mean that all will be OK now?
Does your HAL have any insights into the annoying little issue or is he just a "set up and install" type of HAL?
Cheerio,
vcoolio.
Matt3502
I am so low on the foodchain to HAL that he will get to this when 2020 is out. For me he is an install and flee IT.
Solving issues like this remind me of working on my car: 2004 Land Rover. Sometimes you just have to throw parts at it until the control module decides to play nice. Thanks agin for all your efforts
Solving issues like this remind me of working on my car: 2004 Land Rover. Sometimes you just have to throw parts at it until the control module decides to play nice. Thanks agin for all your efforts
vcoolio
- Posts
- 1278
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- February 16, 2021
Hi Matt,
"For me he is an install and flee IT. "
Ha. Sounds like the people that installed a new system where the missus works. She works at a law firm, these IT people installed the new system, it fell apart and they fled. Being a law firm, for them to flee was not a good idea!
Still waiting on advices from ac3mark so I'll be back (as one famous Terminator once said!).
Cheerio,
vcoolio.
"For me he is an install and flee IT. "
Ha. Sounds like the people that installed a new system where the missus works. She works at a law firm, these IT people installed the new system, it fell apart and they fled. Being a law firm, for them to flee was not a good idea!
Still waiting on advices from ac3mark so I'll be back (as one famous Terminator once said!).
Cheerio,
vcoolio.
Subscribe To Our Newsletter!
DON'T MISS
- Computer Boots to Black Screen
- How to Recover Your Yahoo Account
- How to Disabled Write Protection on a USB
- How to Get Free Diamonds in Free Fire
- How to Install a VPN
- How to Create a Gmail Account
- How to Get More Followers on Instagram
- How to Fix a Blurry Snapchat Video
- Facebook Code Not Received
- How to Recover a Disabled Facebook Account
- How to Upload High Quality Photos to Instagram
Here is the link: http://speedy.sh/cCyST/DAT-drop-box-04-27-2016.xlsx
Basically what I need to happen is when I enter data on the master road list sheet, column "L" corresponds to one of the other sheets "sector 1,2,3" etc. I would like the data I enter from the master road sheet to populate on the corresponding sector sheet automatically.
I really appreciate you taking the time to look at my issue!
Many Thanks!
Matt 3502