Copy a range from multiple worksheets in one workbook to multiple sheets in another workbo

**Vinnty** · 09-06-2012, 12:20 PM

Hi There

I have two workbooks. One is named Volumes (new) 1213.xls the other is named Volumes new (new) 1213 formula file.xls. Both workbooks contain a number of identically named worksheets (ie Total Prod; Total Sales; Total Disp....). Columns headings in Volumes (new) 1213.xls are Column G5 = Product; Column H5-BG5 = week numbers 1 - 52. Column headings in Volumes new (new) 1213 formula file.xls are Column G5 = Product; Column H5 = current week number (variable). Range H6:H1000 in each of the sheets mentioned above in Volumes new (new) 1213 formula file.xls contains sumif formula to get volume detail from another file. Once a week I would like to paste special value the result of the sumif calculations from the Volumes new (new) 1213 formula file.xls to the corresponding worksheet and corresponding week (in range H5:HBG5) in the Volumes (new) 1213.xls workbook.

The reason I want a separate workbook that contain the sumif formula is due to the fact that these formula take an age to calculate. And this happens each time I open the workbook - even I just quickly want to refer to it.

Please can I get help with the code to paste special value the range (H6:H1000) from the multiple worksheets in the formula workbook to the relevant column/week in the multiple worksheets in the other workbook which has no formulas.

Thanks very much.

**xladept** · 09-06-2012, 02:06 PM

This "should" work if the weekly entries are left null until they're posted - else, perhaps you could post examples?

Sub VolForm(): Dim wv As Workbook, wf As Workbook, wsv As Worksheet, wfv As Worksheet
Dim N As String, i As Long
Set wv = Workbooks("Volumes (new) 1213"): Set wf = Workbooks("Volumes (new) formula file")
For Each wsf In wf
N = wsf.Name
Set wsv = wv.Worksheets(N)
'Column H5-BG5 = week numbers 1 - 52.
i = 7: Do: i = i + 1: Loop Until wsv.Cells(5, i) = ""
wsf.Range("H6:H1000").Copy
wsv.Cells(5, i).PasteSpecial xlPasteValues
Next
End Sub

**Vinnty** · 09-07-2012, 05:06 AM

Hi xladept

Thanks for your response. I tried the code but it did not seem to work. I have attached simplified versions of the workbooks. Please note that the workbook containing the formulas has worksheets in it that are not necessarily in the other workbook and vice versa. Therefore in this example I would only like the data in worksheets "Total Prod", "Total Sales" and "Total Disp" copied over.

Thanks for your help.

**xladept** · 09-07-2012, 02:50 PM

Hi Vinnty,

This works now - (I had an awful time getting the book names right):

Sub VolFormX(): Dim wv As Workbook, wf As Workbook, wsf As Worksheet, wsv As Worksheet
Dim N As String, i As Long, S As String
S = ActiveWorkbook.Name: ActiveCell = S
Set wv = Workbooks("Volumes (new) 1213.xls")
Set wf = Workbooks("Volumes (new) 1213 formula file.xls")
For Each wsf In wf.Worksheets
N = wsf.Name
If N = "Week Lookup" Then GoTo GetNext
Set wsv = wv.Worksheets(N)
i = 7: Do: i = i + 1: Loop Until wsv.Cells(6, i) = ""
wsf.Range("H6:H1000").Copy
wsv.Cells(6, i).PasteSpecial xlPasteValues
GetNext: Next
End Sub

Copy a range from multiple worksheets in one workbook to multiple sheets in another workbo

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Copy a range from multiple worksheets in one workbook to multiple sheets in another workbo

Re: Copy a range from multiple worksheets in one workbook to multiple sheets in another wo

Re: Copy a range from multiple worksheets in one workbook to multiple sheets in another wo

Re: Copy a range from multiple worksheets in one workbook to multiple sheets in another wo

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions