Results 1 to 3 of 3

Thread: VBA Code Help

  1. #1
    drjabear
    Guest

    VBA Code Help

    Hi,
    Below is code that I have been working on in Excel 2000. I have been using the record macro function but it is not doing exactly what I need it to do. I was hoping someone could help. I would like line 8 to be able to go to the first blank cell that it can find and then copy the given selection. I know I need to remove Line 9, but how can I make line 8 tell the macro to go to the first blank cell. Currently it is going to the last non blank cell. Please review the following code:

    '
    ' Run_Reports Macro
    ' Macro recorded 1/31/2001 by jbearden
    '

    '
    Sheets("Source Calc").Select
    Range("B2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    ActiveSheet.Previous.Select
    Range("G2").Select
    Selection.End(xlToRight).Select
    Range("V2").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    End Sub

  2. #2
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Code Help

    I think what you need is something along the lines of the following. I have taken the outrageous liberty of tidying the code up a bit, but I was hoping that might show you how to tidy recorded macros. Essentially what I've done is anywhere where you have two lines where the first one selects a range and the second one works on the selection, I've combined them. What you were looking for to move to the next blank cell was to go to the end, and then use the offset method to jump one cell to the right (look up offset in the help).

    Jon


    Sheets("Source Calc").Select

    Range(Range("B2"), Range("B2").End(xlToRight).End(xlDown)).Copy

    ActiveSheet.Previous.Select

    Range("G2").End(xlToRight).Offset(0, 1).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False

  3. #3
    drjabear
    Guest

    Re: VBA Code Help

    John,
    Thanks for the help, it works like a beauty!! Additionally, I would like to learn VBA. What is the best route if I have to teach myself? Again thanks for the help.

    Jason

Posting Permissions

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