Tidying/Amending code

[Patriot2879]

Hi just wondering is there any way of tidying up the code below as it is repeated quite a lot just wondering if there was a easier/better way in putting it all together. hope you can help.

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, "F"), .Cells(.Cells(Rows.Count, "F").End(xlUp).Row, "F")).Copy
    End With
     pasteSheet.Cells(Rows.Count, 33).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, "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, "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
    lr = Sheets("New").Cells(Rows.Count, "B").End(xlUp).Row
    Sheets("New").Range("A3:A" & lr) = Sheets("New").Range("A2")

End Sub

 

[MARK858]

Are columns 2,33,34,8,19,17 of the pastesheet the same number of rows or not?

 

[Patriot2879]

aww I think I gone wrong there, because they psting is over the place when I activate it, when I do the pase I want I to go down 1 row for example A then count across to the correct column ie count 8 for H

 

[MARK858]

aww I think I gone wrong there, because they psting is over the place when I activate it, when I do the pase I want I to go down 1 row for example A then count across to the correct column ie count 8 for H

I am afraid that it is unclear from the above where you want the data to paste to.

 

[Patriot2879]

I am copying from a sheet called 'sheet1' and pasting into a sheet called 'New'

 

[MARK858]

That is not what I mean you state "for example A then count across to the correct column ie count 8 for H".
Where does the 8 come from?
How does it relate to lines like the below?

pasteSheet.Cells(Rows.Count, [COLOR="#FF0000"]33[/COLOR]).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

At the moment it looks like they are random locations.

As it stands the best I can do with tidying the code is below but you have stated that the paste locations are wrong and so I need detailed information on the destinations or the method to get those destinations.

Private Sub CommandButton3_Click()

    Set copySheet = Worksheets("Sheet1")
    Set pasteSheet = Worksheets("New")

    Application.ScreenUpdating = False

    With copySheet
        .Columns("C:C").Sort Key1:=.Range("C:C"), Order1:=xlDescending, Header:=xlYes

        .Range(.Cells(2, "C"), .Cells(.Cells(Rows.Count, "C").End(xlUp).Row, "C")).Copy
        pasteSheet.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        Range("B2", Range("B2").End(xlDown)).NumberFormat = "0"

        .Range(.Cells(2, "F"), .Cells(.Cells(Rows.Count, "F").End(xlUp).Row, "F")).Copy
        pasteSheet.Cells(Rows.Count, 33).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

        .Range(.Cells(2, "F"), .Cells(.Cells(Rows.Count, "F").End(xlUp).Row, "F")).Copy
        pasteSheet.Cells(Rows.Count, 34).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

        .Range(.Cells(2, "L"), .Cells(.Cells(Rows.Count, "L").End(xlUp).Row, "L")).Copy
        pasteSheet.Cells(Rows.Count, 8).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

        .Range(.Cells(2, "M"), .Cells(.Cells(Rows.Count, "M").End(xlUp).Row, "M")).Copy
        pasteSheet.Cells(Rows.Count, 19).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

        .Range(.Cells(2, "Q"), .Cells(.Cells(Rows.Count, "Q").End(xlUp).Row, "Q")).Copy
        pasteSheet.Cells(Rows.Count, 17).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    End With


    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

    lr = Sheets("New").Cells(Rows.Count, "B").End(xlUp).Row
    Sheets("New").Range("A3:A" & lr) = Sheets("New").Range("A2")

    Application.ScreenUpdating = True
End Sub

 

[Patriot2879]

Hi for example number 33 is pasting into AG it's the alphabet number count after 26 which is z and 27 is as 28 is ab etc. Sorry I think I got confused with your question

 

[Patriot2879]

Hiya for example the snippet of code below I want to copy from 'Sheet1' column L to last row and paste into Sheet 'New' in row count 8 which is column H

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[\CODE]

 

[MARK858]

I know the numbers are the column numbers based on the alphabet (that is what the Cells syntax is) but what I don't know is what you are using to define that particular number.

If they are random columns then if they are incorrect in your code you need to state what the correct columns are, if they are not random then you need to state the pattern.

Or do you mean from this

I want I to go down 1 row for example A then count across

that you want to go one cell down from the last cell with data in column A and copy to the columns already stated? (just a guess and was basically what I was asking in post number 2).

I am sure what you are asking is clear when you are looking at the sheet but unfortunately we can't see it so we need to be clear on your requirement.

We will get there

 

[Patriot2879]

Hi yes that's it for each paste it has to go down 1 then count across. Because along the top row I have headers. Hope this helps sorry I am not very good at explaining it better