0

I have tried various expressions to do [what appears to be] the simple task of copying an excel table between two worksheets. In addition, I need to enclose the expression inside a loop. Here are four expressions (all beginning with "Sheets") I have tried. All of them compile, but then crash upon running:

    p = 6
For i = 1 To NumTables
    'Read "OP LLs" table into "EIRP budget"
    Sheets("EIRP Budget").[B6:L17] = Sheets("OP LLs").Range(Cells(p, 2), Cells(p + 11, 12))
    Sheets("EIRP Budget").[B6:L17] = Sheets("OP LLs").[Cells(p, 2), Cells(p + 11, 12)]
    Sheets("OP LLs").Range(Cells(p, 2), Cells(p + 11, 12)).Copy Sheets("EIRP Budget").[B6]
    Sheets("OP LLs").["B" & p & : & "L" & p + 11].Copy Sheets("EIRP Budget").[B6:L17]
    p = p + 15
Next

Any help would be greatly appreciated.

Community
  • 1
  • 1
jmaz
  • 507
  • 3
  • 8
  • 19

1 Answers1

1

I suspect you are having difficulties because of your unqualified Cells (you're not specifying what worksheet Cells refers to)

Try (not tested):

dim wksCopyFrom as Worksheet
dim wksCopyTo as Worksheet

set wksCopyFrom = Sheets("OP LLs")
set wksCopyTo = Sheets("EIRP Budget")

For i = 1 To NumTables
    p = 6
    'Read "OP LLs" table into "EIRP budget"
    wksCopyFrom.Range(wksCopyFrom.Cells(p,2), wksCopyFrom.Cells(p+11,12)).Copy wksCopyTo.Range("B6")
    p = p + 15
Next
dendarii
  • 2,958
  • 20
  • 15
  • Thank you, this worked. I was not aware of this approach and it has improved my code. One small correction to your syntax: place an additional ")" in front of ".Copy" and the code works as intended. – jmaz Aug 23 '13 at 18:33
  • Thanks Max. I didn't have time to test it - I have corrected the code now. – dendarii Aug 27 '13 at 09:19