+ Reply to Thread
Results 1 to 10 of 10

Issues pasting in column B and making 2nd macro available

  1. #1
    Registered User
    Join Date
    02-08-2008
    Posts
    35

    Issues pasting in column B and making 2nd macro available

    I have created an excel macro in VB which searches through a column of values for a given date, it then pastes the row into a second sheet. This works perfectly fine except:-

    1. I encountered problems when I tried to put a link to a macro "NewDeals" in Column A of each line, things got very messy, then paste the copied row starting at B instead of A.
    2. I also tried to set the print area to stop when there are no more values however I think it sets the print area before the macro finishes copying and pasting so it only prints my original text.
    3. I would eventually like to be able to set the date externally without having to enter the macro, I'm guessing this is quite easy so it is less important as I will probably search for an answer once everything else works ok.

    Thanks for any contributions.

    Please Login or Register  to view this content.

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474
    This workbook has quite a few examples of what you speak of..



    http://www.excelforum.com/showthread.php?t=631965
    Check out the codes in the workbook

    What do you mean by putting a link to a macro in a columns?

    as for the print code,
    you have an X variable but don't use it

  3. #3
    Registered User
    Join Date
    02-08-2008
    Posts
    35
    I've probably explained very poorly and I apologise. Thanks for the workbook though is has some useful items.

    The search and paste function works fine, and after some tinkering and actually studying the order of the commands the "set print area" is perfect now as well. So...

    I have already constructed a separate macro called "Newdeals", I would like a button for this added at the start of each row, column A, before I paste in the copied information, which would then start in column B.

  4. #4
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474
    Quote Originally Posted by sungsam
    ......... I would like a button for this added at the start of each row, column A, before I paste in the copied information, which would then start in column B.
    I understand that would be really cool to do......
    there could be other alternatives,
    what would happen when the button got pressed?

  5. #5
    Registered User
    Join Date
    02-08-2008
    Posts
    35
    The macro brings up a form that I've already created, this utilises the information in that row, edits it using criteria set then saves it to a new line in another workbook.

    I know it would be neater to create the form within the macro however I already have a form I have created.

    Is it possible to link a button to a form already created or will I have to create the form anew within the macro?

  6. #6
    Registered User
    Join Date
    02-08-2008
    Posts
    35
    I thought I'd post my macro so far and hopefully that will clarify exactly what I would like to achieve.

    Please Login or Register  to view this content.
    Within the IF I would like to add a button (or something which runs the form when u click on it) in column A of each line which opens up the form "NewDeals" when clicked. At the moment when the macro runs it opens up the form, instead of just adding a link to it.

  7. #7
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474
    Quote Originally Posted by sungsam
    I thought I'd post my macro so far and hopefully that will clarify exactly what I would like to achieve.

    Please Login or Register  to view this content.
    Within the IF I would like to add a button (or something which runs the form when u click on it) in column A of each line which opens up the form "NewDeals" when clicked. At the moment when the macro runs it opens up the form, instead of just adding a link to it.
    Hi,
    You could try the right click event,
    Please Login or Register  to view this content.
    This code goes into the worksheet module
    If you are unsure where that is, then check this site to find out where that is
    http://www.contextures.com/xlvba01.html#Worksheet

  8. #8
    Registered User
    Join Date
    02-08-2008
    Posts
    35
    Thanks very much that seems to be sorted now. I've posted my code below with what the sections do. Only problem I have now is that the print area will select 4000+ pages as my spreadsheet contains " ".

    Please Login or Register  to view this content.
    Thanks again for all the help
    Last edited by sungsam; 02-12-2008 at 11:10 AM.

  9. #9
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474
    Possibly, selecting all the empty columns, and then go to edit clear all, then do the same with the rows, then see how many pages there are to print

  10. #10
    Registered User
    Join Date
    02-08-2008
    Posts
    35
    I don't know why but the spreadsheet still seems to contain approx 60k of rows, even after I selected all of them and did clear all, and then tried to delete the rows.

    Just wanted to say thanks again for the help. My workplace has now decided to swap to Excel 2007. I've already tested the macro on the test machine with Excel 2007 and it got upset over the use of "Format"; seems like I'm going to have to start from stratch for the core of my macro, lame.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1