Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copy Range automatically (Excel 2003)

    Hello,

    I am working with some Ado-connections to a sql server which get me all timetracked data from my colleagues.
    My problem is, that i have some calculated columns at the right side for data analysis. Every time, when there are new data (Column A:H, - new data , green),
    i have to copy my range manually (columns J:S, last range entries -orange row, see attached file) till the last entry from A to H. Is there a possibillity to do this every time, I start the Ado
    without manipulating it manually?
    thanks,
    stefan

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy Range automatically (Excel 2003)

    The easiest way is to place the formulas directly next to the data, and to specify "Fill down formulas in columns adjacent to data" in the Options for the query. See screenshot below.
    Otherwise, you'd have to write VBA code.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Range automatically (Excel 2003)

    well, as some other colleagues shall use this Excel without special knowledge which range is to copy....
    how can i do this with code?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy Range automatically (Excel 2003)

    If you don't know the range, it's going to be difficult to write code <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  5. #5
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Range automatically (Excel 2003)

    the range is always the last row entry from column J to column S (as i coulered it orange)
    It should be copied, as often as there are new entries from column A to H.. (like my attached workbook)

    i attached the ado with it...

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy Range automatically (Excel 2003)

    Does this do what you want?

    Sub FillFormulas()
    Dim lngFirst As Long
    Dim lngLast As Long
    lngFirst = Range("J65536").End(xlUp).Row
    lngLast = Range("A65536").End(xlUp).Row
    Range("J" & lngFirst & ":S" & lngLast).FillDown
    End Sub

    You could call this macro at the end of ADO_Import_Daten_aus_Webdesk.

  7. #7
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Range automatically (Excel 2003)

    This code does exactly what i want...
    perfect, many thanks,
    stefan

Posting Permissions

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