Copying and Pasting Cells from Multiple Worksheets

Goods

New Member
Joined
Feb 28, 2002
Messages
3
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top