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:



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


    You need to include worksheet references when using ranges - otherwise XL thinks everything takes place on same worksheet


    Software: OpenOffice 3.0/NeoOffice 3.0 on Mac OS X 10.5.6
    Humanware: Older than dirt


    Old, slow, and confused - but at least I'm inconsistent!


    Rich
    (retired Excel 2003 user, 3.28.2008)

  • 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

    :silverha:

  • Re: PasteSpecial to Different Sheets, etc...


    Just as a note. I would probably put this code in the Standard module rather than the worksheet module. Then I would just have one line for the worksheet module:


    Code
    Private Sub Finished_Click() 
         Application.Run "MyOtherCode"
    End Sub


    But that is just me.


    Software: OpenOffice 3.0/NeoOffice 3.0 on Mac OS X 10.5.6
    Humanware: Older than dirt


    Old, slow, and confused - but at least I'm inconsistent!


    Rich
    (retired Excel 2003 user, 3.28.2008)

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



    Thanks for any assistance!


    Bubbis Thedog

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!