Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Unable to paste alphanumeric values using VBA

Unable to paste alphanumeric values using VBA

Unable to paste alphanumeric values using VBA

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"
    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()"
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    Range("G1").Formula = "=Now()"
        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"","""")"
    Selection.AutoFill Destination:=Range("A3:A2000"), Type:=xlFillDefault
    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
        Selection.NumberFormat = "#;#;"
    'Copy materials table and modify to correct format
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
        Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        Selection.Cut Destination:=Columns("A:A")
        Selection.Cut Destination:=Columns("D:D")
        Selection.Cut Destination:=Columns("E:E")
        Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        Selection.Cut Destination:=Columns("F:F")
        Selection.Delete Shift:=xlToLeft
        Range(Selection, Selection.End(xlDown)).Select
     'Copy table to converted layout
        **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(Selection, Selection.End(xlDown)).Select
        Range(Selection, Selection.End(xlDown)).Select
    'Format date and time
        Selection.NumberFormat = "m/d/yyyy"
        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

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?.

RE: Unable to paste alphanumeric values using VBA


Just wondering why you are doing this...


        Range(Selection, Selection.End(xlDown)).Select

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.


glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Unable to paste alphanumeric values using VBA

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.

RE: Unable to paste alphanumeric values using VBA

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 



‘If there’s nothing in row 1

Just to TEST what’s happening, replace .Copy with .Select 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. winky smile


glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close