Page 1 of 3 123 LastLast
Results 1 to 15 of 35
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: selecting a multi range of columns and rows (excel)

    The line

    Range(wbk.Worksheets("JobChartsData").Range("A28") .Offset(5, 0), wbk.Worksheets("Coil Summary").Range("A28").Offset(5, 12)).Copy

    does not make sense, you're trying to define a range from cells in two different worksheets. Moreover, since the first Range is not preceded by a worksheet reference, it will refer to the current worksheet, so in all there may be three different worksheets involved. You should refer to only one.

  2. #2
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    selecting a multi range of columns and rows (excel)

    If strFile = "" Then
    wsh.Range("C" & i) = "No workbook found"
    Else
    Set wbk = Workbooks.Open(Filename:=sfl.Path & "" & strFile, AddToMRU:=False)
    wsh.Range("B" & i) = wbk.Worksheets("Coil Summary").Range("J9")
    wsh.Range("C" & i) = wbk.Worksheets("Coil Summary").Range("B5")
    wsh.Range("D" & i) = wbk.Worksheets("Coil Summary").Range("J6")
    Range(wbk.Worksheets("JobChartsData").Range("A28") .Offset(5, 0), wbk.Worksheets("Coil Summary").Range("A28").Offset(5, 12)).Copy
    wsh.Paste Destination:=wsh.Range("E1").Offset(i, 12)
    Application.CutCopyMode = False
    wbk.Close SaveChanges:=False
    End If
    Next sfl


    the above snip code selects a cell from a worksheet. On the last statement the cells it selects are not just one but 12 across and 5 rows down. THis code I have created seems not to wanna work. what looks wrong:?
    i have attached a page of info im trying to extract and paste into a seperate workbook

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: selecting a multi range of columns and rows (excel)

    Change your line:
    Range(wbk.Worksheets("JobChartsData").Range("A28") .Offset(5, 0), wbk.Worksheets("Coil Summary").Range("A28").Offset(5, 12)).Copy

    To something like:

    With wbk.Worksheets("JobChartsData")
    .range(.Range("A28").Offset(5, 0), _
    .Range("A28").Offset(5, 12)).Copy
    end with

    so that all ranges refer to the same sheet.
    Steve

  4. #4
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: selecting a multi range of columns and rows (excel)

    the code which will extrac single cells

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

    Re: selecting a multi range of columns and rows (excel)

    In addition to what Hans and Steve said, there is something else in your question that is not clear. Are you trying to copy 12 cells from one row 5 rows down from A28, or are you trying to copy 5 rows by 12 columns and if so which 5 rows.

    You really don't need to use the Offset method in this case, you could just refer to cells A33 and M33.
    Legare Coleman

  6. #6
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: selecting a multi range of columns and rows (excel)

    it seems like its coping but how would i paste it in to column e

  7. #7
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: selecting a multi range of columns and rows (excel)

    I have posted a spread sheet with an example of information that needs to be extracted. I am coping A28 to A33 to L28 to L33

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

    Re: selecting a multi range of columns and rows (excel)

    You copied a row of 12 cells. Do you want to paste them down column E, or in some row starting at column E?
    Legare Coleman

  9. #9
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: selecting a multi range of columns and rows (excel)

    I think you want to paste it into your work sheet with the macro starting at column E??

    well if so under steves code try writing this

    wsh.Paste Destination:=wsh.Range("E1")

  10. #10
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: selecting a multi range of columns and rows (excel)

    yes, I would like to paste the data from A28 to L28 across and an the rows below it to A33 to L28

  11. #11
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: selecting a multi range of columns and rows (excel)

    yes, then paste it into the worksheet

  12. #12
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: selecting a multi range of columns and rows (excel)

    wbk.Worksheets("Coil Summary").range(.Range("A28:L33").Copy)

    I think you need a ) at the end??

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

    Re: selecting a multi range of columns and rows (excel)

    Where do you want to paste it? What workbook? What worksheet? What cell?
    Legare Coleman

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

    Re: selecting a multi range of columns and rows (excel)

    I assume that what you are trying to copy is the range A28:L33. If so, then is this what you want:

    <pre> wbk.Worksheets("Coil Summary").Range("A28:L33").Copy
    </pre>

    Legare Coleman

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

    Re: selecting a multi range of columns and rows (excel)

    Acrually, I had an extra ".range(" in my statement. Thanks, I have corrected the post.
    Legare Coleman

Page 1 of 3 123 LastLast

Posting Permissions

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