PasteSpecial to Different Sheets, etc...

Hello, all.

I was messing around trying to get better with VBA and hit a stumbling block. I have attached the simple workbook that I was using, and pasted the code below if you'd like to check that out first:

Private Sub Finished_Click()
    
    Range("B1:B2").Select
    Selection.Copy
    
    Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial _
    Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    
    Range("C8:F8").Select
    Selection.Copy
    
    Range("A65536").End(xlUp).Offset(0, 2).PasteSpecial _
    Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
    Range("E8").Select
    Selection.Activate


End Sub


Private Sub Worksheet_Activate()
     
    Dim cbCVals
    Dim i As Integer
    
    cbCVals = Array("Axle", "Wheel")
    
    For i = 0 To 1
        cbComponents.AddItem cbCVals(i)
        cbComponents2.AddItem cbCVals(i)
    Next i
     
End Sub

K, now here are my issues:

1) For both PasteSpecials, I need the copied data from the Invoice worksheet to be pasted into the Database worksheet. I've tried and tried to modify the code to do this, but have been unsuccessful being that I'm an amateur at this.

2) The "Finished" button in Invoices is what triggers the Finished_Click macro. This basically adds a record to the database, line by line, each time it's selected. Now, I'd like to have 5 ComboBoxes in the invoices sheet, because obviously more than one Component may be ordered. So, instead of having 5 "Finished" buttons to add individual records to Database, I'd like there to be one "Finished" button that ONLY sends the records for whatever Components are ordered. The only way I know how to get the information for each Component into Database right now is to copy a range from Invoices, and paste the whole range. So, I'd potentially be pasting empty records into Database if only 3 Components are ordered. How do I get around this?

I'd appreciate any help with this. Thanks.

Bubbis Thedog

Re: PasteSpecial to Different Sheets, etc...

Thanks for the reply, Shades.

Actually, that's what I attempted --specifying what sheet the data is to be pasted into. One thing that I tried was adding Sheets("Database").Select after both lines that contain Selection.Copy, and that did not work.

I thought that you may be able to add the to-be-pasted-into sheet (Database in my case) in the PasteSpecial line. I just don't know where to put it, and how to put it in there.

Thanks again,

Bubbis Thedog

Re: PasteSpecial to Different Sheets, etc...

Thanks for the advice, shades. I cut the code from the Worksheet module, and pasted it into a new Module 1, then pasted your code into the Worksheet module. What do I change "MyOtherCode" to?

I got my code almost working, by the way. It seems as if there's something wrong directly following the first copy and paste that's causing the name in the combobox to disappear. This, in turn, turns formulas depending on that name to #N/A, and the #N/As are what's getting pasted into my Database worksheet. Here's my code if you, or anyone else, can find out what I'm doing wrong. I've also attached the revised file.

Private Sub Worksheet_Activate()
     
    Application.ScreenUpdating = False
    
    Dim cbCVals
    Dim i As Integer
    
    cbCVals = Array("Axle", "Wheel")
    
    cbComponents.Clear
    cbComponents2.Clear
    
    For i = 0 To 1
        cbComponents.AddItem cbCVals(i)
        cbComponents2.AddItem cbCVals(i)
    Next i


    Application.ScreenUpdating = True


End Sub


Private Sub Finished_Click()
    
    Application.ScreenUpdating = False
    
    Sheets("Invoice").Range("B1:B2").Copy
    
    Sheets("Database").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial _
    Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    
    Sheets("Invoice").Activate
    
    Range("C8:F8").Copy
    
    Sheets("Database").Range("A65536").End(xlUp).Offset(0, 2).PasteSpecial _
    Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
    Application.ScreenUpdating = True


End Sub

Thanks for any assistance!

Bubbis Thedog