CODEWITHSUNDEEP
excelvba
blabla
blabla

Reputation: 13

Using loop for copying several sheets into several different sheets in same workbook

I have this code. Can I copy all the sheets using one single code using a loop?

Sub clearLKP_68()
Sheet7.Cells.Clear
End Sub


Sub copyFromRel68ToLKP_68()
Sheets("Rel 6.8").Columns("A:R").copy Sheets("LKP_68").Range("A1")
End Sub

Sub clearLKP_69()
Sheet8.Cells.Clear
End Sub


Sub copyFromRel69ToLKP_69()
Sheets("Rel 6.9").Columns("A:R").copy Sheets("LKP_69").Range("A1")
End Sub

Sub clearLKP_70()
Sheet9.Cells.Clear
End Sub


Sub copyFromRel70ToLKP_70()
Sheets("Rel 7.0").Columns("A:R").copy Sheets("LKP_70").Range("A1")
End Sub

Upvotes: 1

Views: 48

Answers (1)

eirikdaude
eirikdaude

Reputation: 3255

Does this work for you? Feel free to ask if there's anything that's unclear.

Option Explicit

Sub test()
  Dim separator As String
  Dim i As Long

  separator = Application.DecimalSeparator
  Application.DecimalSeparator = "."
  For i = 68 To 70
    Worksheets("LKP_" & CStr(i)).Cells.Clear
    Worksheets("Rel " & Format(i / 10#, "0.0")).Columns("A:R").Copy Destination:=Worksheets("LKP_" & CStr(i)).Range("A1")
  Next i
  Application.DecimalSeparator = separator
End Sub

Upvotes: 1

Related Questions