Copy range A:7 - Z200 from all sheets and paste into summary sheet

**cheesiepoof05** · 12-06-2011, 03:53 PM

Hi all,

This is my first post and I don't think it is too hard of a question but I need a macro to copy the range A:7-X200 of every worksheet within a workbook (Excel 2007) and paste it into a Summary Sheet. Also, could you show me in the macro anything I would need to change if the range changed unless it is simply change anywhere you see the range in the macro?

Thanks!

**tigeravatar** · 12-06-2011, 03:59 PM

cheesiepoof05,

Welcome to the forum!
Here's some code to get you started. In your title you use A7:Z200 but in the post you state A7:X200. The code uses A7:X200, change as needed. the wsDestNm is the worksheet Destination Name, so it should be the name of the Summary Sheet.

Please Login or Register  to view this content.

**arthurbr** · 12-06-2011, 04:03 PM

There's a useful add-in at RonDeBruin's site.

Code is also available I think

**cheesiepoof05** · 12-06-2011, 04:55 PM

Thanks for the code Tiger. I am getting an error though and when I debug it is highlighting the section "ws.Range(strRange).Copy Sheets(wsDestNm).Cells(Rows.Count, "A").End(xlUp).Offset(1)". Do you know why by chance? Thanks

Originally Posted by tigeravatar

cheesiepoof05,

Welcome to the forum!
Here's some code to get you started. In your title you use A7:Z200 but in the post you state A7:X200. The code uses A7:X200, change as needed. the wsDestNm is the worksheet Destination Name, so it should be the name of the Summary Sheet.

Please Login or Register  to view this content.

**tigeravatar** · 12-06-2011, 05:09 PM

You'll get an error there if you don't have a worksheet named "Summary". Change wsDestNm as needed.

**cheesiepoof05** · 12-06-2011, 05:45 PM

Tiger,

I misread your post and titled my summary worksheet wsDestNm. Once I got that fixed it worked like a charm. A million thanks, you're the man!

Copy range A:7 - Z200 from all sheets and paste into summary sheet

LinkBack

Thread Tools

Rate This Thread

Display

Copy range A:7 - Z200 from all sheets and paste into summary sheet

Re: Copy range A:7 - Z200 from all sheets and paste into summary sheet

Re: Copy range A:7 - Z200 from all sheets and paste into summary sheet

Re: Copy range A:7 - Z200 from all sheets and paste into summary sheet

Re: Copy range A:7 - Z200 from all sheets and paste into summary sheet

Re: Copy range A:7 - Z200 from all sheets and paste into summary sheet

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions