Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Copying and Pasting Cells from Multiple Worksheets

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    New Jersey
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a file that has 75 tabs or sheets. I need to copy a range of cells from each of them every (same range every sheet) week and paste it all into MiniTab for statistical analysis. I would like to select all sheets (group) and then select the range of cells I want to copy and do a mass paste into MiniTab. I get the message "The information cannot be pasted because the Copy area and the paste area are not the same size and shape" I tried every different way I can think of to do it and I am stuck. I hope one of you very talented people can help me out.

    Thanks in advance
    Joe

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am also interested in the solution to this. The only way I can do this is to open the clipboard toolbar, go to each individual sheet and copy each range individually. Then use the paste all function from the clipboard and it's all on one page, albeit it may not look the way I want it to AND I am limited to only 12 individual copies/ranges since that is the limit of the clipboard.

  3. #3
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You can do it via VBA
    But a bit more info needed

    eg Range address,pastes to another book
    or tab / sheet within the workbook

    Post if required

    Ivan

  4. #4
    New Member
    Join Date
    Feb 2002
    Location
    New Jersey
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Ivan,

    I am glad you can help. The Range is A172:Z269 on 75 different tabs for example one being called Tom2-18 and I would like if all data could be pasted into either a new workbook or a Summary tab in the same workbook which ever is easier.

    Thanks
    Joe

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I got this from MudFace:

    On 2002-03-12 12:28, Mudface wrote:
    Hi, Cosmos, you could try the following. Insert a worksheet called Summary first, and change the CellsToCopy range to suit you. As for the other question, it's probably best if you post that up on the board, as I don't have enough time to got through it at the mo. Regards, Chris.

    Public Sub CopyandPaste()

    Dim ws As Worksheet
    Dim CellsToCopy As String

    CellsToCopy = "A1:D10"

    Worksheets("Summary").UsedRange.Delete

    For Each ws In Worksheets
    If ws.Name <> "Summary" Then
    ws.Range(CellsToCopy).Copy _
    Destination:=Worksheets("Summary").Range("A65536").End(xlUp)
    End If
    Next ws

    End Sub
    I hope this is what you wanted. I did not create this, I give all credt to MudFace! Just wanted to share the info!

    [ This Message was edited by: Cosmos75 on 2002-03-13 12:59 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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