Advanced Search

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

/ Published in: Visual Basic
Save to your folder(s)
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)

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. Sub CopyRange()
  2.  
  3.     Dim oRange As Range
  4.     Dim startColumn As String
  5.     Dim endColumn As String
  6.     Dim rangeStart As Integer
  7.     Dim rangeEnd As Integer
  8.     Dim myBook As Workbook
  9.     Dim sh As Worksheet
  10.     'Dim ws1 As Worksheet
  11.     Dim Wksht As Range
  12.     Dim rangeT As Range
  13.  
  14.     'Application.ScreenUpdating = False
  15.  
  16.     wk = Sheets("pres").Range("e5")
  17.     startColumn = "D"
  18.     endColumn = "R"
  19.     rangeStart = wk + 2
  20.     rangeEnd = wk + 3
  21.     rangeEnd = 28
  22.  
  23.     Set wb1 = Workbooks("Weekperformance 2012.xlsm")
  24.  
  25.     'Set ws1 = wb1.Sheets("Mapping")
  26.  
  27.     Set rangeT = Sheets("Mapping").Range("A54:A84")
  28.  
  29.  
  30.  
  31.     For Each sh In wb1.Worksheets
  32.         If LCase(Right(sh.Name, 4)) = "data" Then
  33.  
  34.  
  35.         Set oRange = sh.Range(startColumn & rangeStart & ":" & endColumn & rangeStart)
  36.          oRange.Copy
  37.         oRange.Offset(1, 0).PasteSpecial xlPasteAll
  38.  
  39.         End If
  40.  
  41.       Next sh
  42.  
  43.  
  44.  
  45.  
  46. End Sub

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.