Sorting A-z in sheet1 in column c

P

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,259
Office Version
  1. 2010
Platform
  1. Windows
Hi I have the code below where I am copying data over from sheet1 to New, but first I want in sheet1 column b sorting A-z, I have the code below where everything else works apart from the first part sorting column C in Sheet1 A-Z, please can you help?
Code: 
Private Sub CommandButton3_Click()
With Sheets("Sheet1")
        Columns("C:C").Sort Key1:=Range("C:C"), Order1:=xlDescending, Header:=xlYes
    End With
    
  Set copySheet = Worksheets("Sheet1")
  Set pasteSheet = Worksheets("New")
    With copySheet
    .Range(.Cells(2, "C"), .Cells(.Cells(Rows.Count, "C").End(xlUp).Row, "C")).Copy
  End With
  pasteSheet.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
     Range("B2", Range("B2").End(xlDown)).NumberFormat = "0"
  
  With copySheet
   .Range(.Cells(2, "L"), .Cells(.Cells(Rows.Count, "L").End(xlUp).Row, "L")).Copy
    End With
     pasteSheet.Cells(Rows.Count, 8).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
     
       With copySheet
   .Range(.Cells(2, "M"), .Cells(.Cells(Rows.Count, "M").End(xlUp).Row, "M")).Copy
    End With
     pasteSheet.Cells(Rows.Count, 19).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
     
         With copySheet
   .Range(.Cells(2, "F"), .Cells(.Cells(Rows.Count, "F").End(xlUp).Row, "F")).Copy
    End With
     pasteSheet.Cells(Rows.Count, 34).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
     
     
          With copySheet
   .Range(.Cells(2, "F"), .Cells(.Cells(Rows.Count, "F").End(xlUp).Row, "F")).Copy
    End With
     pasteSheet.Cells(Rows.Count, 35).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
     
               With copySheet
   .Range(.Cells(2, "Q"), .Cells(.Cells(Rows.Count, "Q").End(xlUp).Row, "Q")).Copy
    End With
     pasteSheet.Cells(Rows.Count, 17).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
     
    Application.ScreenUpdating = False
    With Range("Q2", Range("Q" & Rows.Count).End(xlUp))
        .EntireColumn.Insert
        .NumberFormat = "@"
        With .Offset(, -1)
            .FormulaR1C1 = "=Text(RC[1],""dd/mm/YYYY"")"
            .Offset(, 1).Value = .Value
            .EntireColumn.Delete
        End With
    End With
    Application.ScreenUpdating = True

End Sub
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
Re: Sorting A-z in sheet1 in column c help

HI I have also tried amending to the below but still no joy.
Code: 
Private Sub CommandButton2_Click()
  Dim copySheet As Worksheet
  Dim pasteSheet As Worksheet
  Set copySheet = Worksheets("New")
  Set pasteSheet = Worksheets("Combined")
Range("AL2", Range("AL2").End(xlDown)).Copy Range("B2")
Range("B2", Range("B2").End(xlDown)).NumberFormat = "0"
Range("G2", Range("G2").End(xlDown)).Copy Range("D2")
Range("AH2", Range("AH2").End(xlDown)).Copy Range("AI2")
Dim lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
With Range("C2:C" & lr)
    .Formula = "=VLOOKUP(B2,Old!B:C,2,FALSE)"
    .Value = .Value
End With
With Range("E2:E" & lr)
    .Formula = "=VLOOKUP(B2,Old!B:E,4,FALSE)"
    .Value = .Value
End With
With Range("AJ2:AJ" & lr)
    .Formula = "=VLOOKUP(LEFT(AI2,LEN(AI2)-2),PC!A:B,2,FALSE)"
    .Value = .Value
    
With copySheet
    .Range(.Cells(2, "A"), .Cells(.Cells(Rows.Count, "A").End(xlUp).Row, "BE")).Copy
  End With
  
  pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
      Sheets("Combined").Select
    ActiveSheet.Range("B2").EntireColumn.Select
    Selection.NumberFormat = "0"
    ActiveSheet.Range("Q2").EntireColumn.Select
    Selection.NumberFormat = "dd/mm/yyyy"
        ActiveSheet.Range("AB2").EntireColumn.Select
    Selection.NumberFormat = "dd/mm/yyyy"
End With
End Sub

Private Sub CommandButton3_Click()
Application.EnableEvents = False
With Sheets("Sheet1")
        Columns("C:C").Sort Key1:=Range("C:C"), Order1:=xlDescending, Header:=xlYes
        Application.EnableEvents = True
    End With
 
 
  Set copySheet = Worksheets("Sheet1")
  Set pasteSheet = Worksheets("New")
    With copySheet
    .Range(.Cells(2, "C"), .Cells(.Cells(Rows.Count, "C").End(xlUp).Row, "C")).Copy
  End With
  pasteSheet.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
     Range("B2", Range("B2").End(xlDown)).NumberFormat = "0"
  
  With copySheet
   .Range(.Cells(2, "L"), .Cells(.Cells(Rows.Count, "L").End(xlUp).Row, "L")).Copy
    End With
     pasteSheet.Cells(Rows.Count, 8).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
     
       With copySheet
   .Range(.Cells(2, "M"), .Cells(.Cells(Rows.Count, "M").End(xlUp).Row, "M")).Copy
    End With
     pasteSheet.Cells(Rows.Count, 19).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
     
         With copySheet
   .Range(.Cells(2, "F"), .Cells(.Cells(Rows.Count, "F").End(xlUp).Row, "F")).Copy
    End With
     pasteSheet.Cells(Rows.Count, 34).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
     
     
          With copySheet
   .Range(.Cells(2, "F"), .Cells(.Cells(Rows.Count, "F").End(xlUp).Row, "F")).Copy
    End With
     pasteSheet.Cells(Rows.Count, 35).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
     
               With copySheet
   .Range(.Cells(2, "Q"), .Cells(.Cells(Rows.Count, "Q").End(xlUp).Row, "Q")).Copy
    End With
     pasteSheet.Cells(Rows.Count, 17).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
     
    Application.ScreenUpdating = False
    With Range("Q2", Range("Q" & Rows.Count).End(xlUp))
        .EntireColumn.Insert
        .NumberFormat = "@"
        With .Offset(, -1)
            .FormulaR1C1 = "=Text(RC[1],""dd/mm/YYYY"")"
            .Offset(, 1).Value = .Value
            .EntireColumn.Delete
        End With
    End With
    Application.ScreenUpdating = True

End Sub
 
Upvote 0
Patriot2879 said:
Hi I have the code below where I am copying data over from sheet1 to New, but first I want in sheet1 column b sorting A-z, I have the code below where everything else works apart from the first part sorting column C in Sheet1 A-Z, please can you help?
Code: 
Private Sub CommandButton3_Click()
With Sheets("Sheet1")
        Columns("C:C").Sort Key1:=Range("C:C"), Order1:=xlDescending, Header:=xlYes
    End With
    
  Set copySheet = Worksheets("Sheet1")
  Set pasteSheet = Worksheets("New")
    With copySheet
    .Range(.Cells(2, "C"), .Cells(.Cells(Rows.Count, "C").End(xlUp).Row, "C")).Copy
  End With
  pasteSheet.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
     Range("B2", Range("B2").End(xlDown)).NumberFormat = "0"
  
  With copySheet
   .Range(.Cells(2, "L"), .Cells(.Cells(Rows.Count, "L").End(xlUp).Row, "L")).Copy
    End With
     pasteSheet.Cells(Rows.Count, 8).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
     
       With copySheet
   .Range(.Cells(2, "M"), .Cells(.Cells(Rows.Count, "M").End(xlUp).Row, "M")).Copy
    End With
     pasteSheet.Cells(Rows.Count, 19).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
     
         With copySheet
   .Range(.Cells(2, "F"), .Cells(.Cells(Rows.Count, "F").End(xlUp).Row, "F")).Copy
    End With
     pasteSheet.Cells(Rows.Count, 34).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
     
     
          With copySheet
   .Range(.Cells(2, "F"), .Cells(.Cells(Rows.Count, "F").End(xlUp).Row, "F")).Copy
    End With
     pasteSheet.Cells(Rows.Count, 35).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
     
               With copySheet
   .Range(.Cells(2, "Q"), .Cells(.Cells(Rows.Count, "Q").End(xlUp).Row, "Q")).Copy
    End With
     pasteSheet.Cells(Rows.Count, 17).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
     
    Application.ScreenUpdating = False
    With Range("Q2", Range("Q" & Rows.Count).End(xlUp))
        .EntireColumn.Insert
        .NumberFormat = "@"
        With .Offset(, -1)
            .FormulaR1C1 = "=Text(RC[1],""dd/mm/YYYY"")"
            .Offset(, 1).Value = .Value
            .EntireColumn.Delete
        End With
    End With
    Application.ScreenUpdating = True

End Sub
Click to expand...
Which column to be sorted
Assuming as column C, just try with the below
Code: 
With Sheets("Sheet1")
        .Columns("C:C").Sort Key1:=.Range("C:C"), Order1:=xlDescending, Header:=xlYes
End With
 
Last edited:
Upvote 0
That works great thank you, where did I go wrong? was it just the .Columns but?
 
Upvote 0
You must log in or register to reply here.

Similar threads

nathanzumbaugh
  • Solved
Paste cell range below specific named cell(s)
Replies
7
Views
332
nathanzumbaugh
nathanzumbaugh
nathanzumbaugh
  • Question Question
Add blank cells below pasted data from sheet to sheet using VBA-have working code, just need add row syntax
Replies
0
Views
165
nathanzumbaugh
nathanzumbaugh
nathanzumbaugh
  • Question Question
VBS-Command Control Button-Add row below pasted content maintaining format of destination row
Replies
2
Views
262
nathanzumbaugh
nathanzumbaugh
T
  • Question Question
Slow macro
Replies
5
Views
295
topi1
T
C
  • Solved
Need Help VBA
2
Replies
10
Views
406
Trebor76
T

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

Share this page

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