Data selection works, paste option doesn't

**Gralyndr** · 12-02-2011, 12:26 PM

Hello, folks. If I goof this up, please pardon me, new guy here.

I have a database workbook with one sheet to help speed up the data entry process. It may be crude at the moment but it keeps it simple for the end users. Problem I have at the moment is I finally got the macro, "moveit", to select the correct tab to paste to but I get errors on the pasting options. I'm missing something small I can feel it. I was hoping another set of trained eyes can see my problem. I can tinker my way thru Excel but far from being good. Any help would be fantastic. I'll attach a copy of the workbook instead of pasting code to keep it simple.

**tigeravatar** · 12-02-2011, 01:05 PM

Gralyndr,

Give this a try:

Sub moveit()
'
' moveit Macro
'
    
    If Trim(Range("A5").Value) = vbNullString Then
        Range("A5").Select
        MsgBox "Must select a category.", , "Insert Error"
        Exit Sub
    End If
    
    With Sheets(Choose([Match(A5,AH2:AH10,0)], "Res.Contractors", "Comm.Contractors", "Service-Remodelers", "Tradesmen", "Resellers", "Architect-Designers", "Realtor-Flipper", "Property Managers", "Owner-Builder"))
        .Cells(Rows.Count, "A").End(xlUp).Offset(1).Resize(, 23).Value = Range("AA15:AW15").Value
    End With
    
    Range("C4:C26").ClearContents
    Range("C4").Select
    
End Sub

**mrice** · 12-02-2011, 01:16 PM

You are not far off.

Try something like

Sub moveit()
'
' moveit Macro
'
Dim Move2Cat As String
    Move2Cat = Range("A5").Value
    Range("AA15:AW15").Copy
    Select Case Move2Cat
        Case Is = "Resisdental"
            Worksheets("Res.Contractors").Activate
        Case Is = "Commerical"
            Worksheets("Res.Contractors").Activate
        '..... add others here
    
    
    End Select
    Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Data Entry").Activate
    Range("C4:C26").ClearContents
    Range("C4").Select
    
End Sub

**Gralyndr** · 12-02-2011, 01:36 PM

tigeravatar, your's hit spot on, good sir. Awesome. Exactly what I was trying to do. I noticed some new functions in there so you gave me some homework, too. Thank you very much.

mrice, your's works well, too, but tiger's is compacted down real well and it'll help keep my file size down. This little project is getting rather big.

Gentlemen, many thanks.