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.

Attached Files

Utility ID.xlsm (69.0 KB, 9 views)

you can't sort it the way it is.

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.

Attached Files

Utility ID_v2.xlsm (92.4 KB, 10 views)

OK, I'll try that and get back to you. Thanks

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.

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

Attached Images

debug.jpg (66.8 KB, 18 views)

Well, got past that now when I hit F8 I get this error:


Can't execute in break mode.

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.

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.

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.

Attached Files

GAMES.xlsm (101.1 KB, 8 views)

Do you have both the Utility ID and GAMES sheets in the same workbook ?
If so, any others ?

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

This should look after things.

Attached Files

thumb_v4.xlsm (176.4 KB, 8 views)

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.

Just delete it or edit it and run the macro. The macro looks after things.
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.

Yes, the files you've posted have no macros in them and the sheets have not been named "Utility ID" nor "GAMES"
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.

That first code can be disgarded and replaced with the last (in thumb_v4.xlsm) as long as the sheet has the same layout as Utility ID and it's name gets added to the check of sheets that it's ok to operate on.
For example if you were to add another sheet for this to work on.
No problem, glad I was able to help.
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.

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?