Error in report data to 2 worksheets



I had asked a question yesterday on how to duplicate informaiton within a Macro, it worked halfway (refering to the addition of CopyToPage command). I do have a command of ClearContents within my function, so it runs the macro, sends the data to "data" worksheet clears the input and re-runs sending data to "Fin. Affairs".

Below is what I'm working with, my end goal is to gather data from my named cells, and have it report to 2 worksheets.

Sub Florida_Student_Services()

CopyToPage "Data"

CopyToPage "Fin. Affairs"

End Sub

Function CopyToPage(ws_output As String)

next_row = Sheets(ws_output).Range("A" & Rows.Count).End(xlUp).Offset(1).Row

Sheets(ws_output).Cells(next_row, 1).Value = Range("campus").Value

Sheets(ws_output).Cells(next_row, 2).Value = Range("date_paid").Value

Sheets(ws_output).Cells(next_row, 3).Value = Range("type").Value

Sheets(ws_output).Cells(next_row, 4).Value = Range("amount").Value

Sheets(ws_output).Cells(next_row, 5).Value = Range("payee").Value

Sheets(ws_output).Cells(next_row, 6).Value = Range("payment_for").Value

Sheets(ws_output).Cells(next_row, 7).Value = Range("GL_Number").Value

Sheets(ws_output).Cells(next_row, 8).Value = Range("Notes").Value

MsgBox ("Submitted")

Range("C15", "C25").ClearContents

End Function



Morgans You haven't attached a file so we can't know which cells are involved in your input/named ranges nor which cells you're clearing. Or indeed what " worked halfway..." means or refers to (your new macro?),

Yesterday I pointed you to the Add Link button but in this case please edit your original question to add explanation and use the Add Files button to attach an Excel file. It's late evening for me so you may not get a fast response from me.
John_Ru (rep: 1267) Apr 28, '21 at 4:31 pm
Add to Discussion




I think your problem is the ClearContents line. If you don't want C15 and C25 to be copied to the Fin.Service sheet, change that line to:

Range("C15, C25").ClearContents

i.e the cells' A1 references inside the inverted commas (quote marks) since your line clears everything between C15 and C25,

Also I suggest you move the line MsgBox("Submitted") out of the function and into the sub. Or leave it there and change it to say what happened in the function e.g.

MsgBox "Inputs submitted to worksheet " & ws_output

Hope this fixes your issue.



The entry tab, once all is entered the user is to hit "submit" the message pops up to say "submitted" that way they don't continue to submit the same data. Which is why I put ClearContents after the message box.
The issue is once submit is hit, the data ONLY moves to "data" tab not both "data" and "fin. affairs". I do beleive it is the placement or wording of ClearContents.
To fix that issue, I just removed it from the original macro and created a "clear" button.
Thank you for all your help and quick responces. New to macros, today is actually my 2nd day ever to mess with them.
Morgans (rep: 2) Apr 28, '21 at 5:31 pm

Once they click Submit, the VBA code will run and they're unlikely to have chance to re-submit. The MsgBox actually pauses the running of the code (until Ok is clicked) so you're better moving it to the end of the code in my opinion (likewise for clearing the form). 

In the file you posted, some data DOES move to both tabs but your version of the clearcontents line means that when the function is called the first time, only Campus and Date Paid remain uncleared. When it's called a second time from the Sub, only these two fields get copied to Fin.Services.

Just to be clear, you submit button called the Sub. The first line calls the function (with Data as the destination). Once that code is run, control returns to the sub which calls the function again (with Fin.Services as the destination).

You're doing much better than me on Day 2 (but I wasn't aware of TeachExcel when I started to dabble with VBA).
John_Ru (rep: 1267) Apr 28, '21 at 5:56 pm
Add to Discussion

For your 4th day at VBA here is a totally different approach. You can run the code on your project without modification but if you want to hitch it to your button on the Entry sheet you need to assign the macro there by its name "PostPaymentReceipt", or change this name to what you have assigned to the button.

Sub PostPaymentRceipt()
    ' 229

    Dim Inputs              As Variant          ' array of inputs
    Dim R                   As Long             ' loop counter: input Rows
    Dim Ws                  As Worksheet        ' variable assignments
    Dim i                   As Integer          ' loop counter: worksheets

    ReDim Inputs(1 To Int((25 - 11) / 2) + 1)   ' define size of array
    For R = 11 To 25 Step 2
        ' read the cell values into the array
        Inputs(((R - 11) / 2) + 1) = Sheet1.Cells(R, "C").Value
        If (R = 15) And IsEmpty(Inputs(((R - 11) / 2) + 1)) Then Exit Sub
    Next R

    Set Ws = Worksheets("Data")                 ' tab to write to on first loop
    For i = 1 To 2
        ' last used row in column 1 (=A) from the bottom plus one row
        R = Ws.Cells(Ws.Rows.Count, 1).End(xlUp).Row + 1
        ' make Cells(R, "A") the same size as the array and paste the value
        Ws.Cells(R, 1).Resize(1, UBound(Inputs)).Value = Inputs
        Set Ws = Worksheets("Fin. Affairs")     ' tab to write to on second loop
    Next i

    With Sheet1
        .Range("C15:C25").ClearContents         ' delete the recorded data
        .Cells(15, 3).Select                    'ready to enter next payment
    End With
End Sub

So, what's different in the approach? The macro has 3 distinct sections.

  1. Read the data.
  2. Write the entries
  3. Clear up the mess

It also has a deeper layer of organization: each time something is read or written a worksheet is specified. Observe that the Entry tab is referred to as Sheet1. That is its CodeName which you can see in VBE's Project Explorer and change in the Properties. The point is that the CodeName stays the same even while the user may change the tab's name. For practical purposes you can use Sheet1 or Worksheets("Entry") interchangably. But not specifying the sheet you write to or delete from is generally considered a loose cannon.

Read the data.
Your ouput ranges have 8 fields and are identical in both output sheets (excellent planning!). You need an array of 8 elements to write to them. Your input form also has 8 fields, regularly spaced. So it's just a matter of juggling the numbers 11 (first row), 25 (last row) and 2 (interval) until a simple loop generates the row numbers 11 to 25 in intervals of 2 and puts these numbers next to a count from 1 to 8.
You end up with an array (1 To 8) and each of the input cells' values assigned to one element. Observe that you save the task of declaring named ranges.

Write the entries.
The task is repetetitive. Therefore you employ another loop, twice. On each turn all action is the same but the subject (the worksheet) changes. So, you define one worksheet before the loop is entered and the second one at the end of the first loop so that it will be treated on the second round.
The action itself, within each loop, is just to write the ready array to a particular row. So, the code picks the row, defines the first cell in that row, expands that one cell to include the next 8 cells (same size as the array in case you want to add fields to it in the future), and paste the array to the defined range.

Clear up the mess.
The entered data have been posted. I hate to run to the boss all the time and tell him that I did my job. I treat my code the same way. Therefore I just delete the data expecting the user to see that the job is done - better than  MsgBox "Mission accomplished". An additional encouragement for him to keep track of his wits is to directy his focus to the next entry. So the code sets the cursor for him to C15 which is where he should enter something next.
Now, just in case the sleepy colleague will press the Submit button again nevertheless, I added this line above Next R in the frist (Read the data) section.

If (R = 15) And IsEmpty(Inputs(((R - 11) / 2) + 1)) Then Exit Sub

It simply says that nothing should be done if C15 is empty, and this idea could be expanded to include other essential fields and give a message after he has done something wrong, letting him work peacefully and efficiently, without useless interruption, while he is doing everything right.

Observe that the question of what to delete at what stage doesn't come up in my essay because the structure is so clear that no doubt can persist. This advantage will also stay with this code as it gets developed over time. It will always be simple code that looks complicated, as opposed to your first attempt that looks simple but throws up unexpected complications.


Answer the Question

You must create an account to use the forum. Create an Account or Login