range/loop/new workbook problem

[bradgar]

All in VBA.

I have a user form set up to automate data entry. I sucessfully made it so that column A populates according to column B according to a loop i specified. And column B populates according to a number they enter in userform. It auto populates according to a range resize code.

However, the problem is that I need it to stop at row 101 for both columns A and B and start a new workbook coninuing from where the previous one left off..

any suggestions?

 

[texasalynn]

post the code that you have

 

[bradgar]

Private Sub oKbutton_Click()
Dim msg As Integer
Dim nxt As String
Dim nB As Integer
msg = Range("A1").End(xlDown).Value
mDe = Range("B1").End(xlDown).Value
nB = nmbr.Value
mD = moDu.Value
nme = ActiveSheet.Range("A2").Value
 
 
counter = 0
Do
    Range("B" & Rows.Count).End(xlUp).Offset(1).Value = mD
    Range("A" & Rows.Count).End(xlUp).Offset(1).Value = msg + 1
    msg = msg + 1
    counter = counter + 1
Loop Until counter = nB
 ' problems start here (below is my thought on resolution)
If Cells(101, 2) <> "" Then
    With ActiveSheet
        Range("A102:B152").Cut
        Workbooks.Add.Activate
        Range("A2:B52").Select
        ActiveWorkbook.SaveAs nme
    End With
End If
If Cells(101, 1) <> "" Then
    nxt = msg + 1
    nme1 = nme - 100
    Workbooks(nme1).Activate
    With ActiveSheet
        Range("A1:H1").Copy
        Workbooks(nme).Activate
        Range("A1").Select
        Selection.PasteSpecial Paste:=xlValues
        Selection.PasteSpecial Paste:=xlFormats
        Range("A2").Select
    End With
End If
 
 
MsgBox " last cell is:" & msg
End Sub

I have changed a bit since the beginning now, both column A & B are included in the same loop which works better..
Cant' seem to find what i need to do with this..

 

[bradgar]

Took all day but I solved this on my own! I honestly reccommend this macro for anyone who wants to automate entry in two rows (maybe more with some tweaking)!

Private Sub oKbutton_Click()
Dim msg As Integer
Dim nxt As String
Dim nB As Integer
'get value of last cell in column A
msg = Range("A1").End(xlDown).Value
'nB is input from user from as number of entrys
nB = nmbr.Value
'mD is entry from listbox in userform
mD = moDu.Value

'keep track of # of loops
counter = 0
'loop to fill according to user input
Do
    If Range("A101") <> "" Then GoTo endR  'this carries to new book based on 100 entries
    Range("B" & Rows.Count).End(xlUp).Offset(1).Value = mD 'this keeps putting new values into column B as listbox choice (all the same for # nB input) 
    Range("A" & Rows.Count).End(xlUp).Offset(1).Value = msg + 1 'takes last value of column A then adds one and inserts below     
msg = msg + 1 'a number to keep track of mD's sequetially (ie. 1, 2, 3 ,4 ect)

    counter = counter + 1 'adds to keep track of loops
Loop Until counter = nB 'loop until # of loops = user input #
 
'this whole section makes a new workbook when first book reaches 100 entries, copies formats, clears entries, then continues where the old loop left off until # of loops = user input #
endR:
    With Application
        Range("A1:H101").Copy
        Workbooks.Add
        Selection.PasteSpecial Paste:=xlValues
        Selection.PasteSpecial Paste:=xlFormats
        Range("A2:H101").ClearContents
        counter = counter
        Do
            Range("B" & Rows.Count).End(xlUp).Offset(1).Value = mD
            Range("A" & Rows.Count).End(xlUp).Offset(1).Value = msg + 1
            msg = msg + 1
            counter = counter + 1
        Loop Until counter = nB + 1
        ActiveWorkbook.SaveAs Range("A2")
    End With
    MsgBox "end of page, new book started"
    Exit Sub
MsgBox " last cell is:" & msg

End Sub

pretty interesting huh, great way to keep track of inventory items and such

 

[texasalynn]

Good job - it feels good to solve them doesn't it. I do have a question about this

Range("A1:H101").Copy
......
Range("A2:H101").ClearContents

why copy then clear, it would be more efficient to only copy row 1 and eliminate the clear

 

[bradgar]

well i was taking the formating and putting to new workbook and clearing for values. Everyother line is a different color, white, blue, white, blue. So instead of trying to code that into VBA I just .copy then .clearcontents to save formating but no values. Ulitmately, I ran into problems when I started trying to move the macro and userform to new workbook so I abandoned this method.

Instead I convinced my boss to let me hold the info on different sheets in one workbook as it is only a hundred entries per sheet. This has simplified the code a bit for me.

But yes it does feel very good to solve these problems on your own. Now I am just having trouble with the darn thing starting the new sheet for no reason. I think is has to do with the line:

If Range("A101") <> "" Then GoTo endR

is there a better way test for this type of information?