How can i simplify my code using LOOPS in VBA

Question

I need to copy contents from a column in one worksheet and paste it into another (at the same time making a total of 7 copies of each of the variable). There are multiple columns and each column has different no of variables. Unfortunately due to the size of the data my current code is not feasible. I have a feeling it can be written through a loop.

My logic was that if Excel can detect a text in a column then it should copy the cell under it and paste it in a range (thus creating 7 copies). Hope this makes sense. Any help would be greatly appreciated!

This is my code:

Sub SimpleCopy()

If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("E2")) = True Then
Worksheets("Investor Data").range("E3").copy Worksheets("Data Base").range("C114:C120")
End If

If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("E2")) = True Then
Worksheets("Investor Data").range("E4").copy Worksheets("Data Base").range("C121:C127")
End If

If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("E2")) = True Then
Worksheets("Investor Data").range("E5").copy Worksheets("Data Base").range("C128:C134")
End If

If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("E2")) = True Then
Worksheets("Investor Data").range("E6").copy Worksheets("Data Base").range("C135:C141")
End If

If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("E2")) = True Then
Worksheets("Investor Data").range("E7").copy Worksheets("Data Base").range("C142:C148")
End If

If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("E2")) = True Then
Worksheets("Investor Data").range("E8").copy Worksheets("Data Base").range("C149:C155")
End If

If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("E2")) = True Then
Worksheets("Investor Data").range("E9").copy Worksheets("Data Base").range("C156:C162")
End If

If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("E2")) = True Then
Worksheets("Investor Data").range("E10").copy Worksheets("Data Base").range("C163:C169")
End If

If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("E2")) = True Then
Worksheets("Investor Data").range("E11").copy Worksheets("Data Base").range("C170:C176")
End If

If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("E2")) = True Then
Worksheets("Investor Data").range("E12").copy Worksheets("Data Base").range("C177:C183")
End If

If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("E2")) = True Then
Worksheets("Investor Data").range("E13").copy Worksheets("Data Base").range("C184:C190")
End If

If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("E2")) = True Then
Worksheets("Investor Data").range("E14").copy Worksheets("Data Base").range("C191:C197")
End If

If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("E2")) = True Then
Worksheets("Investor Data").range("E15").copy Worksheets("Data Base").range("C198:C204")
End If

If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("F2")) = True Then
Worksheets("Investor Data").range("F2").copy Worksheets("Data Base").range("B205")
End If

If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("F2")) = True Then
Worksheets("Investor Data").range("F3").copy Worksheets("Data Base").range("C205:C211")
End If

If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("F2")) = True Then
Worksheets("Investor Data").range("F4").copy Worksheets("Data Base").range("C212:C218")
End If

If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("F2")) = True Then
Worksheets("Investor Data").range("F5").copy Worksheets("Data Base").range("C219:C225")
End If

If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("G2")) = True Then
Worksheets("Investor Data").range("G2").copy Worksheets("Data Base").range("B226")
End If

If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("G2")) = True Then
Worksheets("Investor Data").range("G3").copy Worksheets("Data Base").range("C226:C232")
End If

If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("G2")) = True Then
Worksheets("Investor Data").range("G4").copy Worksheets("Data Base").range("C233:C239")
End If

If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("G2")) = True Then
Worksheets("Investor Data").range("G5").copy Worksheets("Data Base").range("C240:C246")
End If

If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("G2")) = True Then
Worksheets("Investor Data").range("G6").copy Worksheets("Data Base").range("C247:C253")
End If

If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("H2")) = True Then
Worksheets("Investor Data").range("H2").copy Worksheets("Data Base").range("B254")
End If

If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("H2")) = True Then
Worksheets("Investor Data").range("H3").copy Worksheets("Data Base").range("C254:C260")
End If

If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("H2")) = True Then
Worksheets("Investor Data").range("H4").copy Worksheets("Data Base").range("C261:C267")
End If

If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("H2")) = True Then
Worksheets("Investor Data").range("H5").copy Worksheets("Data Base").range("C268:C274")
End If

If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("H2")) = True Then
Worksheets("Investor Data").range("H6").copy Worksheets("Data Base").range("C275:C281")
End If

If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("H2")) = True Then
Worksheets("Investor Data").range("H7").copy Worksheets("Data Base").range("C282:C288")
End If

If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("H2")) = True Then
Worksheets("Investor Data").range("H8").copy Worksheets("Data Base").range("C289:C295")
End If

If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("H2")) = True Then
Worksheets("Investor Data").range("H9").copy Worksheets("Data Base").range("C296:C302")
End If

If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("H2")) = True Then
Worksheets("Investor Data").range("H10").copy Worksheets("Data Base").range("C303:C309")
End If

If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("H2")) = True Then
Worksheets("Investor Data").range("H11").copy Worksheets("Data Base").range("C310:C316")
End If

If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("H2")) = True Then
Worksheets("Investor Data").range("H12").copy Worksheets("Data Base").range("C317:C323")
End If

If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("H2")) = True Then
Worksheets("Investor Data").range("H13").copy Worksheets("Data Base").range("C324:C330")
End If

If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("I2")) = True Then
Worksheets("Investor Data").range("I2").copy Worksheets("Data Base").range("B331")
End If

If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("I2")) = True Then
Worksheets("Investor Data").range("I3").copy Worksheets("Data Base").range("C331:C337")
End If

If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("I2")) = True Then
Worksheets("Investor Data").range("I4").copy Worksheets("Data Base").range("C338:C344")
End If

If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("I2")) = True Then
Worksheets("Investor Data").range("I5").copy Worksheets("Data Base").range("C345:C351")
End If

If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("J2")) = True Then
Worksheets("Investor Data").range("J2").copy Worksheets("Data Base").range("B352")
End If

If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("J2")) = True Then
Worksheets("Investor Data").range("J3").copy Worksheets("Data Base").range("C352:C358")
End If

If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("J2")) = True Then
Worksheets("Investor Data").range("J4").copy Worksheets("Data Base").range("C359:C365")
End If

If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("J2")) = True Then
Worksheets("Investor Data").range("J5").copy Worksheets("Data Base").range("C366:C372")
End If

If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("J2")) = True Then
Worksheets("Investor Data").range("J6").copy Worksheets("Data Base").range("C373:C379")
End If

If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("J2")) = True Then
Worksheets("Investor Data").range("J7").copy Worksheets("Data Base").range("C380:C386")
End If

If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("J2")) = True Then
Worksheets("Investor Data").range("J8").copy Worksheets("Data Base").range("C387:C393")
End If

If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("K2")) = True Then
Worksheets("Investor Data").range("K2").copy Worksheets("Data Base").range("B394")
End If

If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("K2")) = True Then
Worksheets("Investor Data").range("K3").copy Worksheets("Data Base").range("C394:C400")
End If


If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("K2")) = True Then
Worksheets("Investor Data").range("K4").copy Worksheets("Data Base").range("C401:C407")
End If


If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("K2")) = True Then
Worksheets("Investor Data").range("K5").copy Worksheets("Data Base").range("C408:C414")
End If


If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("K2")) = True Then
Worksheets("Investor Data").range("K6").copy Worksheets("Data Base").range("C415:C421")
End If


If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("K2")) = True Then
Worksheets("Investor Data").range("K7").copy Worksheets("Data Base").range("C422:C428")
End If


If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("K2")) = True Then
Worksheets("Investor Data").range("K8").copy Worksheets("Data Base").range("C429:C435")
End If


If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("K2")) = True Then
Worksheets("Investor Data").range("K9").copy Worksheets("Data Base").range("C436:C442")
End If


If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("K2")) = True Then
Worksheets("Investor Data").range("K10").copy Worksheets("Data Base").range("C443:C449")
End If


If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("K2")) = True Then
Worksheets("Investor Data").range("K11").copy Worksheets("Data Base").range("C450:C456")
End If


If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("L2")) = True Then
Worksheets("Investor Data").range("L2").copy Worksheets("Data Base").range("B457")
End If


If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("K2")) = True Then
Worksheets("Investor Data").range("L3").copy Worksheets("Data Base").range("C457:C463")
End If


If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("K2")) = True Then
Worksheets("Investor Data").range("L4").copy Worksheets("Data Base").range("C464:C470")
End If


If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("K2")) = True Then
Worksheets("Investor Data").range("L5").copy Worksheets("Data Base").range("C471:C477")
End If


If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("K2")) = True Then
Worksheets("Investor Data").range("L6").copy Worksheets("Data Base").range("C478:C484")
End If


If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("K2")) = True Then
Worksheets("Investor Data").range("L7").copy Worksheets("Data Base").range("C485:C491")
End If

End Sub

Answer 1

First of all, welcome.
Secondly, the first thing you could easily avoid, is running the same if over and over.

So instead of checking If Application.WorksheetFunction.IsText(Worksheets("Investor Data").range("E2")) 13 times, and doing one thing each time, you could just check it once, and do all 13 things.

Then, of course, comes the part you are asking about, looping.
There are a lot of places where you can read up on how to loop thing in different ways, but as long as we have a pattern, we can do a loop.

So for the first part, E2, you could have a loop like this;

If Application.WorksheetFunction.IsText(Worksheets("Investor Data").Range("E2")) = True Then
j = 114
For i = 3 To 15
    Worksheets("Investor Data").Range("E" & i).copy Worksheets("Data Base").Range("C" & j & ":C" & j + 6)
    j = j + 7
Next i
End If

This will go through E3-E15 dictated by the for loop, by replacing the row number with the variable used. (Side note, shouldn't you copy E2 too?) There are probably some even better way of doing it, but that is an example of a simple loop.

Since the amount of rows in each column differ, we can't just slap this in a bigger loop and call it a day.

You could do a few smaller loops, but I leave that up to you for now. Good luck.

edit

One comment is talking about functions, which would be one way to do the same thing a lot of times, but with lot less code.

Sub callCopy()

Dim startRow As Long
startRow = 114
Call copySub(startRow, "E", 15)
Call copySub(startRow, "F", 5)
Call copySub(startRow, "G", 6)
Call copySub(startRow, "H", 13)
Call copySub(startRow, "I", 5)
Call copySub(startRow, "J", 8)
Call copySub(startRow, "K", 11)
Call copySub(startRow, "L", 7)

End Sub

This first sub calls the second private sub. This could've been a function, but those are used when you want to return something. Instead we use a private sub:

Private Sub copySub(startRow As Long, iCol As String, iRows As Long)
Dim i As Long
If Application.WorksheetFunction.IsText(Worksheets("Investor Data").Range(iCol & "2")) = True Then
For i = 2 To iRows
    Worksheets("Investor Data").Range(iCol & i).copy Worksheets("Data Base").Range("C" & startRow & ":C" & startRow + 6)
    startRow = startRow + 7
Next i
End If

End Sub

The second sub is just my first example, but with more variables put in. These are then set in the parentheses when calling the sub.
I replaced the fixed numbers for the column C in the database with a variable, so calculating would be easier. But you can just change it to a fixed number when calling if that suits your needs better.