Advanced Search

Copy a range of an excelsheet one row down for every sheet that contains data

/ Published in: Visual Basic
Save to your folder(s)
Copy a range of an excelsheet one row down for every sheet that contains data.
This is usefull when you have to add every week new data to a sheet and you want to keep the values of the previous week.

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.  
  11.  
  12.     Application.ScreenUpdating = False
  13.  
  14.     wk = Sheets("pres").Range("e5")
  15.     startColumn = "D"
  16.     endColumn = "R"
  17.     rangeStart = wk + 2
  18.     rangeEnd = wk + 3
  19.  
  20.  
  21.     Set wb1 = Workbooks("Weekperformance 2013.xlsm")
  22.  
  23.  
  24.     For Each sh In wb1.Worksheets
  25.         If LCase(Right(sh.Name, 4)) = "data" Then
  26.  
  27.  
  28.         Set oRange = sh.Range(startColumn & rangeStart & ":" & endColumn & rangeStart)
  29.          oRange.Copy
  30.          oRange.Offset(1, 0).PasteSpecial xlPasteAll
  31.          oRange.Offset(0, 0).PasteSpecial Paste:=xlValues
  32.  
  33.         End If
  34.  
  35.       Next sh
  36.  
  37. Application.ScreenUpdating = True
  38.  
  39.  
  40. MsgBox ("Week " & wk + 1 & " is ready , you can start with adding data to the new week")
  41.  
  42.  
  43.  
  44. End Sub

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.