Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Jan 2005
    Location
    Gwent, Wales
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Arrays to copy sheets (Excel 2002)

    Hi,

    I have a workbook (A) which opens another workbook ([img]/forums/images/smilies/cool.gif[/img] selected by the user. I have an array which specified the named sheets that I wish to copy and some code which specifies various ranges of data which will be copied form the sheets i.e. not the whole sheet. I then want to paste this data into Workbook (A) in the relevant sheet and range eg. If the data is copied from the first sheet in workbook ([img]/forums/images/smilies/cool.gif[/img], called TestA, this data needs to be pasted in TestA in Workbook (A). How do I get to select the correct sheet in Workbook (A)? This is part of a For Next loop, so I need it to work through the array and populate Workbook A with the pasted data?

    Any ideas anyone?

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

    Re: Arrays to copy sheets (Excel 2002)

    Selecting workbooks, sheets, and ranges is very inefficient and almost never necessary. It is very difficult to give you code without seeing what you already have. You can probably do something like this to do the paste without selecting:

    <pre> Workbooks("C:MyDataWorkbookA.xls").Worksheets("Tes tA").Paste Destination := Range("A1")
    </pre>


    If you show us your code, we can be more precise.
    Legare Coleman

  3. #3
    Lounger
    Join Date
    Jan 2005
    Location
    Gwent, Wales
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Arrays to copy sheets (Excel 2002)

    Ok here is an exerpt of my code. The first bit works, its the bit when it selects the "MasterModel" that I need help with. It needs to select the same sheet in the MasterModel as it has just copied from the "UserModel" and then move through the loop and so on etc.

    Dim Sh As Worksheet

    MasterModel = ActiveWorkbook.Name

    [Various other code]

    UserModel = ActiveWorkBook.Name

    For Each Sh In Sheets(Array("SheetA", "SheetB", "SheetC", "SheetD", "SheetE", "SheetF"))

    StartRng = Range("A10")
    EndRng = Range("A8") - 1

    FirstRange = "D" & StartRng & ":" & "D" & EndRng
    Range(FirstRange).Select
    Selection.Copy

    Windows(MasterModel).Activate

    [How do I select the same sheet as the array to paste the data in eg, first SheetA, then Sheet B etc]

    Range("D15").Select
    Selection.PasteSpecial xlPasteValues

    Windows(UserModel).Activate
    FirstRange = "I" & StartRng & ":" & "AW" & EndRng
    Range(FirstRange).Select
    Selection.Copy

    Windows(MasterModel).Activate

    [How do I select the same sheet as the array to paste the data in eg, first SheetA, then Sheet B etc]

    Next Sh

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

    Re: Arrays to copy sheets (Excel 2002)

    Try something like the following. Note that MasterModel and UserModel are now Workbook objects instead of strings, and that no ranges are selected.

    Dim Sh As Worksheet
    Dim MasterModel As Workbook
    Dim UserModel As Workbook
    Dim StartRng As String
    Dim EndRng As String
    Dim FirstRange As String

    Set MasterModel = ActiveWorkbook
    ' Various other code
    Set UserModel = ActiveWorkbook

    For Each Sh In MasterModel.Sheets(Array("SheetA", "SheetB", _
    "SheetC", "SheetD", "SheetE", "SheetF"))
    StartRng = Sh.Range("A10")
    EndRng = Sh.Range("A8") - 1
    FirstRange = "D" & StartRng & ":" & "D" & EndRng
    Sh.Range(FirstRange).Copy
    MasterModel.Sheets(Sh.Name).Range("D15").PasteSpec ial xlPasteValues
    Next Sh

Posting Permissions

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