Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Repeat copy for indifinate number of worksheets
Hi all! I need some code that will copy a range (incl formatting etc.) of sheet 1, to the exact same range of another workbook's sheet 1. This copy process must repeat for all worksheets in both workbooks even when some sheets are deleted or added in the future. I'm not sure if it will work using the 'For / Next' function. I have attached the example of the code i have so far. The heavily indented mid section is where I need the 'Repeat copy' code. Any help would help! 115: +-------------------------------------------------------------------+ |Filename: Example.txt | |Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=115| +-------------------------------------------------------------------+ -- ARbitOUR ------------------------------------------------------------------------ ARbitOUR's Profile: http://www.thecodecage.com/forumz/member.php?userid=254 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=91316 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Repeat copy for indifinate number of worksheets
Sorry, not a member of that site, so I can't see your attachment and code.
But perhaps this code will get you going. You need to be working with workbook, worksheet and range objects and it becomes pretty easy and you can use the For Each ... Next loop structure to deal with things. General code looks like this Sub ExactCopyBetweenBooks() 'copies specified range from each sheet 'in sourceWB to same range in 'a 2nd workbook (destWB) 'on a worksheet with the same name as in sourceWB Dim destWB As Workbook Dim destWS As Worksheet Dim sourceWS As Worksheet Dim sourceRange As Range 'you need to know the name of the other workbook, 'or code up a way to find what it is 'so you can Set destWB to the other workbook Set destWB = Workbooks("DestinationWorkbook.xls") 'simple example, copies the same range 'from all worksheets in this workbook 'to sheets with same name in the other workbook For Each sourceWS In ThisWorkbook.Worksheets Set sourceRange = sourceWS.Range("A1:Z99") For Each destWS In destWB.Worksheets If destWS.Name = sourceWS.Name Then sourceRange.Copy destWS.Range("A1").PasteSpecial xlPasteAll Exit For ' done with this page End If Next ' keep looking for sheet name match Next ' look at next sheet in this workbook 'cleanup and housekeeping Set sourceRange = Nothing Set destWB = Nothing End Sub "ARbitOUR" wrote: Hi all! I need some code that will copy a range (incl formatting etc.) of sheet 1, to the exact same range of another workbook's sheet 1. This copy process must repeat for all worksheets in both workbooks even when some sheets are deleted or added in the future. I'm not sure if it will work using the 'For / Next' function. I have attached the example of the code i have so far. The heavily indented mid section is where I need the 'Repeat copy' code. Any help would help! 115: +-------------------------------------------------------------------+ |Filename: Example.txt | |Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=115| +-------------------------------------------------------------------+ -- ARbitOUR ------------------------------------------------------------------------ ARbitOUR's Profile: http://www.thecodecage.com/forumz/member.php?userid=254 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=91316 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Repeat copy for indifinate number of worksheets
Thanx Latham! With a bit of tweaking it worked a treat :) -- ARbitOUR ------------------------------------------------------------------------ ARbitOUR's Profile: http://www.thecodecage.com/forumz/member.php?userid=254 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=91316 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Repeat copy for indifinate number of worksheets
Thanks for letting us all know that it worked reasonably well, glad I was
able to help a little. "JLatham" wrote: Sorry, not a member of that site, so I can't see your attachment and code. But perhaps this code will get you going. You need to be working with workbook, worksheet and range objects and it becomes pretty easy and you can use the For Each ... Next loop structure to deal with things. General code looks like this Sub ExactCopyBetweenBooks() 'copies specified range from each sheet 'in sourceWB to same range in 'a 2nd workbook (destWB) 'on a worksheet with the same name as in sourceWB Dim destWB As Workbook Dim destWS As Worksheet Dim sourceWS As Worksheet Dim sourceRange As Range 'you need to know the name of the other workbook, 'or code up a way to find what it is 'so you can Set destWB to the other workbook Set destWB = Workbooks("DestinationWorkbook.xls") 'simple example, copies the same range 'from all worksheets in this workbook 'to sheets with same name in the other workbook For Each sourceWS In ThisWorkbook.Worksheets Set sourceRange = sourceWS.Range("A1:Z99") For Each destWS In destWB.Worksheets If destWS.Name = sourceWS.Name Then sourceRange.Copy destWS.Range("A1").PasteSpecial xlPasteAll Exit For ' done with this page End If Next ' keep looking for sheet name match Next ' look at next sheet in this workbook 'cleanup and housekeeping Set sourceRange = Nothing Set destWB = Nothing End Sub "ARbitOUR" wrote: Hi all! I need some code that will copy a range (incl formatting etc.) of sheet 1, to the exact same range of another workbook's sheet 1. This copy process must repeat for all worksheets in both workbooks even when some sheets are deleted or added in the future. I'm not sure if it will work using the 'For / Next' function. I have attached the example of the code i have so far. The heavily indented mid section is where I need the 'Repeat copy' code. Any help would help! 115: +-------------------------------------------------------------------+ |Filename: Example.txt | |Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=115| +-------------------------------------------------------------------+ -- ARbitOUR ------------------------------------------------------------------------ ARbitOUR's Profile: http://www.thecodecage.com/forumz/member.php?userid=254 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=91316 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Indicating Rows to Repeat At Top on all Worksheets Simultaneously | Excel Worksheet Functions | |||
Repeat printing rows in multiple worksheets | Excel Discussion (Misc queries) | |||
How can i repeat certain rows in all worksheets in an excel file | Excel Discussion (Misc queries) | |||
How do I set up a column to repeat on other worksheets in Excel | Excel Discussion (Misc queries) | |||
Modify a Macro to Repeat for all Selected Worksheets | Excel Worksheet Functions |