Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Jun 2005
    Location
    Singapore
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copy Marco (Office XP)

    Hans and all,

    I have tried writing a marco based on the recorded marco to copy a processed sheet to a template workbook's Product sheet but am clueless of how to modify it. As the rows may change to be more or less for the next working sheet than the current one, I am trying to write a marco which can copy the numbers of rows starting from A2 to the last filled rows, so that the next time when I copy , it won't be confine only to these current rows as recorded.

    I think the part on the Range("A2:F215").Select need to be change but am unsure to how to do it.

    The following marco was recorded :

    Sub copy()
    '
    ' copy Macro
    ' Macro recorded 8/8/2005 by francis
    '

    Range("A2:F215").Select
    Selection.copy

    Workbooks.Open Filename:= _
    "Cocuments and SettingsfrancisMy DocumentsMy First ProgramMFTemp.xls" _
    , UpdateLinks:=0

    Range("A8").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    End Sub

    Thanks Always, kun

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Copy Marco (Office XP)

    kun, look at the CurrentRegion method. Just replace the select line with
    <pre>Selection.CurrentRegion.Select</pre>

    HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Marco (Office XP)

    Is there a cell in the last row that is guaranteed to have something in it? Is column F always the last column? If not, is there a row that is guaranteed to have something in the last column?
    Legare Coleman

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Marco (Office XP)

    That method will not select the desired area if there are any blank rows or columns in the area.
    Legare Coleman

  5. #5
    Star Lounger
    Join Date
    Jun 2005
    Location
    Singapore
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Marco (Office XP)

    Legare,

    Thank you for your prompt response, column F will always be the last column and the last rows will always have a Product Grand Total in column A and its amount in column F.

    Thanks again, kun

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Marco (Office XP)

    Try replacing:

    <code>
    Range("A2:F215").Select
    </code>

    with

    <code>
    Range("A2", Range("A65536").End(xlUp).Offset(0, 5)).Select
    </code>
    Legare Coleman

  7. #7
    Star Lounger
    Join Date
    Jun 2005
    Location
    Singapore
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Marco (Office XP)

    Sam,

    Thank you for assisting, the code doesn't work in my case as there are some blank cells ( columns in between the first column and the last column) which use for sub totals. It paste a blank to cell A8 in the template Product sheet, but I am sure in other situation it will work well. Thank you for pointing out this method and will look into it to learn more about this method.

    cheers,

  8. #8
    Star Lounger
    Join Date
    Jun 2005
    Location
    Singapore
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Marco (Office XP)

    Legare,

    Once again you make my day or ( should I say my nite now) . Thank you for your help. I am wondering why is it that the code use
    Range("A65536").End(xlUp).Offset(0, 5)?

    thanks always, kun

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Marco (Office XP)

    Range("A65536") is the last possible cell in column A. .End(xlup) is a method that searches up from there and finds the first cell that has something in it. This is the last cell with something in it in column A. the .Offset(0,5) then moves over to column F.
    Legare Coleman

  10. #10
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Copy Marco (Office XP)

    Sorry, if you want all of the sheet then a simple code line is <pre>ActiveSheet.UsedRange.Select</pre>

    If you have stuff beyond column F that you don't want to copy, then use <pre>Intersect(ActiveSheet.Columns("A:F"), ActiveSheet.UsedRange).Select</pre>

    That almost makes Legare's code look simple! <img src=/S/yep.gif border=0 alt=yep width=15 height=15>
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Marco (Office XP)

    But, definitely does not do the same thing! <img src=/S/grin.gif border=0 alt=grin width=15 height=15> Particularly since he did not want to copy row 1.
    Legare Coleman

Posting Permissions

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