Using variables to populate sheet and cell references in another sheet

Hi All,

I was wondering if someone can help me, I have a work sheet that contains forms as well as a number of calculating sheets. The calculating sheets exist as one for every financial yeah. So the macro I have created has to reference the starting sheet. This is fine, no problems there. But what I am trying to do is when I run the marco, I want the line in the active sheet (known as WS) to populate a line in one of the forms based on an if ststement (yes I tried next rows and it didn't work for me, so I went this path).

So initially I tried this..

Sub CreateRenewalForm12()

Range("G12").Select

Dim ActiveSht As String
WS = ActiveSheet.Name
Dim CurrentRow As Integer
CurrentRow = ActiveCell.Row
Dim Product As String
Product = "G" & CurrentRow
Dim ExpiryDate As String
ExpiryDate = "H" & CurrentRow
Dim PreviousYear As String
PreviousYear = "M" & CurrentRow
Dim CPI As String
CPI = "Q" & CurrentRow
Dim ProRata As String
ProRata = "P" & CurrentRow

If Sheets("Customer Renewal Form").Range("A42") = "" Then
Sheets("Customer Renewal Form").Select
Range("A42") = Product
Range("C42") = ExpiryDate
Range("D42") = PreviousYear
Range("E42") = CPI
Range("G42") = ProRata
Range("A42").Select


ElseIf Sheets("Customer Renewal Form").Range("A43") = "" Then
Sheets("Customer Renewal Form").Select
Range("A43") = Product
Range("C43") = ExpiryDate
Range("D43") = PreviousYear
Range("E43") = CPI
Range("G43") = ProRata
Range("A43").Select

This worked as in it populated the right cells, but brought in the values of G12 rather than making it a formula to bring in the value of whatever was in the worksheet WS cell G12.

So then I tried this..

Sub CreateRenewalForm12()

Range("G12").Select

Dim ActiveSht As String
WS = ActiveSheet.Name
Dim CurrentRow As Integer
CurrentRow = ActiveCell.Row
Dim Product As String
Product = "=" & WS & "G" & CurrentRow
Dim ExpiryDate As String
ExpiryDate = "=" & WS & "H" & CurrentRow
Dim PreviousYear As String
PreviousYear = "=" & WS & "M" & CurrentRow
Dim CPI As String
CPI = "=" & WS & "Q" & CurrentRow
Dim ProRata As String
ProRata = "=" & WS & "P" & CurrentRow

If Sheets("Customer Renewal Form").Range("A42") = "" Then
Sheets("Customer Renewal Form").Select
Range("A42") = Product
Range("C42") = ExpiryDate
Range("D42") = PreviousYear
Range("E42") = CPI
Range("G42") = ProRata
Range("A42").Select


ElseIf Sheets("Customer Renewal Form").Range("A43") = "" Then
Sheets("Customer Renewal Form").Select
Range("A43") = Product
Range("C43") = ExpiryDate
Range("D43") = PreviousYear
Range("E43") = CPI
Range("G43") = ProRata
Range("A43").Select

And this wont work at all....

So I guess I really need help the populating my variables with something that when placed into the cell on the form will turn into a formula.

Can someone please help me..

Thanks in advance.

always the way... I [play with this for 2 days, and as soon as I [post I get it to work.... Now I tried creating the whole text string before but it didn't like it... mustn't of been doing something right before.

Sub CreateRenewalForm12()

Range("G12").Select

Dim ActiveSht As String
WS = ActiveSheet.Name
Dim CurrentRow As Integer
CurrentRow = ActiveCell.Row
Dim Product As String
Product = "=" & "'" & WS & "'!" & "F" & CurrentRow
Dim ExpiryDate As String
ExpiryDate = "=" & "'" & WS & "'!" & "H" & CurrentRow
Dim PreviousYear As String
PreviousYear = "=" & "'" & WS & "'!" & "M" & CurrentRow
Dim CPI As String
CPI = "=" & "'" & WS & "'!" & "Q" & CurrentRow
Dim ProRata As String
ProRata = "=" & "'" & WS & "'!" & "P" & CurrentRow

If Sheets("Customer Renewal Form").Range("A42") = "" Then
Sheets("Customer Renewal Form").Select
Range("A42") = Product
Range("C42") = ExpiryDate
Range("D42") = PreviousYear
Range("E42") = CPI
Range("G42") = ProRata
Range("A42").Select