I am trying to copy a selection from one worksheet to another using vba and style the new sheet but one set of column values is not copying over

Question

I can get everything but the original column "U" to copy over (which is a date field). Literally everything else works but this one field, any ideas as to why this one field would not copy over?

Here's what I wrote so far:

Sub CopiesandMovesReferralDatafromOLAB()


    Cells.Select

    Range("D:D,C:C,E:E,AM:AM,F:F,R:R,AI:AI,AJ:AJ,U:U").Select
        Application.CutCopyMode = False
        Selection.Copy
        Workbooks.Add
        ActiveSheet.Paste

    Range("A1").Select 
        ActiveCell.FormulaR1C1 = "xxx"
        Range("B1").Select
        ActiveCell.FormulaR1C1 = "xxx"
        Range("C1").Select
        ActiveCell.FormulaR1C1 = "xxx"
        Range("D1").Select
        ActiveCell.FormulaR1C1 = "xxx"
        Range("E1").Select
        ActiveCell.FormulaR1C1 = "xxx"
        Range("F1").Select
        ActiveCell.FormulaR1C1 = "xxx"
        Range("G1").Select
        ActiveCell.FormulaR1C1 = "xxx"
        Range("H1").Select
        ActiveCell.FormulaR1C1 = "xxx"
        Range("I1").Select
        ActiveCell.FormulaR1C1 = "xxx"
        Range("J1").Select
        ActiveCell.FormulaR1C1 = "xxx"

        Range("A1:J1").Select
        Selection.Font.Bold = True
        With Selection.Font
        .ThemeColor = xlThemeColorDark1
            .TintAndShade = 0
        End With

        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 10053222
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With

        Cells.Select
        Cells.EntireColumn.AutoFit

End Sub

Just saying, you'll *want* to fix that to avoid *further* errors down the line, *and* to end up with code that's easier to read, to maintain, and more importantly, to debug. Like what you're trying to do right now. — Mathieu Guindon, Aug 18 '17 at 14:48
i don't know how to use variables and what not (i'm not a programmer) and don't know how to convert what i have to that format — K. Rheinheimer, Aug 18 '17 at 14:51
It copied column U when I tested it. You have 9 columns selected when you copy and you're placing `xxx` in 10 columns, so maybe is working as expected? The copied columns appear in the alphabetical order - so column U appears in column F after pasting (6th column being copied). — Darren Bartrup-Cook, Aug 18 '17 at 15:14

Mathieu Guindon · Accepted Answer · 2017-08-18T15:22:57.530

0

Not sure why column U wouldn't copy over, but work with objects instead, and supply the destination range to the Copy method, so that you don't need two instructions, and you're not pasting on whatever random cell is currently active in that destination worksheet - if that cell is A1, then your header row is currently overwriting the first row of data you're pasting over:

Dim sourceSheet As Worksheet
Set sourceSheet = ActiveSheet

Dim targetBook As Workbook
Set targetBook = Application.Workbooks.Add

Dim targetSheet As Worksheet
Set targetSheet = targetBook.Worksheets(1)

With sourceSheet.Range("D:D,C:C,E:E,AM:AM,F:F,R:R,AI:AI,AJ:AJ,U:U")
    .Copy targetSheet.Range("A2")
End With

As for the rest... use With blocks to work with a given specific Range, instead of successively .Selecting each individual cell and working with ActiveCell like macro-recorder "code":

Dim headings() As Variant
headings = Array("Column1","Column2","Column3", ..., "Column10")

With targetSheet.Range("A1:J1")
    .value = headings
    With .Font
        .Bold = True
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With .Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 10053222
        .TintAndShade = 0
        .PatternTintAndShade = 0            
    End With
    .EntireColumn.AutoFit
End With

I don't know how to use variables and what not (I'm not a programmer)

You're writing code, you're programming. Not knowing everything is normal, you're here to learn. As long as you're willing to learn, nothing will stop you. Declare local variables with Dim, assign object references with the Set keyword, specify Option Explicit at the top of every module, and you'll do great!

edited Aug 18 '17 at 15:22

answered Aug 18 '17 at 15:08

Mathieu Guindon

69,817
8
107
235

Sorry - I've been getting frustrated with this. And I realized it was copying over U (I'm an idiot). I'll try this out. Thanks! – K. Rheinheimer Aug 18 '17 at 15:19
Ok - I tried this and I can get it to create the header row in the format I need. However, I have two questions: 1. I need to put specific header titles that are unique to this report, how do I do that? Within this range `With targetSheet.Range("A1:J1") .Value = " "` 2. Doing this did not bring over any data from this range: `With sourceSheet.Range("D:D,C:C,E:E,AM:AM,F:F,B:B,R:R,AI:AI,AJ:AJ,U:U") .Copy targetSheet.Range("A2")` a. I also need the columns to be in the order of the range above – K. Rheinheimer Aug 18 '17 at 15:30
@K.Rheinheimer I've edited my answer to make it easy to specify distinct headers, see revision. – Mathieu Guindon Aug 18 '17 at 15:37
Ah that's amazing, thank you. One more thing - the range that I am moving over is out of the order selected in the first With statement, is there a way to maintain that order? – K. Rheinheimer Aug 18 '17 at 15:47
The behavior should be identical to what it would be if you selected the columns manually holding down Ctrl key and then copying - the order doesn't change, but Excel pastes them one next to the other. – Mathieu Guindon Aug 18 '17 at 15:53
Currently it's copying them in alphabetical order of the range selection, I.E. columns B, C, D, E, F....etc – K. Rheinheimer Aug 18 '17 at 15:55
I got it, I googled and found a different macro to add to do the rearranging! – K. Rheinheimer Aug 18 '17 at 16:08

I am trying to copy a selection from one worksheet to another using vba and style the new sheet but one set of column values is not copying over

1 Answers1