+ Reply to Thread
Results 1 to 4 of 4

Copying specific cell ranges from a worksheet multiple times to a new sheet

Hybrid View

  1. #1
    Registered User
    Join Date
    11-09-2009
    Location
    Missouri
    MS-Off Ver
    Excel 2007
    Posts
    4

    Question Copying specific cell ranges from a worksheet multiple times to a new sheet

    Here is what I have:

    I have a workbook with multiple worksheets. Each worksheet corresponds to a certain store fixture estimate. Ont these sheets I have a specific cell where you can input how many fixtures of that type are to be used.

    On that sheet also, is a range of cells (ex. Range("A65:F3340")) that needs to be copied to a new summations sheet of total hours to build the project.

    If sheet 1 has 1 fixture - the macro should copy the range of cells only once.
    Sheet 2 has 4 fixtures - tha macro should copy the same range four times appending each set of data tot eh end of the previous, And so on for each fixture sheet.

    How would I write a macro to do this? I can provide a sample workbook if needed. Thanks.
    Last edited by kaz8772; 11-10-2009 at 03:57 PM.

  2. #2
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Copying specific cell ranges from a worksheet multiple times to a new sheet

    A sample would be good. Include a sample of your source worksheets (your various fixture worksheets) and what the new new summations worksheet will look like after the procedure is run (your before and after).
    John
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  3. #3
    Registered User
    Join Date
    11-09-2009
    Location
    Missouri
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Copying specific cell ranges from a worksheet multiple times to a new sheet

    Ok - I tried uploading but it kept failing. I think it has to do with my laptop and my wireless connection. I sent you a PM

    Now how it works is each tab is a seperate fixture. Presently if there is more than one fixture of the same type it has to be saved as multiple tabs.

    Don't move the tabs because the tabs must stay in their present position so that the Est Summary Total page adds the pages together correctly. You can unhide the hidden tabs by Ctrl+Shft+V and you must rehide them to do the export of the fixture data to the Transfer_Sheet. To do the export Ctrl-Shft-P and that will start it. It takes a while for the program to complete. It will ask you to do a print preview (hit yes) then when the preview comes up hit escape and it will begin.

    Once the data has been exported to the Tractivity_Input tab the macro then copies the data to the Transfer_Sheet using copy & paste special.

    You will see that there are several tabs that are duplicated - these are the sections of data that I need to duplicate. The actual per tab data that is exported starts on each fixture tab at C137 (the hours).

    Any help would be good. But this workbook is pretty confusing. Even people in my office can't follow it.

    The idea would be to eliminate the multiple tabs for the same fixtures and to have the/a macro copy the data the multiple times. They number of fixtures would be controlled on each tab at the top left (C22)

  4. #4
    Registered User
    Join Date
    11-09-2009
    Location
    Missouri
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Copying specific cell ranges from a worksheet multiple times to a new sheet

    I have figured it out. Thanks for your time Jaslake!:biggrin:

    Solved here: http://www.mrexcel.com/forum/showthread.php?p=2117260

    Sub Summarize_Copy_And_Exclude()
    
    Dim a As Long, b As Long, d As Long
    
        For a = 1 To Sheets.Count ' Sets up sheet count
            If Worksheets(a).Name <> "Summation" Then ' excludes sheet Summation
                If Worksheets(a).Name <> ("x1") Then ' excludes sheet x1
                If Worksheets(a).Name <> ("x2") Then ' excludes sheet x2
                        For b = 1 To Worksheets(a).Cells(1, 6)
                        Worksheets(a).Range("A1:C4").Copy
                        d = Sheets("summation").Range("A65536").End(xlUp).Row + 1
                        Sheets("summation").Range("A" & d).PasteSpecial
                        Next b
                 End If
                 End If
            End If
        Next a
    MsgBox "complete"
    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