Write to second sheet if first sheet is full

[davedave24]

I have a workbook template that has 25 available rows where data from a userform is inserted. I use the following code to find the next empty row to write the data:

     myWorkbook.Worksheets("Sheet1").Range("B32").Select
     Do Until ActiveCell.Value = ""
        ActiveCell.Offset(1, 0).Select
     Loop

The layout has already been copied to 10 sheets (2 sheets will be enough but just in case). If the loop reaches row 57, instead of writing the data, it needs to move to the next sheet, and begin there. Obviously, sheet 2 may already have data in it, so another loop starting again at B32 finds the next blank line on sheet 2 to write the data. And so on, if it gets to row 57, it should move to sheet3, loop to the next empty line, write data, unless it gets to row 57, then it goes to sheet 4, and so forth.

I'm experimenting with ActiveCell.Row > 56 to try to force it to the next sheet, with little success. I don't want to have 10 nested If statements checking the row number for each sheet and moving to the next, so does anyone have a good way to do this with minimal code?

TIA!

 

[SkipVought]

hi,

Your posted code improved...

     myWorkbook.Worksheets("Sheet1").Range("B32").End(xldown).offset(1).select

I rarely use the Select method and would recommend a cell reference instead.

So please describe the source sheet.

It appears that the target sheets have data that starts in B32. Yes?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[davedave24]

Hi Skip.

Yeah the first line of data goes in row 32 (columns B to N), all the way down to row 56. Row 58 and a few below that contain totals and stuff like that, so the data goes from 32>56, then onto the next sheet. It's essentially so it prints out as a nice form.

The issue I appear to be having is that on sheet2, it will not print the data starting at column B. 90% of the time, it writes the data starting at column N, or R, or S, or something completely random like that. It does this with your line of code and the loop I was using.

I've got it to write from B>N twice; the rest of the time it starts at a different letter for no reason (it finds the next empty row just fine). Writing to sheet1 works fine.

I'll post my test code. At the moment I'm just trying to force it to write to sheet2 if sheet1 is full.

myWorkbook.Worksheets("Sheet1").Range("B32").Select
            Do Until ActiveCell.Value = ""
                ActiveCell.Offset(1, 0).Select
            Loop
            
            If ActiveCell.Row > 56 Then
             myWorkbook.Worksheets("Sheet2").Range("B32").End(xlDown).Offset(1).Select
                
                PasteDataWeeklyDeliveryReport   'this just puts the data in the cells
                myWorkbook.Save
                myWorkbook.Close
            ElseIf ActiveCell.Row < 56 Then
            
            
                'write the data to the workbook
                PasteDataWeeklyDeliveryReport
                myWorkbook.Save                     'save
                myWorkbook.Close                    'close
            End If
[code]