reshape grid for sumif calculations in vba

[DrewsterUK]

Hi all

I have a report that comes to me in several 'grids' in a workbook, and in order to report on it properly I need to make a small reshape.

https://imgur.com/OaX4kdh

These grids appear in one worksheet but have varying row lengths all the way down (separated by one blank row). I need to insert a blank column A, then add the firstname lastname in column F to each row between Job Board and TOTAL. the number of rows between job board and total varies from 1 to 10, but otherwise the structure of the grid doesn't change.

Can anyone help please?

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub DrewsterUK()
   Dim Rng As Range
   Range("A:A").Insert
   For Each Rng In Range("B:B").SpecialCells(xlConstants).Areas
      Rng.Offset(2, -1).Resize(Rng.Count - 3).Value = Rng.Offset(, 5).Resize(1, 1).Value
   Next Rng
End Sub

 

[DrewsterUK]

Hi Fluff

Thanks for the post. It creates the cell in column A but doesn't move the firstname lastname into the relevant cells in Column A between job board and total - i get runtime error 1004; application defined or object-defined error

 

[Fluff]

Will your blocks of data always have at least 4 rows?
Also is there anything above the first block?

 

[DrewsterUK]

to illustrate, this is where I got to so far...

Columns("A:A").Insert Shift:=xlToRight
For Each cell In Columns("G").Cells.SpecialCells(xlCellTypeConstants)

If cell.Value Like "* *" Then cell.Offset(2, -6).Range("A1").Value = cell.Value
cell.Offset(2, -6).Range("A1").Select

Next cell

unfortunately it's a complete mess of a report that gets generated in this horrible format. my code puts the name in the correct first position, but i cant work out how to then autofill down the correct number of rows because that number can vary each time.

the tables start a few rows down after some meaningless summary text and then will always follow the same format along the top row and the left column. The number of rows varies, but there is always

Overview
Job Board
-(optional row)
-(optional row)
-(optional row)
Total

so I guess there's a minimum of 4 rows, but no no minimum of the total number of mini-tables. once you've inserted blank column A, firstname-lastname is always in column G

 

[Fluff]

Will the summary text always be the same number of rows?
If not will it always have data in col A (col B after the insert)?

 

[DrewsterUK]

Will the summary text always be the same number of rows?
If not will it always have data in col A (col B after the insert)?

grids start as you see them on row 3. row 1 contains summary information, row 2 is blank.

the earliest position that 'firstname lastname' COULD appear is G3 (after the column insert).
after the insert, there is always data in column B

after these edits, my sumif formulae reference Column A & Column B

 

[Fluff]

In that case try

Sub DrewsterUK()
   Dim Rng As Range
   Range("A:A").Insert
   For Each Rng In Range("B2:B" & Rows.Count).SpecialCells(xlConstants).Areas
      Rng.Offset(2, -1).Resize(Rng.Count - 3).Value = Rng.Offset(, 5).Resize(1, 1).Value
   Next Rng
End Sub

 

[DrewsterUK]

You are both a gentleman and a scholar - thank you

 

[Fluff]

You're welcome & thanks for the feedback