vba to copy value and paste in worksheet with the same name as value

husker32

New Member
Joined
Feb 25, 2014
Messages
7
Hello all,

I have a workbook that has 121 different worksheets. I would like some help creating a macro that would search the first sheet titled "ledger" in column c. Column C has sudsiary names. Each one of my subsiary names already has a worksheet with the same name. So the macro would need to copy the entire row for all of the claims with the same subsidiary. For example there is a subsiary name reg and there are hundreds of rows of reg claims I want to copy all of those claims and put them in the sheet title reg. Currenlty I have a macro that uses the auto filter to achieve this but it is slow and long. I was hoping that there was a loop that could achieve this goal. Thanks in advance.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Hello all,
I am new to vba and macros, but my problem is similar to this one. I tried the solutions above but did not work.
So here is my scenario: I have a sheet called "unfiltered" where a lots of trucks' fuel consumption is listed in each row. The first column ("A") has the license plate numbers. And all my sheets are named according to the license plates.
What I want to do is to copy the rows to the given sheets based on the license plate numbers. The first row in all of the sheets are labels. So for example: A2 = MDN-229, and I want this row to be copied to the sheet name "MDN-229".

I have 140 trucks and a few thousand rows in the "unfiltered" sheet.

Thanks for any help given
 
Upvote 0
...........my scenario: I have a sheet called "unfiltered" where a lots of trucks' fuel consumption is listed in each row. The first column ("A") has the license plate numbers. And all my sheets are named according to the license plates.
What I want to do is to copy the rows to the given sheets based on the license plate numbers. The first row in all of the sheets are labels. So for example: A2 = MDN-229, and I want this row to be copied to the sheet name "MDN-229".......


Hi,
. Have you checked out these similar MrExcel Threads.
. At very first glance your application seems quite similar to the first few.
.
. In any case it would greatly improve your chances of help if you can give us a “Picture” of what you want BUT NOT a jpg or similar….important is that we can copy it easily into a worksheet. See for example my notes in Post #21 and Post #42 in the first link :-
http://www.mrexcel.com/forum/excel-...-into-multiple-worksheets-based-column-3.html

Is there a way to automatically Filter, Copy and Paste to Tabs based on value in ColumnA
Copy entire row to another worksheet if column = specific value
Copying a Row Based on Coloumn Contents
VBA code for Grouping columns in excel based on certain criteria













Hello all,
I am new to vba and macros, but my problem is similar to this one. I tried the solutions above but did not work.........


. If you have tried, where did you start getting problems?

. Alan
 
Upvote 0
Hi,
. Have you checked out these similar MrExcel Threads.
. At very first glance your application seems quite similar to the first few.
.
. In any case it would greatly improve your chances of help if you can give us a “Picture” of what you want BUT NOT a jpg or similar….important is that we can copy it easily into a worksheet. See for example my notes in Post #21 and Post #42 in the first link :-
http://www.mrexcel.com/forum/excel-...-into-multiple-worksheets-based-column-3.html
Thanks for your quick reply.
I checked the first link you provided and tried to use the solution in post #4. The code did create different sheets according to data in column A, but did not copy anything. All of my sheets remained empty.
 
Upvote 0
……first link you provided and tried to use the solution in post #4. The code did create different sheets according to data in column A, but did not copy anything…..



O.k.
That early code was a bit before my time so I am not too familiar with it. From your brief description I thought some of the later Codes I have recently been looking at might have been easier to adapt….
. At this distance it is very hard to guess what might be going wrong.
. I know that, just as one example, getting no copy can occur with my codes when Headers are missing. But that is a peculiarity of Auto-Filter which the code you are looking at does not use..

. Anyways I think it will be difficult to help you further without a better picture of your Spreadsheet and codes you have tried.
. If you are not familiar with the MrExcel Tools yet (As I mentioned in Post #21 for example in that first Thread. – And in my signature below), then at least drop off a File, again as I indicated in Post #21. )

. If all else fails then PM (Private Message) me and I can give you my e-mail address and you can attach a file per email (To PM you must be logged in, then click on my name above my photo and the rest should be obvious).

. Important…. Give A clear spreadsheet example (shortened, and if necessary made up data if anything is sensitive ) of your "unfiltered" sheet, and some additional sheets , “hand done” as it were as to show exactly how you want the output based on that example data from the "unfiltered" sheet. (Put the codes in that you have tried as well)

. Otherwise we are working a bit blind and can give only wild guesses as to what is going wrong

. Alan
 
Upvote 0
Thanks for your quick reply.
I checked the first link you provided and tried to use the solution in post #4. The code did create different sheets according to data in column A, but did not copy anything. All of my sheets remained empty.

Hi polyn4,
. Thanks for the PM and file. I will take a look when I can and get back and contact here
( and supply MrExcel HTML Screen shots so everyone can benefit and follow wot is going on, and possibly then in addition someone else can also provide alternative solutions _ That is always useful for everyone.)

Alan
 
Upvote 0
……: I have a sheet called "unfiltered" where a lots of trucks' fuel consumption is listed in each row. The first column ("A") has the license plate numbers. And all my sheets are named according to the license plates.
What I want to do is to copy the rows to the given sheets based on the license plate numbers. The first row in all of the sheets are labels. So for example: A2 = MDN-229, and I want this row to be copied to the sheet name "MDN-229"…
… …………………………………………
Basically what I want to do is, that after I put datas in the “unfiltered” sheet and run the macro, all of the rows should be moved in the specific sheets according to the license plate numbers (column A). So after I ran the macro the “unfiltered” sheet should be empty and the datas are moved to the correct sheet.
And later on when I have new datas, I just put them in the unfiltered sheet again, and run the macro.


…..

………………………………………………


Hi Polyn4

. Thanks for supplying a File with reduced data. That helps! (For the next time about a third as much as you sent would be adequate.)
. I was not able to make much progress on the macro you supplied with the file you sent. I am a beginner myself, learning from answering these threads, and without any explain ‘Green comments I cannot follow it. In fact it does not appear to be one from this Thread.
. Can you clear that one up?. If you can contact the author He/She may be willing to make a useful contribution to This Thread.
.
. Here is the code you sent:

Code:
[color=blue]Sub[/color] columntosheets()
 
[color=blue]Const[/color] sname [color=blue]As[/color] [color=blue]String[/color] = "unfiltered" [color=lightgreen]'change to whatever starting sheet[/color]
[color=blue]Const[/color] s [color=blue]As[/color] [color=blue]String[/color] = "A" [color=lightgreen]'change to whatever criterion column[/color]
[color=blue]Dim[/color] d [color=blue]As[/color] [color=blue]Object[/color], a, cc&
[color=blue]Dim[/color] p&, i&, rws&, cls&
[color=blue]Set[/color] d = CreateObject("scripting.dictionary")
[color=blue]With[/color] Sheets(sname)
    rws = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
    cls = .Cells.Find("*", , , , xlByColumns, xlPrevious).Column
    cc = .Columns(s).Column
[color=blue]End[/color] [color=blue]With[/color]
[color=blue]For[/color] [color=blue]Each[/color] sh [color=blue]In[/color] Worksheets
    d(sh.Name) = 1
[color=blue]Next[/color] sh
 
Application.ScreenUpdating = [color=blue]False[/color]
[color=blue]With[/color] Sheets.Add(after:=Sheets(sname))
Sheets(sname).Cells(1).Resize(rws, cls).Copy .Cells(1)
.Cells(1).Resize(rws, cls).Sort .Cells(cc), 2, Header:=xlYes
a = .Cells(cc).Resize(rws + 1, 1)
p = 2
[color=blue]For[/color] i = 2 [color=blue]To[/color] rws + 1
    [color=blue]If[/color] a(i, 1) <> a(p, 1) [color=blue]Then[/color]
        [color=blue]If[/color] d(a(p, 1)) <> 1 [color=blue]Then[/color]
            Sheets.Add.Name = a(p, 1)
            .Cells(1).Resize(, cls).Copy Cells(1)
            .Cells(p, 1).Resize(i - p, cls).Copy Cells(2, 1)
        [color=blue]End[/color] [color=blue]If[/color]
        p = i
    [color=blue]End[/color] [color=blue]If[/color]
[color=blue]Next[/color] i
Application.DisplayAlerts = [color=blue]False[/color]
    .Delete
Application.DisplayAlerts = [color=blue]True[/color]
Application.ScreenUpdating = [color=blue]True[/color]
[color=blue]End[/color] [color=blue]With[/color]
Sheets(sname).Activate
 
[color=blue]End[/color] [color=blue]Sub[/color]

………………………………………
.
. Anyways, I have another initial solution for you..
. My starting point was my codes in Post #40 at MrExcel Thread:
http://www.mrexcel.com/forum/excel-...-into-multiple-worksheets-based-column-4.html

. The final code I include at the end of this Thread. For now I leave my ‘Green Comments on it as it helps me remember wot I did. I can remove them and simplify it a bit for you if you wish.
. I post now just a few sample screen shots, so that others can follow what is going on.
.
. Initially Part of your “unfiltered” sheet looks like this

<b></b><table width="10" cellpadding="1px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">Jármu Név</td><td style="text-align: center;;">Telematika csoport</td><td style="text-align: center;;">Jármucsoport</td><td style="text-align: center;;">Jármuvezeto</td><td style="text-align: center;;">Jármuvezeto csoport</td><td style="text-align: center;;">Állapot</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">MIN-423</td><td style="text-align: center;;">MB</td><td style="text-align: center;;">DUV-HU-DUV-Csehbanya EN6 Actros SZML </td><td style="text-align: center;;">name 1</td><td style="text-align: center;;">DUV HU Logisztikai DUV HU Bernard  </td><td style="text-align: center;;">Szünet</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">MDN-229</td><td style="text-align: center;;">MB</td><td style="text-align: center;;">DUV-HU-DUV-Csehbanya </td><td style="text-align: center;;">name 2</td><td style="text-align: center;;">DUV HU Logisztikai </td><td style="text-align: center;;">Szünet</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">MDN-229</td><td style="text-align: center;;">MB</td><td style="text-align: center;;">DUV-HU-DUV-Csehbanya </td><td style="text-align: center;;">name 3</td><td style="text-align: center;;">DUV HU Logisztikai </td><td style="text-align: center;;">Vezetés</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">MMG-561</td><td style="text-align: center;;">MB</td><td style="text-align: center;;">DUV-HU-DUV-Csehbanya EN6 Actros SZML </td><td style="text-align: center;;">name 4</td><td style="text-align: center;;">DUV HU Logisztikai </td><td style="text-align: center;;">Szünet</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">MDN-229</td><td style="text-align: center;;">MB</td><td style="text-align: center;;">DUV-HU-DUV-Csehbanya </td><td style="text-align: center;;">name 5</td><td style="text-align: center;;">DUV HU Logisztikai </td><td style="text-align: center;;">Szünet</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">MDN-229</td><td style="text-align: center;;">MB</td><td style="text-align: center;;">DUV-HU-DUV-Csehbanya </td><td style="text-align: center;;">name 6</td><td style="text-align: center;;">DUV HU Logisztikai </td><td style="text-align: center;;">Vezetés</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">MDN-229</td><td style="text-align: center;;">MB</td><td style="text-align: center;;">DUV-HU-DUV-Csehbanya </td><td style="text-align: center;;">name 7</td><td style="text-align: center;;">DUV HU Logisztikai </td><td style="text-align: center;;">Szünet</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">MDN-229</td><td style="text-align: center;;">MB</td><td style="text-align: center;;">DUV-HU-DUV-Csehbanya </td><td style="text-align: center;;">name 8</td><td style="text-align: center;;">DUV HU Logisztikai </td><td style="text-align: center;;">Vezetés</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">MDN-229</td><td style="text-align: center;;">MB</td><td style="text-align: center;;">DUV-HU-DUV-Csehbanya </td><td style="text-align: center;;">name 9</td><td style="text-align: center;;">DUV HU Logisztikai </td><td style="text-align: center;;">Szünet</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">MAE-745</td><td style="text-align: center;;">MB</td><td style="text-align: center;;">DUV-HU-DUV-Csehbanya EN6 Actros SZML </td><td style="text-align: center;;">name 10</td><td style="text-align: center;;">DUV HU Logisztikai </td><td style="text-align: center;;">Vezetés</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">MAE-745</td><td style="text-align: center;;">MB</td><td style="text-align: center;;">DUV-HU-DUV-Csehbanya EN6 Actros SZML </td><td style="text-align: center;;">name 11</td><td style="text-align: center;;">DUV HU Logisztikai </td><td style="text-align: center;;">Szünet</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">MDN-229</td><td style="text-align: center;;">MB</td><td style="text-align: center;;">DUV-HU-DUV-Csehbanya </td><td style="text-align: center;;">name 12</td><td style="text-align: center;;">DUV HU Logisztikai </td><td style="text-align: center;;">Vezetés</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;;">MDN-229</td><td style="text-align: center;;">MB</td><td style="text-align: center;;">DUV-HU-DUV-Csehbanya </td><td style="text-align: center;;">name 13</td><td style="text-align: center;;">DUV HU Logisztikai </td><td style="text-align: center;;">Szünet</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;;">MLP-162</td><td style="text-align: center;;">MB</td><td style="text-align: center;;">DUV-HU-DUV-Csehbanya EN6 Actros SZML </td><td style="text-align: center;;">name 14</td><td style="text-align: center;;">DUV HU Logisztikai </td><td style="text-align: center;;">Vezetés</td></tr></tbody></table><p style="width:3em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">unfiltered</p><br /><br />

.
.
After running the macro I am providing below, Part a typical new sheet produced would look like this:
.

<b></b><table width="10" cellpadding="1px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Jármu Név</td><td style=";">Telematika csoport</td><td style=";">Jármucsoport</td><td style=";">Jármuvezeto</td><td style=";">Jármuvezeto csoport</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">MDN-229</td><td style=";">MB</td><td style=";">DUV-HU-DUV-Csehbanya </td><td style=";">name 2</td><td style=";">DUV HU Logisztikai </td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">MDN-229</td><td style=";">MB</td><td style=";">DUV-HU-DUV-Csehbanya </td><td style=";">name 3</td><td style=";">DUV HU Logisztikai </td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">MDN-229</td><td style=";">MB</td><td style=";">DUV-HU-DUV-Csehbanya </td><td style=";">name 5</td><td style=";">DUV HU Logisztikai </td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">MDN-229</td><td style=";">MB</td><td style=";">DUV-HU-DUV-Csehbanya </td><td style=";">name 6</td><td style=";">DUV HU Logisztikai </td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">MDN-229</td><td style=";">MB</td><td style=";">DUV-HU-DUV-Csehbanya </td><td style=";">name 7</td><td style=";">DUV HU Logisztikai </td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">MDN-229</td><td style=";">MB</td><td style=";">DUV-HU-DUV-Csehbanya </td><td style=";">name 8</td><td style=";">DUV HU Logisztikai </td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">MDN-229</td><td style=";">MB</td><td style=";">DUV-HU-DUV-Csehbanya </td><td style=";">name 9</td><td style=";">DUV HU Logisztikai </td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">MDN-229</td><td style=";">MB</td><td style=";">DUV-HU-DUV-Csehbanya </td><td style=";">name 12</td><td style=";">DUV HU Logisztikai </td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">MDN-229</td><td style=";">MB</td><td style=";">DUV-HU-DUV-Csehbanya </td><td style=";">name 13</td><td style=";">DUV HU Logisztikai </td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:2,1em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">MDN-229</p><br /><br />



. I suggest you play around with the code, try to understand it a bit and then get back to me here.
. The code may not do exactly what you want but it should be a good starting point.
. You, me or together we can make any modifications you require.
. I have tested it in XL2007 and it appears to work OK.

Good Luck!
Alan

Code: (With ‘Comments initially)

. Brief notes For the Code.
. 1 ) This version will initially delete any sheets other than the unfiltered sheet or any other sheet you wish to keep..
. 2 ) For now it does not delete or clear the unfiltered sheet.

……….So after I ran the macro the “unfiltered” sheet should be empty and the datas are moved to the correct sheet.
And later on when I have new datas, I just put them in the unfiltered sheet again, and run the macro.
…..
…… I suggest an alternative here for your application would be simply to add new data at the end of the data already in the “unfiltered” sheet and run the macro. The end results, I believe will be the same as what you wish.



Code:
[color=blue]Option[/color] [color=blue]Explicit[/color]
 
[color=blue]Sub[/color] PolyColumnToSheet_AlanFeb2015MethodAutoFilterVisibleCellsCopyCriteriaUniqueArray()
 
[color=lightgreen]'Application.ScreenUpdating = False 'Not necerssary but speeds things up a bit, by turning screen updating off. Good to edit out for Debuging Purposes.[/color]
[color=blue]On[/color] [color=blue]Error[/color] [color=blue]GoTo[/color] TheEnd [color=lightgreen]'If anything goes wrong go to the End instead of crashing. Useful to Edit out for Debuging[/color]
[color=blue]Dim[/color] wks1 [color=blue]As[/color] Worksheet: [color=blue]Set[/color] wks1 = ThisWorkbook.Worksheets("unfiltered") [color=lightgreen]'set sheet name - Give abbreviation for "unfiltered" sheet in ThisWorkbook all Objects, Properties and Methods of Object Worksheet obtainable to view in the intellisense given after typing . Dot[/color]
 
        [color=lightgreen]'1) Optional Start Bit to Delete Sheets / Tabs------------[/color]
        Application.DisplayAlerts = [color=blue]False[/color] [color=lightgreen]'Prevents being asked everytime if you really want to delete the Workbook[/color]
        [color=blue]Dim[/color] ws [color=blue]As[/color] Worksheet [color=lightgreen]'Tempory worksheet name. ws now has Methods and Properties of Worksheets obtained with typing . dot after it![/color]
        [color=blue]For[/color] [color=blue]Each[/color] ws [color=blue]In[/color] ActiveWorkbook.Worksheets [color=lightgreen]'We could alternatively use something like ThisWorkbook, Workbooks("Itsname.xlxm")[/color]
            [color=blue]If[/color] ws.Name <> "ASheetToKeep" And ws.Name <> wks1.Name [color=blue]Then[/color]  [color=lightgreen]'Check that Worksheet name is not that of any that you want (Name property here returns name without .xlsm bit on end)[/color]
            ws.Delete
            [color=blue]Else[/color] [color=lightgreen]'Presumably then the worksheet name is That of the first sheet or any you wish to keep[/color]
            [color=lightgreen]' do nothing (Don't delete it!)[/color]
            [color=blue]End[/color] [color=blue]If[/color]
        [color=blue]Next[/color] ws
        Application.DisplayAlerts = [color=blue]True[/color] [color=lightgreen]'Turn it back on[/color]
        [color=lightgreen]'---End Bit to delete any Sheets / Tabs--------------------[/color]
 
[color=lightgreen]'Some variables used in various places[/color]
[color=blue]Dim[/color] vLkUpc [color=blue]As[/color] Long: [color=blue]Let[/color] vLkUpc = 1 [color=lightgreen]'set column number 'Column where search criteria for filtering is. '( Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.--upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster.[/color]
[color=blue]Dim[/color] rws [color=blue]As[/color] [color=blue]Long[/color] [color=lightgreen]'Bound variable Row count used in looping[/color]
[color=blue]Dim[/color] lr [color=blue]As[/color] Long: [color=blue]Let[/color] lr = wks1.Cells.Find(What:="*", After:=wks1.Cells(1, 1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row [color=lightgreen]'Get last Row with entry anywhere for Sheet1. Method: You start at first cell then go backwards (which effectively starts at end of sheet), sercching for anything ( = * ) by rows, then get the row number. This allows for different excel versions with different available Row numbers) Just a different method here for fun- finds last row in sheet rather than row for last entry in particular cell[/color]
[color=blue]Dim[/color] lshtc [color=blue]As[/color] Long: [color=blue]Let[/color] lshtc = wks1.Columns.Count [color=lightgreen]'Number of Columns in sheet...### used as column number for tempory unique column...###[/color]
    [color=lightgreen]'Let lshtc = 21 'This is useful for debugging so that you can see the tempory column of unique license plate numbers[/color]
[color=blue]Dim[/color] lc [color=blue]As[/color] Long: [color=blue]Let[/color] lc = wks1.Cells(1, lshtc).End(xlToLeft).Column [color=lightgreen]'Last column with entry in heading in unfiltered sheet. Found by starting at last cell in row 1, then going backwards (ToLeft) until something is found, with .End returning a range from which the column property can be used to get the column number[/color]
[color=lightgreen]'--------------------------------------[/color]
 
    [color=lightgreen]'2) make an Array for Unique Search values, using a Tempory column[/color]
    [color=blue]Let[/color] wks1.Cells(1, lshtc) = "Unique" [color=lightgreen]'...###The last Column inn the sheet is used. (This has an advantage of not interfering with our Method for getting lc). Here just for fun we give the array, that is to say the tempory column, a heading[/color]
        [color=blue]For[/color] rws = 2 [color=blue]To[/color] lr [color=blue]Step[/color] 1 [color=lightgreen]'Going down all rows  from just after heading in First sheet[/color]
        [color=blue]On[/color] [color=blue]Error[/color] [color=blue]Resume[/color] [color=blue]Next[/color] [color=lightgreen]'Necersary to ensure the looping goes on if the match cannot be determined, as below we have a look Up Array with empty cells[/color]
            [color=blue]If[/color] wks1.Cells(rws, vLkUpc) <> "" And Application.WorksheetFunction.Match(wks1.Cells(rws, vLkUpc), wks1.Columns(lshtc), 0) = 0 [color=blue]Then[/color] [color=lightgreen]'provided something is there, we check to see if that value is already in our vLook Up Array by looking to see for a match. If it is not there then.....[/color]
            wks1.Cells(wks1.Rows.Count, lshtc).End(xlUp).Offset(1) = wks1.Cells(rws, vLkUpc) [color=lightgreen]'.....Put  it there[/color]
            [color=blue]Else[/color] [color=lightgreen]'Else do nothing[/color]
            [color=blue]End[/color] [color=blue]If[/color]
        [color=blue]Next[/color] rws
    [color=blue]Dim[/color] myarr() [color=blue]As[/color] [color=blue]Variant[/color] [color=lightgreen]'Array for Unique search criteria. Important to get this Dimensioning right. Variant must be used as below  initially an object is seen...>>  http://www.excelforum.com/excel-new-users-basics/1058406-range-dimensioning-range-and-value-referencing-and-referring-to-arrays.html[/color]
    myarr() = Application.WorksheetFunction.Transpose(wks1.Columns(lshtc).SpecialCells(xlCellTypeConstants, xlTextValues).Value) [color=lightgreen]'just a complicated but nice one-liner way of getting just the values and no empty cells in the Array. XlcellTypeConstants just gives constants, the second argument is the type. Here Strings are there as the heading made sure of that - here excel guessed based on that due to the heading string "Unique".. This could be an untypical case where that second argument could be left out. Transpose is just to get the Array as A Row of Columns which we need rather than a Column of rows as is in the tempory Column.[/color]
    wks1.Columns(lshtc).Delete [color=lightgreen]'Delete the tempory Column (Delete is usually better than Clear.. >>  http://www.mrexcel.com/forum/excel-questions/787428-clear-delete-shift-%3Dxlup-let-y-%3D-y-%96-1-usedrange-rows-count-anomale.html[/color]
    [color=lightgreen]'---End of making an Array----------------------------------------[/color]
 
    [color=lightgreen]'3 ) Make a New worksheet with the name of the Unique search criteria, and in the same loop.....--->>-[/color]
        [color=blue]For[/color] rws = 2 [color=blue]To[/color] [color=blue]UBound[/color](myarr) [color=lightgreen]'For each unique value in the Array[/color]
        wks1.Range(wks1.Cells(1, 1), wks1.Cells(lr, lc)).AutoFilter Field:=vLkUpc, Criteria1:="" & myarr(rws) & "" [color=lightgreen]'This blends out everything except  where rows meet our search citeria[/color]
            [color=blue]If[/color] [color=blue]Not[/color] Evaluate("=ISREF('" & myarr(rws) & "'!A1)") Then [color=lightgreen]'Check to see if the sheet is there by seeing if the reference to cell A1 in that sheet doesn#t exist. If it is true that it does not exist, then[/color]
            Sheets.Add(After:=Worksheets(Worksheets.Count)).Name = "" & myarr(rws) & "" [color=lightgreen]'Make it as that after the last sheet[/color]
            [color=blue]Else[/color]
            Sheets("" & myarr(rws) & "").Move After:=Worksheets(Worksheets.Count) [color=lightgreen]'Otherwise If the sheet is there it could be anywhere so we put it after last sheet[/color]
            [color=blue]End[/color] [color=blue]If[/color]
   
        [color=lightgreen]'.......->>---...Copy Entire row that is visible (Not blended out) to the current sheet in loop[/color]
        wks1.Range("A" & 1 & ":A" & lr & "").SpecialCells(xlCellTypeVisible).EntireRow.Copy [color=lightgreen]'Copy just wot is visible after filtering[/color]
        Worksheets(myarr(rws)).Range("A1").PasteSpecial Paste:=xlPasteFormulas [color=lightgreen]'Being very Explicit here with an extra line enabling us to Paste Special with arguments to make sure the correct version from Clipboard is copied[/color]
        Sheets("" & myarr(rws) & "").Columns.AutoFit [color=lightgreen]'Just tidy up a bit[/color]
            [color=lightgreen]'wks1.AutoFilterMode = False 'Normally done at end of code to make all unfiltered sheet visible. But Putting here helps with debugging[/color]
        [color=blue]Next[/color] rws
    [color=lightgreen]'----End making (if necerssary) new sheet and copying filtered rows to it[/color]
 
wks1.Activate [color=lightgreen]'Activate that sheet 1 just to see it[/color]
 
TheEnd: [color=lightgreen]'We come here on erroring rather than crashing. Anything that should be done before ending the macro should be done here, to make sure it will always be dine ecen if the code crashes![/color]
wks1.AutoFilterMode = [color=blue]False[/color] [color=lightgreen]'re - Blend in everything in sheet 1[/color]
Application.ScreenUpdating = [color=blue]True[/color] [color=lightgreen]'Screen need to be turned back on or your screen will be "dead"!! Important to turn screen on here, incase anything goes wrong.[/color]
Application.CutCopyMode = [color=blue]False[/color] [color=lightgreen]'Stops screen selection flicke after Pasting[/color]
[color=blue]End[/color] [color=blue]Sub[/color] [color=lightgreen]'PolyColumnToSheet_AlanFeb2015MethodAutoFilterVisibleCellsCopyCriteriaUniqueArray()[/color]
[color=lightgreen]'[/color]
'
'I have a sheet called "unfiltered" where a lots of trucks' fuel consumption is listed in each row. The first column ("A") has the license plate numbers. And all my sheets are named according to the license plates.
[color=lightgreen]'What I want to do is to copy the rows to the given sheets based on the license plate numbers. The first row in all of the sheets are labels. So for example: A2 = MDN-229, and I want this row to be copied to the sheet name "MDN-229".[/color]
[color=lightgreen]'I have 140 trucks and a few thousand rows in the "unfiltered" sheet.[/color]
[color=lightgreen]'[/color]
'Basically what I want to do is, that after I put datas in the “unfiltered” sheet and run the macro,  all of the rows should be moved in the specific sheets according to the license plate numbers (column A). So after I ran the macro the “unfiltered” sheet should be empty and the datas are moved to the correct sheet.
[color=lightgreen]'And later on when I have new datas, I just put them in the unfiltered sheet again, and run the macro.[/color]
 
Upvote 0
…………
. I was not able to make much progress on the macro you supplied with the file you sent. I am a beginner myself, learning from answering these threads, and without any explain ‘Green comments I cannot follow it. In fact it does not appear to be one from this Thread.
. Can you clear that one up?. If you can contact the author He/She may be willing to make a useful contribution to This Thread.......


QUICK EDIT:

Hi Polyn,
.. I see now where you got that code from....I was getting all these similar Threads mixed up ...Post # 4
http://www.mrexcel.com/forum/excel-...a-into-multiple-worksheets-based-column.html?

... so the Author is mirabeau .. but he is "banned"!! so you cannot contact him!!
Alan
 
Upvote 0
………………………………………………
Code: (With ‘Comments initially)

. Brief notes For the Code.
. 1 ) This version will initially delete any sheets other than the unfiltered sheet or any other sheet you wish to keep..
. 2 ) For now it does not delete or clear the unfiltered sheet.


…… I suggest an alternative here for your application would be simply to add new data at the end of the data already in the “unfiltered” sheet and run the macro. The end results, I believe will be the same as what you wish.

First of all: thanks for your time and work. It did help, and works with my sheet.
I will try to dig myself into it.
However what I forgot to mention is that later on I will want to add at least one more sheet where I summarize some of the results, but since the macro deletes all the sheets but the unfiltered I will have to summarize maybe in a new workbook
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top