automatically copy and paste a predefined range of cells

**Dewy** · 11-08-2010, 12:32 PM

Hi, im basically trying to figure out if there is a way to automatically copy and paste a predefined range of cells however many times they input.

This is what I have so far. <see attached sheet>. As you can see on sheet1 I have x amount of sections. On sheet 2, under all of my data I have a place for my calculations and formulas for each section. Id like to know how or if there is a way to predefine a section, have someone input in a cell how many sections they want, and then have excel automatically copy and paste the predefined section however many times they inputted.

**rylo** · 11-08-2010, 09:54 PM

Hi

1) Will the formulas on sheet2 already exist for the max number of sections to be carried across, or do the formulas have to be created for all the sections.

2) Would it be possible to only have 1 formula calc section on sheet2, and just put in the K value for the relevant section, and bring back the results to sheet1?

3) Will the sections in sheet1 be regularly spaced or do you have to find them (currently the don't have regular spacing, but that may be an issue with the example data).

rylo

**Dewy** · 11-10-2010, 10:49 AM

Think i solved it

Sub auto_run()
    Dim a As Variant
    Dim b As Variant
    Dim s As Variant
    'setup variables
    a = 1
    s = 7
    'set values to variables
    
    b = Application.InputBox(prompt:="Insert number of K values to test", Type:=1)
    'box to ask for number of K values to test
    
    Sheets("Calculations").Select
    Rows("27:" & Rows.Count).Delete Shift:=xlUp
    'deletes any rows below first K value (used mainly as a reset)
    
    Sheets("K Table").Select
    Rows("47:" & Rows.Count).Delete Shift:=xlUp
    'deletes any rows below first K value (used mainly as a reset)
    
    If b = a Or b > a Then
    'start loop
           
        Do Until b = a
            Sheets("Calculations").Select
            'switch to first sheet
            Range("A24:L26").Select
            Selection.Copy
            'selects table to copy and copies it to clipboard
            ActiveCell.Offset(s, 0).Select
            ActiveSheet.Paste
            'makes cell jump down 's' amount of rows and pastes table (s initially at 7)
            Sheets("K Table").Select
            'switches to second sheet
            Application.CutCopyMode = False
            Range("A41:R46").Select
            Selection.Copy
            'selects calculations to copy and copies it to clipboard
            ActiveCell.Offset(s, 0).Select
            ActiveSheet.Paste
            'makes cell jump down 's' amount of rows and pastes table (s initially at 7)
            a = a + 1
            s = s + 7
            'updates loop counter
            Loop
            'ends copy and paste part of loop on both sheets
                    
        Else
            MsgBox "error!"
            'displays if loop contains error
            
        End If
        'ends loop
        
        Sheets("Calculations").Select
        'switches to first sheet for easy viewing

End Sub