Worksheets

[Programmin'Pete]

I have an excel spreadsheet consisting of 2 sheets (Request Form & Request History). I have a button on the first one that when I press it I want it to save all the details on the 2nd one in plain columns but I am having problems referencing both at same time. In My code I have the following:

Please Login or Register  to view this content.

Now when I type SheetTo for example then follow it with the . notation it comes up with the action linked to a worksheet but when I try that with SheetFrom and hit the . it shows nothing and I cant refer to it at all. Can anyone help? I am trying to automate forms used where I work and this is halting my progress.

You will have to excuse filenames for attachments they are back to front, but these are screen shots of what I mean tho.

[Leith Ross]

Hello Programmin'Pete,

To assign an object variable to an object, you need to use the Set statement.

Please Login or Register  to view this content.

[Programmin'Pete]

This is the code I have now based on the changes you suggested, but still when I go to, on line after the set commands and try to use both
SheetTo. or SheetFrom. only one of them comes up with the list box with the fields for a worksheet. The rest of the code was to determine the next unused row for the data to be saved into

Please Login or Register  to view this content.

[shg]

Pete,

Please take a few minutes to read the forum riules, and then edit your post to add code tags.

Thanks.

[Programmin'Pete]

Tried what u suggested and still when I type SheetTo. the drop down box doesnt show.

[Leith Ross]

Hello Programmin'Pete,

Post your workbook and I'll review it.

To make your posts easier to read, copy, and edit please wrap your code.

How to wrap your Code
1. Select all your code using the mouse.
2. Click on the # icon on the toolbar in the Message window. This will automatically wrap the text you selected with the proper Code tags to create a Code Window in your post.

Use the Bulletin Board Code Tags
[code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] at the end of the last line.


To learn more about BB codes used in this forum, just click on the link below...

Bulletin Board Codes Tags

[Programmin'Pete]

Here is the spreadsheet. I have wracked my brain on this and there has got to be something I am missing. just seems weird that out of the two worksheet reference variables one refers to the worksheet and other doesnt.

[Leith Ross]

Hello Programmin'Pete,

You posted an Excel 2007 worksheet. I don't Excel 2007 (see my profile - to the right of my name). You will need to post a copy in Excel 2003 format before I can help you.

[Programmin'Pete]

Sorry about that, new to forum and didnt think to check your profile. Converted it as per excel to "Excel 97-2003" format

[Leith Ross]

Hello Programmin'Pete,

Here is the updated macro. It has been added to the attached workbook.

Please Login or Register  to view this content.

[Leith Ross]

Hello Programmin'Pete,

Here's an update to the macro. I caught an error in the NextRow calculation.

Please Login or Register  to view this content.

[Programmin'Pete]

Thanks for the help, atm being in a call centre that I am using this spreadsheet for I am flat chat. will slot that code in and give it a whirl as soon as i can.

[Programmin'Pete]

Thanks for that code, just had chance to place it into macro and works great. And looking at it a few time and I understand what is going on, alot cleaner code than before......