to make this code simpler

by

Something like this: 

Sub Macro1()
  Dim Sh1 As WorkSheet, Sh2 As WorkSheet
  Set Sh1 = Sheets("Sheet1")
  Set Sh2 = Sheets("ALB1")

  Dim R As Long
  For R = 2 to 127
    Sh1.Range("D" & R & ":E" & R).Copy Sh2.Range("C" & R - 1)
  Next R
End Sub

Or even better:

Sheets("ALB1").Range("C1:D126") = "=Sheet1!D2"

Assigning a formula that works on the first cell of a range to the whole range is equivalent to typing the formula on the first cell and copying it right and down.

More Related Contents:

  1. Simple VBA code to open cells one after the other
  2. Open folder in new instance
  3. Copy data from cell in selected row
  4. Refer to sheet using codename
  5. VBA Macro On Timer style to run code every set number of seconds, i.e. 120 seconds
  6. Assign code to a button created dynamically
  7. Last not empty cell (column) in the given row; Excel VBA
  8. HTML Text with tags to formatted text in an Excel cell
  9. Split comma separated entries to new rows [closed]
  10. Get list of Excel files in a folder using VBA [duplicate]
  11. Select first empty cell in column F starting from row 1. (without using offset )
  12. Excel VBA – QueryTable AfterRefresh function not being called after Refresh completes
  13. Excel VBA: AutoFill Multiple Cells with Formulas
  14. VBA.Find and adding a value
  15. excel vba- extract text between 2 characters
  16. Round function in Excel, worksheet function vs VBA
  17. How to continue the code on the next line in VBA
  18. Excel VBA Userform – Execute Sub when something changes
  19. VBA: How to change the value of another cell via a function?
  20. Count the number of rows in another sheet [duplicate]
  21. Getting Excel to refresh data on sheet from within VBA
  22. VBA Password Input with Cancel Function
  23. Finding first blank row, then writing to it
  24. Calling regasm without administrative rights for COM interop in Excel VBA
  25. Is there a way for DIR(path) in VBA to handle strings longer than 260?
  26. How do I activate a specific workbook and a specific sheet?
  27. Start VBA macro when editing a cell
  28. VBA Run-time error 438 appears when “paste” runs
  29. If Not function proceeds when value *is* the one specified
  30. Copy an entire worksheet to a new worksheet in Excel 2010

Leave a Comment