Report

Automatically moving data [Solved]

Ask a question Matt3502 - Latest answer on May 27, 2016 12:16AM
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 :)
See more 
Helpful
+0
moins plus
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.
Matt3502- May 1, 2016 11:59AM
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
Reply
Add comment
Helpful
+0
moins plus
Hello Matt,

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.
Matt3502- May 2, 2016 10:42AM
YOU TOTALLY ROCK!!
Thanks so much for solving this issues for me. Way beyond my skill set.
Reply
Add comment
Helpful
+0
moins plus
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:-


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.
Matt3502- May 2, 2016 10:51AM
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.
Reply
DonJuan692006 2Posts Thursday May 26, 2016Registration date May 26, 2016 Last seen - May 26, 2016 10:53AM
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
Reply
vcoolio 785Posts Thursday July 24, 2014Registration date ModeratorStatus September 19, 2016 Last seen - May 26, 2016 10:21PM
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.
Reply
vcoolio 785Posts Thursday July 24, 2014Registration date ModeratorStatus September 19, 2016 Last seen DonJuan692006 - May 26, 2016 10:48PM
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.
Reply
Add comment
Helpful
+0
moins plus
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.
Matt3502- May 2, 2016 04:04PM
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!
Reply
Add comment
Helpful
+0
moins plus
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.
Matt3502- May 3, 2016 08:56AM
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!
Reply
Add comment
Helpful
+0
moins plus
Hello Matt,,

You're welcome. Glad that I could help.

Again, good luck with the volunteer project.

Cheerio,
vcoolio.
Matt3502- May 25, 2016 09:16AM
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
Reply
Add comment
Helpful
+0
moins plus
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.
Matt3502- May 25, 2016 11:44AM
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!!
Reply
Add comment
Helpful
+0
moins plus
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.
Matt3502- May 26, 2016 10:08AM
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.
Reply
Add comment
Helpful
+0
moins plus
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.
Matt3502- May 26, 2016 11:42PM
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
Reply
Add comment
Helpful
+0
moins plus
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.
Add comment

Members get more answers than anonymous users.

Being a member gives you detailed monitoring of your requests.

Being a member gives you additional options.

Not a member yet?

sign-up, it takes less than a minute and it's free!