#16
|
|||
|
|||
I'm back. I'm having problems. When I add an entry, say Joe Friday/U001 at the end of the list, when I try to sort it is asking me how I want to move the data, up, down, left, right. Of course I want the data to move down but the last entry on row 50 in columns A, D, G should move up to Row 1 of the same columns and the last entry in Row 50 Column K should move to Row 51, Column A. Here's the Utility ID file I am trying to do. So here's how it should look after the sheet has been sorted.
Joe Friday - U001 should go after D-Coder-U001 in Column A row 26 and everything after Joe Friday should move down 1 row until it reaches row 50. The entry that was in Column A Row 50(Proofreader - U002) will move to Column D Row 1 and move the data in that Column down 1 row. The entry that was in Column D row 50(Paperclip - U013) would move to Column G Row 1 and move the data in that Column down 1 row. The entry that was in Column G row 50(MLX - U018) would move to Column J Row 1 and move the data in that Column down 1 row. Now the entry that was in Column J Row 50(Keybrd Buffer - U023) will be moved to Column A Row 51 and the process repeats itself until the end of the entries. Of course the U### tags along with the entry name. ie; Column A1 and Column B1 and so forth are a pair. |
#17
|
|||
|
|||
Quote:
What the code does... Takes all the data from the Utility ID sheet and puts it into columns A & B on the ScratchPad sheet. Sorts alphabetically based on column A, because that's what your first picture appears to be. Deletes everything from the Utility ID sheet. Writes the ScratchPad data back to the Utility ID sheet in 50 row chunks. try this... Re-name Sheet1 to Utility ID Remove the single space from cell A251 Add a new sheet and name it ScratchPad Alt+F11 to bring up the VBA environment From menu, Insert module Copy the code to vba module 1 Close the VBA environment Add Joe Friday/U001 at the bottom of columns D & E at row 246 Alt+F8 and run the macro. Joe Friday is now at A135. |
#18
|
|||
|
|||
OK, I'll try that and get back to you. Thanks
|
#19
|
|||
|
|||
OK, I did everything you said and all goes OK until I hit Alt F8 and I get a bullet that says it can not do that with Instant Replay turned on. I have no idea what Instant Replay is nor where to turn it off at.
|
#20
|
|||
|
|||
OK, the Instant Replay is in my graphics card. Anyway I turned it off and ran the module and got the same runtime error as I stated earlier. Clicking on debug it gave me this which I took a screenshot of. I don't know why because I have the sheet named Utility ID
|
#21
|
|||
|
|||
Well, got past that now when I hit F8 I get this error:
Can't execute in break mode. |
#22
|
|||
|
|||
thumb, the file I attached in post #17 (Utility ID_v2.xlsm) shows as having 0 views, have you not downloaded it yet ?
It's what your file should be like after implementing the 'try this' suggestions. I know it works. I can't think the error with Set src = Sheets("Utility ID") being anything other than the name of the sheet. Maybe leading or trailing space(s) or too many in the middle ? Can't execute in break mode. click the little square reset button on the toolbar then try again. |
#23
|
|||
|
|||
OK, it almost worked perfectly except it has two Joe Friday's, one on row 135 and one on row 136. Unless you already had put one there on the Utility IDv2, I didn't look to see if it was there or not beforehand. Or was I suppose to use my Uility ID sheet?
Nevermind, I went back and looked at your original Utility ID v2 and you already had a joe friday at 135 so when I ran it, it put another one there. Only thing I see different is that in the v2 version, you have it sorted by name and in my original, it was sorted by ID. So does that mean I have to sort by using the name instead of the ID? If so that's OK because I usually go by the name anyway. |
#24
|
|||
|
|||
OK, I'm trying to run this using another sheet called GAMES which is sorted by the names but when I run the alt F8 it gives me an Out of range error. Is the code you gave me just for the Utility sheet? I did go in the code and changed the name from Utility ID to Games in two places thinking that's all it needed. The Games sheet is the same as the Utility ID sheet other than different names and file #'s.
|
#25
|
|||
|
|||
Do you have both the Utility ID and GAMES sheets in the same workbook ?
If so, any others ? |
#26
|
|||
|
|||
Just those two. How do I post the code that I changed? It's the same code you gave me in the beginning just changed the name from Utility to GAMES in both places.
Ahh, got it.... Option Explicit Sub Eight_To_Two_And_Back() Dim src As Worksheet, dest As Worksheet Dim r As Long, c As Long, x As Long Dim ray As Variant Set src = Sheets("GAMES") Set dest = Sheets("ScratchPad") dest.UsedRange.Delete r = 1 For c = 1 To 10 Step 3 With src ray = Range(.Cells(1, c), .Cells(Rows.Count, c + 1).End(xlUp)).Value x = UBound(ray, 1) End With With dest .Cells(r, 1).Resize(x, 2) = ray r = r + x End With Next c 'sort dest.Sort.SortFields.Clear dest.Sort.SortFields.Add Key:=Range("A1:A" & Rows.Count).End(xlUp), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With dest.Sort .SetRange Range(dest.Range("A1"), dest.Range("B" & Rows.Count).End(xlUp)) .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Call BackToEight End Sub Private Sub BackToEight() Dim i As Long, lr As Long Dim r As Long, c As Long Dim src As Worksheet, dest As Worksheet Set src = Sheets("ScratchPad") Set dest = Sheets("GAMES") dest.UsedRange.ClearContents lr = src.Range("A" & Rows.Count).End(xlUp).Row r = 1 c = 1 For i = 1 To lr Step 50 dest.Cells(r, c).Resize(50, 2).Value = src.Cells(i, 1).Resize(50, 2).Value c = c + 3 If c = 13 Then r = r + 50 c = 1 End If Next i End Sub |
#27
|
|||
|
|||
This should look after things.
|
#28
|
|||
|
|||
Yes, that worked great! I do have a few questions. Say if I made a mistake when adding a new entry whatever it is, mispelled name, wrong ID, or even found out that I had already had it entered and I want to delete it. How would I go about deleting an entry and have everything else move to where it suppose to?
Second question is are you putting a macro in the files you are giving me? Reason I'm asking is because I know the code I have has different names in them. Third question is are these codes/macros file oriented? In other words, the code that you gave me in the beginning, will it only work on the Utility ID file and ScratchPad or can I change the names and use the code for other files I have like the GAMES file I am trying to do which when I try gives me an 'Subscript Out Of Range' error? BTW, thank you so much for helping me out, I don't think I would ever have been able to figure this out myself. |
#29
|
||||
|
||||
Quote:
It's like you said originally... no problem when only dealing with 2 columns. That's the crutch of what the macro does... Puts things into two columns on the ScratchPad so they can be worked with. The sorting is automated there. Everything on the 8 column sheet is deleted then 50 row chunks are written from the ScratchPad back to the 8 column sheet. Quote:
I've adjusted the names of the sheets and within the macros to suit the most recent issues. You'll notice the last file I posted checks if the active sheet is named either Utility ID or GAMES and if it is uses Activesheet rather than the specific name, and if it isn't either gives the Goofy sheet message. Try it, try running the macro with the ScratchPad being the active sheet. Quote:
For example if you were to add another sheet for this to work on. Code:
'make sure dealing with an acceptable sheet If ActiveSheet.Name <> "GAMES" And ActiveSheet.Name <> "Utility ID" And ActiveSheet.Name <> "whatever sheet" Then MsgBox "Goofy Sheet" & vbLf & "Will not continue" Exit Sub End If Quote:
You're a 65 year old beginner. We all had to start somewhere. I've got a couple of years on you and only started playing with this stuff (for entertainment) when I retired. Good Luck with your project. |
#30
|
|||
|
|||
So as long as the layout is the same and I name whatever file I want to do with Games or Utility ID and use the macro that's in the Thumb sheet I'll be good to go?
Reason I ask is that I have a few other projects that I need to do and they all use the same layout. I just need to rename them and I'm good to go...Right? After they have been sorted, I can rename them back to whatever name I need them to be so I don't get confused when printing them out? So where do I add the macro, in Sheet 1(games or utility ID) or in sheet 2 in the ScratchPad? |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Macro to sort columns | SerenityNetworks | Excel Programming | 4 | 09-02-2016 06:20 AM |
.pdf to .xlsx - How to get sort-able columns? | CCinPA | Excel | 0 | 07-05-2016 08:30 AM |
Normal sort not bringing along other columns | Dave Fraser | Excel | 2 | 06-06-2014 11:48 AM |
quick replace, sort, change columns | userman | Excel | 1 | 05-01-2012 06:24 AM |
How to sort table having three columns? | Bahir Barak | Word | 2 | 01-20-2011 01:52 PM |