#1
|
|||
|
|||
How to sort multiple columns with pages
I have a database if you want to call it that with program names in one column and disk ID numbers in the next column. I can With all the data in just two columns is no problem sorting and keeping everything in order but that's where my expertise stops. What I have is a spreadsheet that has 8 columns with the names and ID's in each two. The page break is at every 50 lines for printing purposes. My problem is when I want to add a new program name and disk ID, I can't make everything either move down and over and to the next page. Am I making any sense? Here's an example of what I need to achieve:
If I added a new program name Bull's Eye 2 after the Bull's Eye entry, I would have to insert a space before the Business Card M entry along with the disk ID which would move everything down. The problem is I do not want everything to move down in a way, I need it to move down but the last entry in that column before the page break would move over to the top of the next column and so forth. That would also mean that the last entry in the last column would have to move down to the next page and in the first column. Do you follow? So if I did insert that space for the new program name/ID, the last program and ID number in the last two columns at the bottom of the page break which would be Disk Doctor U259, would be forced to move down to the next page line 51 columns 1 & 2. I've attached a pic showing what I am talking about. I am a novice at this so this has me pulling my hair out or at least what I have left of it. |
#2
|
|||
|
|||
What's in row 1 and what's the name of the sheet ?
|
#3
|
|||
|
|||
Right now just the same data as you see in the pic and the name of the sheet is Utility ID. Here's a pic of the top of the sheet. I can put names to it if needed.
|
#4
|
|||
|
|||
On a copy of your picture...
add a new worksheet named ScratchPad paste this code into a standard vba module Code:
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("Utility ID") 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("Utility ID") 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 add some data to "Utility ID" Alt+F8 will bring up the macro dialogue box run the Eight_To_Two_And_Back macro the "ScratchPad" sheet can be hidden. |
#5
|
|||
|
|||
OK, I understand what you are telling me to do but where do I find the VBA Editor?
Remember, I'm a 65 year old newbie at this. |
#6
|
||||
|
||||
hit the ALT key + F11 key
|
#7
|
|||
|
|||
I did but nothing happened. I did go into the macro settings and told it to trust the vba editor, do I need to turn something else on?
|
#8
|
|||
|
|||
Oh, wait, some letters came up FHNPMARWX
|
#9
|
||||
|
||||
Do you have a "Developer" tab in your ribbon?
If not, go to File|Options then pick "Customize Ribbon" and in the right pane check the "Developer" checkbox. Click Ok. Now try Alt+F11 again or go to Developer tab and click "Visual Basic" on the far left. |
#10
|
|||
|
|||
OK, I'm in the editor and I pasted the code into Module1 and then saved the ScratchPad.xlsm. What do I do next?
I added some data to the Utility ID sheet which just moved everything down in that column. I tried the alt F8 and a message came up saying I had to turn off instant replay whatever that is. |
#11
|
|||
|
|||
I may be messing things up. I opened up the editor and brought up the macro1 code and told it to run it using the Eight_To_Two_And_Back macro and it came up with an error: Run-time error '9': Subscript out of range.
|
#12
|
||||
|
||||
I think NoSparks will have to help you with that. You seem to have run the macro correctly but something wrong with the code supplied or dataset you have.
|
#13
|
|||
|
|||
OK, I have to run out for a couple hours so maybe he can look at it. I do have a question though about when he says to enter an entry into my Utility ID, if I try to insert the entry to where it should go, I have to right click on that cell and it asks me how I want to move the data that is already there. Where and how am I suppose to add a new entry? I'll be back later. Thanks to both of your help so far, I really appreciate it.
|
#14
|
||||
|
||||
I think he means to make sure your "Utility ID" sheet is complete with the data you want in it.
|
#15
|
|||
|
|||
Would have been better to post a sample Excel file rather than a picture, then there's no questions as to what you're working with.
Anyway, here's the test file I put together for this, "ScratchPad" is hidden. The code is for this file, not necessarily yours, unless I've got everything right as to what you're working with. As for adding data... doesn't matter where you put it as everything gets re-sorted. I'd just stuff it on the bottom as another record. Instant replay.... never seen or heard of that, can't help there. You're past this part of things but just for info... Don't know about Excel 2013 but my 2010 installation had Disable all macros without notification as a default setting. I changed it to Disable all macros with notification. This was done under File > Options > Trust Center > Trust Center Settings > Macro Settings. |
Thread Tools | |
Display Modes | |
|
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 |