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

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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
350
nathanzumbaugh
nathanzumbaugh
C
  • Solved
Copy data from multiple sheets into a master sheet - VBA button stuck
Replies
3
Views
183
NdNoviceHlp
NdNoviceHlp
H
  • Solved
Macro to speedy up process when copying Data
Replies
3
Views
76
Logit
Logit
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
167
nathanzumbaugh
nathanzumbaugh
W
  • Solved
Looking For Tips To Improve My VBA
Replies
8
Views
198
who am i
W

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,633
Latest member
DougMo

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