Copying and Pasting Cells from Multiple Worksheets

[Goods]

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

[Cosmos75]

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.

[Ivan F Moala]

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

[Goods]

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

[Cosmos75]

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 ]