melgibson2
02-04-2011, 08:50 AM
Hi. I have a workbook with a form layout identical on each sheet (10 sheets). The layout is designed to be printed out, so each sheet/page only holds 25 records, starting at row 32 (B32) down to row 56.
My data goes into the form, but if sheet 1 is full, it needs to go to sheet 2 (again, starting at B32), and write to the next empty row. Similarly, if sheet2 turns out to also be full, then it goes to sheet3, finds the next empty row, writes the data, and so on until it finds a sheet that's not full.
Here's my test code, which for the moment is only concerned with moving to sheet2 if sheet1 is full.
The problem is that if it decides to write to sheet2, the data writes in the wrong place. It will randomly start writing in column N, or S, or something like that; also it's not in the correct row, just a random place. Why is this?
Code:
'find next empty row
myWorkbook.Worksheets("Sheet1").Range("B32").End(xlDown).Offset(1).Select
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 < 57 Then
'write the data to the workbook
PasteDataWeeklyDeliveryReport
myWorkbook.Save 'save
myWorkbook.Close 'close
End If
My data goes into the form, but if sheet 1 is full, it needs to go to sheet 2 (again, starting at B32), and write to the next empty row. Similarly, if sheet2 turns out to also be full, then it goes to sheet3, finds the next empty row, writes the data, and so on until it finds a sheet that's not full.
Here's my test code, which for the moment is only concerned with moving to sheet2 if sheet1 is full.
The problem is that if it decides to write to sheet2, the data writes in the wrong place. It will randomly start writing in column N, or S, or something like that; also it's not in the correct row, just a random place. Why is this?
Code:
'find next empty row
myWorkbook.Worksheets("Sheet1").Range("B32").End(xlDown).Offset(1).Select
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 < 57 Then
'write the data to the workbook
PasteDataWeeklyDeliveryReport
myWorkbook.Save 'save
myWorkbook.Close 'close
End If