Advanced Search

Return to Snippet

Revision: 59758
at September 28, 2012 00:25 by Hassito
Initial Code 
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
Initial URL 

                                

                                                            

                                    

                                        

                                        Initial Description
                                        

                                    

                                    
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)

                                

                                                            

                                    

                                        

                                        Initial Title
                                        

                                    

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

                                

                                                            

                                    

                                        

                                        Initial Tags
                                        

                                    

                                    

                                

                                                            

                                    

                                        

                                        Initial Language
                                        

                                    

                                    
Visual Basic