VBA copy blocks to other sheet, transpose result, repeat.

[JohnTheMiner]

OK folks, it's Friday night and I have not cracked it.

I have multiple horrible spreadsheets each with 10+ blocks of data. Each block consists of ten columns (Representing rooms) and 50 rows. The rows contain 'Groups of data', each up to 5 rows long, but not all filled out...so there are blanks (Names and addresses etc). It's not conducive to any form of analysis. Getting the data for a block into a "Database" sheet is the objective, so each data field has its own column, and I will wind up with 5 columns and 500 rows per 'Data block"
Plan is to take a block of 5 (in the first column), copy. Take to "Database" sheet, transpose and paste.
Loop back, take next block of 5 going down. Repeat 10 times.
Increment column counter by 1.
Repeat taking blocks of 5 going down.
Repeat until column counter =10.

Simples.

Code I am using here: Its grinding to a halt on "Compile Error, Next without For" pointing to the "Next i" at the bottom. Yet I think I have a 'For'...apologies for not getting it further, but I only do this suff once every few years....should be slicker..

Any advice or pointers appreciated....

Sub test()
Dim lastRangeRow As Integer 'This is last row in "Database"to append pasted data
Dim lastDataRow As Integer 'The lastrow of a particular big data block: 50x10
Dim i As Integer 'Counter to count down the rows in blocks of 5
Dim RoomCol As Integer 'Counter to indicate which column I am taking data from.
Dim noofRooms As Integer 'This defines the number of columns in the data block, most of them 10
Dim CopyRange As Range ' Range to be copied,1 column by 5 rows
Dim StartCell As Range 'Cell at top of range to be copied
Dim wsFrom As Worksheet 'Worksheet the data is coming from
Dim wsTo As Worksheet 'Worksheet the data is going to




For RoomCol = 4 To 14 'Column "D" has first column of data

i = 26 'manually correct for differet datablocks in different places in sheets (They are all over the place)
lastDataRow = i + 50 'Data blocks are50 rows deep

For i = 26 To lastDataRow Step 5 'start row for block, jumping down in steps of 5. First big data block starts in Row 26.

Do While i <= lastDataRow 'until you reach the last row, keep transposing the data

Set wsFrom = Worksheets("week1")
Set wsTo = Worksheets("Database")

Set StartCell = wsFrom.Range(i, RoomCol) 'This should get my cell to (26,4), start of my first data block

wsFrom.Range(StartCell, StartCell.Offset(0, 5)).Copy 'Take a range going down 5 cells, copy.

lastRangeRow = ws2.Cells(ws2.Rows.Count, 6).End(xlUp).Row - 1 'Go to database sheet,up to last empty row in column 6 (F)
ws2.Range("F" & lastRangeRow).PasteSpecial Transpose:=True 'Into column F, up to last row position, paste transpose.

Next i ' Increment i (Rown numbers)
Next RoomCol 'Increment Column number

End Sub

 

[JLGWhiz]

The For has a Next but the Do does not have a Loop.

Do While i <= lastDataRow Step 5
    'stuff to do
Loop

but why the two For i and a Do loop. Wouldn't one or the other do the job?

 

[JohnTheMiner]

Agree...ive been cobbling bits a pieces together, so its bit of a Frankenstein. Have removed the 'Do' loop, also removed the For..To for the RoomCol...let me get one bit working then I can expand.

Code reduced to this, however while it compiles OK , its saying "Runtime Error 9, subscript out of Range" This would indicate that I don't have a 'Week1' sheet, however I think I do:

Any pointers?

 

[JLGWhiz]

Be sure your sheet tab name does not have leading or trailing spaces. Also, if it is Week 1 instead of Week1. It has to be exact and in the same workbook as the code, since you are not specifying the parent workbook. VBA assumes any object without a specified parent object refers to the Active parent.

 

[JohnTheMiner]

JLG, thanks for stepping in to help, appreciated, work got a bit hectic, solution found (I had written the code in the wrong workbooks module...!). Thanks again.

 

[JLGWhiz]

JLG, thanks for stepping in to help, appreciated, work got a bit hectic, solution found (I had written the code in the wrong workbooks module...!). Thanks again.

You're welcome,
regards, JLG