Sub CopyRange()

    Dim oRange As Range
    Dim startColumn As String
    Dim endColumn As String
    Dim rangeStart As Integer
    Dim rangeEnd As Integer
    Dim myBook As Workbook
    Dim sh As Worksheet
    'Dim ws1 As Worksheet
    Dim Wksht As Range
    Dim rangeT As Range

    'Application.ScreenUpdating = False
    
    wk = Sheets("pres").Range("e5")
    startColumn = "D"
    endColumn = "R"
    rangeStart = wk + 2
    rangeEnd = wk + 3
    rangeEnd = 28
    
    Set wb1 = Workbooks("Weekperformance 2012.xlsm")

    'Set ws1 = wb1.Sheets("Mapping")
    
    Set rangeT = Sheets("Mapping").Range("A54:A84")
    

    
    For Each sh In wb1.Worksheets
        If LCase(Right(sh.Name, 4)) = "data" Then
    
       
        Set oRange = sh.Range(startColumn & rangeStart & ":" & endColumn & rangeStart)
         oRange.Copy
        oRange.Offset(1, 0).PasteSpecial xlPasteAll
            
        End If
            
      Next sh




End Sub

This code copies every variable line of a specified sheet to the next line of another the specified sheet. This code is usefull if you want to add a lot of different xls sheets in one sheet. After that you can use a pivottable to analyze the data. (this instead of opening every sheet separate and analyze it)

Copy every variable line to next line on every \"data\" sheet

Visual Basic