+ Reply to Thread
Results 1 to 4 of 4

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

Hybrid View

  1. #1
    Registered User
    Join Date
    09-06-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    2

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

    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.

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

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

    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
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Registered User
    Join Date
    09-06-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    2

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

    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.
    Attached Files Attached Files

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

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

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1