Macro that will change multiple cell references to one cell below on another sheet

[wangs930]

Hi I have been trying to work through this macro based on other posts I have found but I am very new to this and couldn't make it work. It think this should be very easy but I just can not figure it out.

I have to generate several hundred invoices by hand, and I have the fields that I need (name, email, amount owed, etc...) on the invoice template (Sheet2) pulling from the data sheet on "Student_Information" (Sheet1) to populate the invoice template. I need a macro that will move each of these fields down by one cell each time the macro is on the data sheet ("Student_Information") to reflect the appropriate information so that I can generate a PDF invoice.

In my invoice template (Sheet 2) here are the formulas and the cells they are referencing. The data begins on row 3 on Sheet1 and goes until about row 210.
1- E4 - =Student_Information!B3
2- A11 - =Student_Information!C3
3- A12 - ="UNID: "&Student_Information!B3
4- A13 - =Student_Information!H3
5- A14 - =Student_Information!I3
6- E18 - =-Student_Information!K3
7- E19 - =-Student_Information!N3

I recorded a macro that will change from the first student on the list to the second (code below), but after I create that invoice I need it to then move to the third, fourth, fifth... student once the invoice is generated. Currently this is what I have:

Sub changeformulas()
'
' changeformulas Macro
'
'
Range("E4").Select
ActiveCell.FormulaR1C1 = "=Student_Information!RC[-3]"
Range("A11").Select
ActiveCell.FormulaR1C1 = "=Student_Information!R[-7]C[2]"
Range("A12").Select
ActiveCell.FormulaR1C1 = "=""UNID: ""&Student_Information!R[-8]C[1]"
Range("A13").Select
ActiveCell.FormulaR1C1 = "=Student_Information!R[-9]C[7]"
Range("A14").Select
ActiveCell.FormulaR1C1 = "=Student_Information!R[-10]C[8]"
Range("E18").Select
ActiveCell.FormulaR1C1 = "=-Student_Information!R[-14]C[6]"
Range("E19").Select
ActiveCell.FormulaR1C1 = "=-Student_Information!R[-15]C[9]"
Range("E20").Select
End Sub

Thank you for your help in advance!!!!!

 

[Trebor76]

Hi wangs930,

Welcome to MrExcel!!

First of all well done with having a single template where the data is populated to instead of trying to have separate tabs as I so often incorrectly is the case

See how this code goes:

Option Explicit
Sub Macro1()

    Dim lngMyRow   As Long
    Dim lngLastRow As Long
    Dim wsSource   As Worksheet
    Dim wsOutput   As Worksheet
    
    Application.ScreenUpdating = False
    
    Set wsSource = Sheets("Student_Information") 'Sheet name containing the data. Change to suit if necessary.
    Set wsOutput = Sheets("Sheet2") 'Sheet name for invoice. Change to suit if necessary.
    
    lngLastRow = wsSource.Range("B:N").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    For lngMyRow = 3 To lngLastRow
        With wsOutput
            .Range("E4").Value = wsSource.Range("B" & lngMyRow)
            .Range("A11").Value = wsSource.Range("C" & lngMyRow)
            .Range("A12").Value = "UNID: " & wsSource.Range("B" & lngMyRow)
            .Range("A13").Value = wsSource.Range("H" & lngMyRow)
            .Range("A14").Value = wsSource.Range("I" & lngMyRow)
            .Range("E18").Value = wsSource.Range("K" & lngMyRow)
            .Range("E19").Value = wsSource.Range("N" & lngMyRow)
            'Code here to produce invoice
        End With
    Next lngMyRow
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[wangs930]

Robert,

Thank you for the efforts man you are a stud!

So I set it up but it will only change the values on time. They all go from row 3 to 4 but then if I run it again they will not continue onto to row 5.

Any thoughts?

Also for anyone else having similar problems that can't get someone to write their code for them like I am I found an OK work around using OFFSET. I have inserted an OFFSET argument into each formula and have it referencing the a cell for the Row. As I change that cell from 1, to 2, to 3, etc... it moves the formulas down each row.

 

[Trebor76]

I'm not sure what you mean as the code loops through each row from Row 3 to the last row found across columns B to N of the wsSource tab???

 

[wangs930]

So I cleared out all my other macro codes and started from scratch and it worked! I must have messed something up when I copied the code.

Thanks again man!!

 

[Trebor76]

You're welcome