Offseting a range

[tomas]

Hello

So there is an important snippet of the code I am working on. So to describe the situation I have master workbook (resultbook in code ) And I loop over all excel files in folder within them loop over all worksheets and including just one with desired names and then within worksheets looping over ranges.


So lets say .

I have Book1 and sheet 1 and within this ranges

("B16:AD28"),("B32:AD44"),("B48:AD60")

Then I paste this range in resultbook in E column.

In B column I would like to have name of workbook
In c column I would like to have a sheet name
In D column to have

for range ("B16:AD28") Level 1
for range (("B32:AD44") Level 2 etc

- the thing is number of rows in one range is always 13, number of ranges will vary but can be found by number of occurences of string "JAN" in column

- anyways I would rather avoid soludion based on math calculations and just have some code which immiediatelly after a range is pasted would offset into columns B,C,D and filled them as I need.

Hopefully it's not confusing

' Loop to include all workbooks
For n = 1 To FNum
           
           Set MyBook = Workbooks.Open(MyPath & MyFiles(n))
'Loop to include all targetted worksheets
            For i = 1 To x
                For Each wsk In MyBook.Worksheets
               
                    If wsk.Name = ResultBook.Worksheets("List1").Range("A1").Offset(i, 0) Then
                        wsk.Select
                        lastrow = Range("B100000").End(xlUp).Row
                        For c = 1 To lastrow
                            If Range("B1").Offset(c, 0) = "Jan" Then
                                Range(Cells(c + 1, 2), Cells(c + 13, 50)).Select
                                Selection.Copy
                                firstrow = ThisWorkbook.Worksheets("List1").Range("E100000").End(xlUp).Row
                           
                                ActiveSheet.Paste Destination:=ResultBook.Worksheets("List1").Cells(firstrow + 1, 5)
                            End If
                        Next c
                           
                       
                       
                    End If
               
                Next wsk
           
            Next i
        ActiveWorkbook.Close
       
   Next n

 

[Marc L]

Hi !

As you can read in VBA inner help, first parameter of Offset is for row,
second is the one for column !
​