Unable to paste alphanumeric values using VBA

[Rossco82]

I have the below code which works fine (all be it some of you will look at it and shudder). However I have just had it srpung on me that the materials are alpha numeric (innitially we were told numeric). I didn't think this would be an issue, but the paste special option doesn't work now.

I have tried the suggestions in this post

https://stackoverflow.com/questions/6778759/copyin...

, but with no joy. I've tried selecting exactly matching field sizes and a single cell, again, no difference.

The horrendous code:

Sub ButtonMacro()
    
    'Hide alerts
      Application.DisplayAlerts = False
      
      'Insert Sheets
    Sheets.Add.Name = "Table"
    
    'Insert Sheets
    Sheets.Add.Name = "Converted"
    
    Sheets("Converted").Activate
    Range("A1").Formula = "MSG"
    Range("B1").Formula = "=Order!F2"
    Range("C1").Formula = "ORDER"
    Range("D1").Formula = "1400008000"
    Range("E1").Formula = "501346009175"
    Range("F1").Formula = "=TODAY()"
        Range("F1").Select
            Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    
    Range("G1").Formula = "=Now()"
        Range("G1").Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
       
    Range("A2").Formula = "HDR"
    Range("B2").Formula = "C"
    'Missing C
    'Missing D
    'Missing E
    'Missing F
    Range("G2").Formula = "=Order!F2"
    Range("H2").Formula = "=Order!D2"
    'Missing I
    'Missing J
    Range("K2").Formula = "=Order!C2"
    Range("L2").Formula = "=Order!F5"
    'Missing M
    Range("N2").Formula = "=Order!F7"
    Range("O2").Formula = "=Order!F8"
    'Missing P
    Range("Q2").Formula = "=Order!F9"
    Range("R2").Formula = "=Order!F12"
    'Missing S
    'Missing T
    'Missing U
    'Missing V
    Range("A3").Formula = "=IF(I3>0,""POS"","""")"
    Range("A3").Select
    Selection.Copy
    Selection.AutoFill Destination:=Range("A3:A2000"), Type:=xlFillDefault
        Range("A3:A2000").Select
    Range("B3").Formula = "=ROW()*10-20"
    Range("C3").Formula = "=Order!C15"
    Range("D3").Formula = "=Order!A15"
    Range("E3").Formula = "=Order!B15"
    Range("F3").Formula = "=Order!D15"
    Range("G3").Formula = "=Order!F15"
    Range("H3").Formula = "=IF(Order!F15<1,"""",""GBP"")"
    Range("I3").Formula = "=Order!H15"
    Range("J3").Formula = "=Order!I15"
    Range("K3").Formula = "=Order!K15"
    
        
        'Format cells to remove 0 value
        Range("A1:Z2000").Select
        Range("Z2000").Activate
        Selection.NumberFormat = "#;#;"
    
    'Copy materials table and modify to correct format
        Sheets("Order").Select
        Range("A14:J2000").Select
        Selection.Copy
        Sheets("Table").Activate
        Range("A1").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    
        Columns("A:A").Select
        Application.CutCopyMode = False
        Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    
        Columns("D:D").Select
        Selection.Cut Destination:=Columns("A:A")
    
        Columns("E:E").Select
        Selection.Cut Destination:=Columns("D:D")
    
        Columns("G:G").Select
        Selection.Cut Destination:=Columns("E:E")
    
        Columns("F:F").Select
        Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    
        Columns("I:I").Select
        Selection.Cut Destination:=Columns("F:F")
    
        Columns("I:I").Select
        Selection.Delete Shift:=xlToLeft
    
        Range("A2:J2000").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
    
     'Copy table to converted layout
        Sheets("Converted").Activate
        Range("C3").Select
        **Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False**
       
    'Evaluate last cell containing a quantity and enter TRA
            Cells(Application.Evaluate("MAX(IF(I3:I2000<>"""",ROW(I3:I2000)),0,1)"), "A").Select
        ActiveCell.Offset(1, 0) = "TRA"
    
     'Clear clipboard
        Application.CutCopyMode = False
    
    'Copy to new doc ready to be saved as CSV
    Range("A1:Z2000").Select
        Range(Selection, Selection.End(xlDown)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        Workbooks.Add
        ActiveSheet.Paste
        
    'Format date and time
        Range("F1").Select
        Selection.NumberFormat = "m/d/yyyy"
        Range("G1").Select
        Selection.NumberFormat = "h:mm:ss"
        
    'Locate TRA and add count of POS
        Cells.Find(What:="TRA", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
            xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
            , SearchFormat:=False).Activate
        ActiveCell.Offset(0, 1).Select
        ActiveCell.Formula = "=COUNTIF(A3:A2000,""POS"")"
        
    'Save new CSV doc
        Application.CutCopyMode = False
        ChDir "C:\Windows\Temp"
        ActiveWorkbook.SaveAs Filename:="C:\Windows\Temp\OrderForm.csv", FileFormat _
            :=xlCSV, CreateBackup:=False
        
        
        'Reinstate alerts
    Application.DisplayAlerts = True
    
    'Msg Box to advise complete
    MsgBox "Thank you. Please submit your form"
    End Sub

I have put the problem area in bold which wasn't showing at the time of writing the post, so assuming when I save it's not in bold the problem section is after "'Copy table to converted layout
Sheets("Converted").Activate
Range("C3").Select"

If I change the entries on the order form to numeric it works fine, so what is it about alpha numeric values?. And how do I get around this?. Is it as simple as a formatting issue?.

 

[SkipVought]

Hi,

Just wondering why you are doing this...

'
        Range("A2:J2000").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy

First, do you actually have DATA in all 1999 rows?

Second, do you have data BELOW row 2000?

Allow me to explain what your code is trying to do.

First you SELECT that range of cells Range("A2:J2000")

THEN you add to that Selection all the cells below that Selection, all the way to the last available row in the sheet, Row 1 million and change.

then you TRY to paste those 1 million plus rows in another sheet starting in row 3 and there's not enough rows on that sheet by one row. TILT!

Perhaps you could tell us what your sheet looks like (rows and columns of data) and what your overall objective is.

BTW, I’m guessing that on your sheet that had all numerics, you have some stray data in column A below row 2000 while on the sheet you were trying to COPY ‘n’ PASTE but couldn’t, there isn’t any.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Rossco82]

Hi Skip,

I was sat playing this morning and came to the retaliation of what was going on with this bit of code, so have already rectified it, but yes, you were exactly right. As it turned out the alphanumerics actually did me a favor. I was testing with single digits and the script works, when entering a1, b1, c1 into the cells and trying it it would work. As soon as I change to something like yv45o23y4v5uh4 it would stop working. So my follow up question is why? It was the same copy area, and it works with alpha numerics, just not long alphanumerics?????

The issue we have is that people could order 1 item, they could order 1500-2000 (hence selecting such a broad range). The sheet goes from xls user friendly format to a very unfriendly looking CSV doc in which I did try copying down formula to avoid copying a vast area, but as there is formula in the cells below (or was when I first started creating the macro, and would be again if I did it the original way) there was issues as the cells were being seen as populated (which they would) and this caused knock on effects.

Thanks for taking the time to gleen the right info out of me, but mark this one up as done buddy.

 

[SkipVought]

It’s never a good idea to copy a possibility rather than an actuality.

Assuming that there’s stuff in row 1 that you don’t want to COPY...

'
        Dim rng As Range

        Set rng = Range(Range("A2"), Range("A2").End(xlToRight))
        Range(rng, rng.End(xlDown)).Copy

Otherwise...

‘If there’s nothing in row 1
    Range("A2").CurrentRegion.Copy

Just to TEST what’s happening, replace [tt].Copy[/tt] with [tt].Select[/tt] to observe what the Copy Range is.

If I’m not sure what a piece of code actually does, I’ll run a little test. It’s a good practice. ;-)

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}