Increment Cell n-times

**yangmuffins** · 02-01-2013, 11:19 PM

Hi, I'm very new to excel programming. I have a very limited background in C++ too and no background in VBA at all. I don't even know if my descriptive title is correct. But anyway, I just want to know if it would be possible to increment a cell index so that after each loop of the function, the active cell selected is exactly n-rows away from the previous one.

Say, I have a macro that 'extracts' data from 30 worksheets and pastes these 'extracted' data into a single worksheet. After each 'run' in each of the 30 worksheets, the macro pastes the data into the destination worksheet, just below the cells where it has already pasted the data from the other worksheets. My current macro is lengthy and ugly because I copied and pasted the same function 30 times, so that I can change the activecell index 30 times. Am I making any sense?

I'm trying to shorten code by calling the same function 30 times, but with the activecell already incremented after each 'run' so that the next pasted data will be in place. Any help?

**HaHoBe** · 02-02-2013, 01:49 AM

Hi, yangmuffins,

VBA has loops which could do the job:

Sub sample()
Dim ws As Worksheet
Dim wsTarg As Worksheet
Dim lngFirstFree As Long

Set wsTarg = Sheets("Summary")
For Each ws In ThisWorkbook.Worksheets
  If Not ws.Name = wsTarg.Name Then
    lngFirstFree = wsTarg.Cells(Rows.Count, "A").End(xlUp).Row + 1
    ws.UsedRange.Copy wsTarg.Cells(lngFirstFree, "A")
  End If
Next ws
Set wsTarg = Nothing
End Sub

This code will go through all the worksheets in the workbook with the code. If the name of the worksheet doesn´t match "Summary" (alter to suit) then the first empty row on Summary is put into the variable lngFirstFree for the copying.

Ciao,
Holger