Tidy recorder code to remove activate and select

[Hoggle]

I'm using the code below - recorded to move populated cells from one worksheet to another. I have 31 columns of comments to copy from one sheet to another and I can see this macro is very clunky but I can't seem to get the syntax right when I try to remove bits that I think are not necessary. My brain hurts, can someone please give me a pointer in the right direction?

Columns("B:B").Select
    Selection.SpecialCells(xlCellTypeConstants, 23).Select
    Selection.Copy
    Sheets("Comments").Select
    Range("A3").Select
    ActiveSheet.Paste
    Sheets("HT Review").Select
    Columns("D:D").Select
    Selection.SpecialCells(xlCellTypeConstants, 23).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Comments").Select
    Range("B3").Select
    ActiveSheet.Paste

 

[somnath_it2006]

Do you want to minimize the code?

 

[Hoggle]

Yes please, if at all possible.

 

[somnath_it2006]

Try This:

    Application.ScreenUpdating = False

    Sheets("HT Review").Columns("B:B").SpecialCells(xlCellTypeConstants, 23).Copy
    Sheets("Comments").Range("A3").PasteSpecial (xlPasteAll)
    Application.CutCopyMode = False
    
    Sheets("HT Review").Columns("B:B").SpecialCells(xlCellTypeConstants, 23).Copy
    Sheets("Comments").Range("B3").PasteSpecial (xlPasteAll)
    Application.CutCopyMode = False
    
    Application.ScreenUpdating = True

 

[somnath_it2006]

Sorry.... Please take this updated code:

    Application.ScreenUpdating = False

    Sheets("HT Review").Columns("B:B").SpecialCells(xlCellTypeConstants, 23).Copy
    Sheets("Comments").Range("A3").PasteSpecial (xlPasteAll)
    Application.CutCopyMode = False
    
    Sheets("HT Review").Columns("D:D").SpecialCells(xlCellTypeConstants, 23).Copy
    Sheets("Comments").Range("B3").PasteSpecial (xlPasteAll)
    Application.CutCopyMode = False
    
    Application.ScreenUpdating = True

 

[Hoggle]

Thanks for your help, am going to test now. Had noticed the column names needed changed.....

Will let you know if I have any problems

 

[Hoggle]

Your code works great except I now want to only copy from row 2 to the last used row in the "HT Review" sheet can I amend the code to read

Sheets("HT Review").Columns("B2:B" & lastcell).SpecialCells(xlCellTypeConstants, 23).Copy
    Sheets("Comments").Range("A3").PasteSpecial (xlPasteAll)
    Application.CutCopyMode = False

where I have already set up the value of lastcell or is their a smarter way to do it?

 

[Hoggle]

To answer my own last query this doesn't work as I get a type mismatch error 13. I can always delete the row I don't want when the comments are all copied over.