Copy,Paste,Arrange A-Z

  • Hi,

    I am trying to sort some data taken from a worksheet & after a temp worksheet is made sort that data A-Z

    I have at present this code but getting confused why it fails to sort.
    can you advise please where Ive gone wrong.
    The data is copied from the source sheet, the temp worksheet is created & the values then pasted. Now this is where it fails when it attempts to sort.

    1. Private Sub NEWSHEET_Click()
    2. Sheets.Add(After:=Sheets("INFO")).Name = "SORT SHEET"
    3. Worksheets("HONDA SHEET").Range("C21", Range("C" & Rows.Count).End(xlUp)).Copy Worksheets("SORT SHEET").Range("A1")
    4. Worksheets("SORT SHEET").Activate
    5. Worksheets("SORT SHEET").Range("A1", Range("A" & Rows.Count).End(xlUp)).Sort [A1], xlAscending
    6. End Sub
  • Hello Maurice,

    Try your code amended as follows:-

    I hope that this helps.



  • Thanks for the quick reply.
    I decided to do the code above as currently I had issues depending on how the worksheet was sorted then the list box didn’t show the correct name for me.
    let me explain.
    I only need column C to be loaded into listbox.
    My issue I had seen was should the worksheet be sorted on column B then when I opened the userform & searched for A on the listbox would show whatever was the first A in column C at the time, basically the worksheet was sorted on B so Column C was all jumbled. If the worksheet was sorted on column C then all is perfect. So I decided the above, take info from C then paste to new sheet & sort. This would sort my problem.

    I have supplied the code which currently runs from the source worksheet BUT i need to now edit it so it takes the sorted information that’s just been sorted on the SORT SHEET & supply it to the listbox & of course once done delete the SORT SHEET.

    Hope you are able to assist/advise.
    Have a nice day.

  • Hello Maurice,

    From what I can understand, you just want the sorted cell range from the SORT sheet loaded into the ListBox.

    If that's the case, then you could do it with two codes as follows:-

    The LoadListBox sub is called through the NEWSHEET_Click sub. The SORT sheet is then deleted as well.

    I hope that this helps.



  • Hello Maurice,

    I would say that that error has come up because the listbox is on a different sheet.

    Hence, in front of this line:-

    ListBox1.AddItem c.Value

    place the sheet name in which the listbox resides.


    Sheets("HONDA SHEET").ListBox1.AddItem c.Value

    You can, of course, declare a sheet variable to tidy things up a little just like in the first code above.



  • Hi,

    Ive now added the line of code like so,

    Sheets("HONDA SHEET").ListBox1.AddItem c.Value

    The listbox is on the worksheet HONDA SHEET

    When i try this i now see a Run Time Error 438


    This is now shown in yellow when i debug,

    Sheets("HONDA SHEET").ListBox1.AddItem c.Value

    I added what i then thought was correct but still the same.

    This is the code i had at present

  • Ok,

    I see what it was.

    The code written was for a ListBox on the worksheet,where i actually have a Userform on a worksheet which then has the ListBox on the userform.

    So now my code looks like this & some progress is being made BUT.....

    At present this is what happens.

    I press the command button NEW SHEET & i see a message box advising data may exist on the sheet.

    I select NO & see the new worksheet created at the end called SORT SHEET & checking it i also see it sorted A-Z.

    Obvioulsy if i had selected YES then i would of seen that sheet on the end BUT then this is as far as it gets.

    What should happen after the new SORT SHEET has been created & sorted is Load names into ListBox & then dlete the SORT SHEET.

    At this point the sheet in question that should open is HONDA SHEET

    The userform that should open is called HondaSheetNameSearch

    The in ListBox1 i should see in the prder A-Z the names copied over from the sheet we have just deleted SORT SHEET