Automatically moving data Solved/Closed

Question

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 :)



Configuration: Windows / Chrome 49.0.2623.112

vcoolio · Answer

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.

vcoolio · Answer

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.

vcoolio · Answer

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.

vcoolio · Answer

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.

vcoolio · Answer

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.

vcoolio · Answer

Hello Matt,,

You're welcome. Glad that I could help.

Again, good luck with the volunteer project.

Cheerio,
vcoolio.

vcoolio · Answer

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.

vcoolio · Answer

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.

vcoolio · Answer

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.

vcoolio · Answer

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.

Automatically moving data

10 responses

Similar discussions

Subscribe To Our Newsletter!