Matt3502 -
Apr 28, 2016 at 10:21 AM - Latest reply: vcoolio1159
Posts Thursday July 24, 2014Registration dateModeratorStatus
March 28, 2018
Last seen
- May 27, 2016 at 12:16 AM
Hello,
I have a master data sheet that one column in the sheet that represents a sub set of the data. When I add information to the master sheet, I want the information to populate the sheet that represents the sub set. The master sheet contains 495 rows of info broken down into 15 columns. Rather than filter thru the data, It would be easier to just look at the other sheet will full data pertaining only to that smaller group.
Can this be done?
Thanks for your Help :)
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.
Thanks for taking a look at this!
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
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:-
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:-
Please for give my ignorance vcoolio, but where does the macro "hide" within the spread sheets?
I see from your msg time stamped 10:22 US EDT, that it looks like it is on an excel spread sheet page.
Once again many thanks.
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.
vcoolio1159
Posts Thursday July 24, 2014Registration dateModeratorStatus
March 28, 2018
Last seen
>
DonJuan6920062
Posts Thursday May 26, 2016Registration date
May 26, 2016
Last seen -May 26, 2016 at 10:21 PM
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.
vcoolio1159
Posts Thursday July 24, 2014Registration dateModeratorStatus
March 28, 2018
Last seen
>
DonJuan6920062
Posts Thursday May 26, 2016Registration date
May 26, 2016
Last seen -May 26, 2016 at 10:48 PM
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.
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.
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!
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:-
Vcoolio-
Once again you have come thru with flying colors! I really appreciate all your help! Your explanation of how things work in layman's terms has given me a much better understanding of Excel.
Many, many Thanks!
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 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.
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!!
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.
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.
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
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!).
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