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

**kaz8772** · 11-09-2009, 05:04 PM

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.

**jaslake** · 11-09-2009, 09:34 PM

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

**kaz8772** · 11-09-2009, 10:13 PM

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)

**kaz8772** · 11-10-2009, 03:54 PM

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