Copy-paste with loop, different ranges under each other

Question

I'm quiet new to vba. I would like to have a code that copies up to 200 different ranges (B4:B48, C4:C48,D4:D48...GT4:GT48) to a cell (B50) and put the different ranges under each other.

So, the first ranges (B4:B48) should be put in cell B50, the second range (C4:C48) should be put in cell B95 etc.

I suppose this code will need a loop, however: not all the 200 ranges are filled, if there is an empty range then that range doesn't need to be copied. Every range can contain a different number of items, although the maximum different number of items is always 42.

Thanks if you can help me further!

I added an example, the coulours are just making it more visual but aren't needed in vba-code.

What have you researched and tried? Where did you get stuck or errors? Please note that because this is no free code writing service it is necessary that you show either what you have tried so far and where you got stuck or errors (by showing your code) or at least to show what you have researched and the effort you made. Otherwise it is just asking us to do all the work for you. Reading [ask] might help you to improve your question. — Pᴇʜ, Feb 14 '20 at 10:53
For someone quiet new to vba, it seems difficult to find a solution for 1) different ranges (from left to right, select & copy with a loop) and 2) past them in a different way (under each other with a loop). Range("A23:R4423").Select Selection.ClearContents Range("A1").Select ' selecteer range met formules en genereer x aantal roosters met formules Dim i As Integer Range("BV23:CM44").Select Selection.Copy For i = 1 To 200 Range("A" & 1 + i * 22).Select ActiveSheet.Paste Next i — kds, Feb 17 '20 at 08:48
Note that code in comments gets pretty easily unreadable. Therefore always [edit] your original question to add code. • I know that starting with VBA can be somehow difficult but please understand that we also cannot write the code for you. So we can only assist you writing the code yourself by telling what you did wrong and how to improve it. I'll give you some ideas how to start it: See my answer below. — Pᴇʜ, Feb 17 '20 at 09:32

Pᴇʜ · Answer 1 · 2020-02-17T09:34:41.700

First you will need to copy each of your range one by one.

If your ranges B4:B48, C4:C48,D4:D48...GT4:GT48 follow a rule like always one column right, then you can use a loop going through all columns. B is column no 2 and GT is column no 202. So you can loop like

Dim iCol As Long
For iCol = 2 To 202
    'your code here
Next iCol

The ranges eg B4:B48 can then be dynamically written as

ws.Range(ws.Cells(4, iCol), ws.Cells(48, iCol))

wich means if iCol = 2 then Cells(4, iCol) is B4 and Cells(48, iCol) is B48 so it represents the range between those 2 cells. In the next iteration Next iCol will increase iCol by one so the same line represents column C now.

Note that in the code above ws represents your worksheet, you should specify that in the beginning so you can use that shortcut ws:

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("YourSheetName")

You will also need to find the last used cell in column B to determine the place where you need to paste your range. A good way to do this is:

Dim LastUsedRow As Long
LastUsedRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

So now your next free row in column B is LastUsedRow + 1 and this is where you should paste.

Give it a try on your own, do some research if you get stuck. I gave you pretty much of what you need. The rest should be really basic stuff you can easily figure out by reading some basic tutorials or google.

Two last things that will prevent you from running into common issues:

I recommend always to activate Option Explicit: In the VBA editor go to Tools › Options › Require Variable Declaration and declare all your variables properly.
You might benefit from reading How to avoid using Select in Excel VBA. Using .Select or .Activate should be avoided at any cost. It slows down everything a lot and your code will run into issues easily soon or later.

thanks for helping me getting started! now i have something to start with and to give it a try! — kds, Feb 17 '20 at 10:20

Copy-paste with loop, different ranges under each other

1 Answers1