|
Combining specific ranges from multiple worksheets into one
I need to combine (Copy/ Pastelink ) several worksheets into 1 Summary
sheet but I dont need the first 3 rows or last 4 rows of the worksheet to be copied to the Summary worksheet. except for the first sheet. The sheets are all formatted alike, but each sheet has varying amounts of data. All go from A5: to column U except the first sheeet which will start at A1 I will like to keep the page formatting also. (Shading, colors etc.. ) Headings from the first sheet , Sheet 1 is to be used for Headings of the summary sheet. It does not have to be created in code. I dont want the last 4 rows of any sheet posted to the summary sheet. Sheets 1 - 10 included. Sheet names Sheet 1 - Sheet 10 Any working code samples or help appreciated |
Combining specific ranges from multiple worksheets into one
If you can be sure that no used cells appear below the data on each sheet,
then Sub grabRowsIWant() Dim Sht As Worksheet Dim SumSht As Worksheet Set SumSht = Worksheets("SummarySheet") For Each Sht In ThisWorkbook.Worksheets If Sht.Name < SumSht.Name Then Sht.Range("A4:A" & Sht.UsedRange.Rows.Count - 4).EntireRow.Copy SumSht.Range("A" & SumSht.UsedRange.Rows.Count - 3).Insert Shift:=xlDown End If Next Sht End Sub "simora" wrote in message ... I need to combine (Copy/ Pastelink ) several worksheets into 1 Summary sheet but I dont need the first 3 rows or last 4 rows of the worksheet to be copied to the Summary worksheet. except for the first sheet. The sheets are all formatted alike, but each sheet has varying amounts of data. All go from A5: to column U except the first sheeet which will start at A1 I will like to keep the page formatting also. (Shading, colors etc.. ) Headings from the first sheet , Sheet 1 is to be used for Headings of the summary sheet. It does not have to be created in code. I dont want the last 4 rows of any sheet posted to the summary sheet. Sheets 1 - 10 included. Sheet names Sheet 1 - Sheet 10 Any working code samples or help appreciated |
Combining specific ranges from multiple worksheets into one
I am trying to copy a specific range from 30 worksheets onto a summar sheet, where I can sort the data. I thought this solution might hel (even though I want the summary to be dynamic, but when I tried usin the code, and I get a syntax error at "Shift:=xlDown". Thanks, Daw -- crowleyd ----------------------------------------------------------------------- crowleydl's Profile: http://www.excelforum.com/member.php...fo&userid=2596 View this thread: http://www.excelforum.com/showthread.php?threadid=37511 |
Combining specific ranges from multiple worksheets into one
Dawn,
Record the steps while doing this manually. The recorded code should show you the syntax you need. -- steveB Remove "AYN" from email to respond "crowleydl" wrote in message ... I am trying to copy a specific range from 30 worksheets onto a summary sheet, where I can sort the data. I thought this solution might help (even though I want the summary to be dynamic, but when I tried using the code, and I get a syntax error at "Shift:=xlDown". Thanks, Dawn -- crowleydl ------------------------------------------------------------------------ crowleydl's Profile: http://www.excelforum.com/member.php...o&userid=25968 View this thread: http://www.excelforum.com/showthread...hreadid=375119 |
Combining specific ranges from multiple worksheets into one
Dawn,
Haven't forgotten you... Have to go out and will be back in a few hours. Patience... -- steveB Remove "AYN" from email to respond "crowleydl" wrote in message ... Sub grabRowsIWant() Dim Sht As Worksheet Dim SumSht As Worksheet Set SumSht = Worksheets("Corp Sum") For Each Sht In ThisWorkbook.Worksheets If Sht.Name < SumSht.Name Then Sht.Range("j3:j" & Sht.UsedRange.Rows.Count - 2).EntireRow.Copy SumSht.Range("A" & SumSht.UsedRange.Rows.Count - 3).Insert Shift:=xlDown End If Next Sht End Sub above is the code, and I get an error at "Shift:=xlDown". I am not sure that the code will do what I want, but I have been trying all possible solutions that I find posted here at the forum. I have a vlookups array on 30 worksheets that is pulling specific information about sponsors into the same setup (range J3-M16) on each worksheet. the sponsors are in a list of mixed items at a different part of each worksheet (columns A-E with headers in row 5). this list will be manually added to throughout the year. to keep from doing double work, I am trying to list all of the sponsor information from each sheet on a separate sheet automatically (this will also prevent mistakes). I want to be able to use autofilter on the sponsor sheet, to sort by date, amount, member, alpha, etc., as the need arises. I would be happy to provide you with a copy of the file. Dawn STEVE BELL Wrote: Dawn, Not a problem! But I'll need to see your code and a brief explanation of how you want it to behave. Make sure I understand what is supposed to happen. It helps to use the Reply Group function so that the previous post(s) is included - this makes it easier to see what has been said and tried... So reply back with a copy of your code... -- steveB Remove "AYN" from email to respond "crowleydl" wrote in message ... Thank you for the response, but I am new to macros and do not know how to do it manually. If I understand what it is doing, I can adapt a macro once I have copied it, but I cannot create one. Dawn -- crowleydl ------------------------------------------------------------------------ crowleydl's Profile: http://www.excelforum.com/member.php...o&userid=25968 View this thread: http://www.excelforum.com/showthread...hreadid=375119 -- crowleydl ------------------------------------------------------------------------ crowleydl's Profile: http://www.excelforum.com/member.php...o&userid=25968 View this thread: http://www.excelforum.com/showthread...hreadid=375119 |
| All times are GMT +1. The time now is 10:05 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com