Macro to hide cells based on contents in previous tab

[claireegloff]

Hi there

I have a spreadsheet where users can enter details of multiple clients and it will pre-populate forms based on the number of clients entered.

I only want 3 forms to be pre-popped if 3 clients are entered, 30 if 30 are and so on.

I want a macro that says if a cell in column A of tab 1 contains data, populate a form on tab 2 for that client.

Is this possible?

 

[Jake Blackmore]

Hello,

To begin with you need a check macro - something like :

Option Explicit
Public Sub foo()
    Dim rngSpare    As Excel.Range
    Set rngSpare = Sheet1.Range("A1")
    If Not rngSpare.Value = "" Then
        Debug.Print rngSpare.Value
    End If
End Sub

If your cell is A1 and it is empty debug.print will run - you can replace this with your next copy paste macro. I suggest recording a macro copying and pasting exactly how you want it to and that will give you the basis for the code you will need to run if A1 or whichever cell it is has something in it.

 

[hippiehacker]

your subject is a bit confusing to the message.

1. You enter nth number of customer to a sheet
2. then you want to check how many entries have been done
3. you want to open then nth amount of Forms for each customer on separate sheets???

 

[Jake Blackmore]

True - I was only guessing.

Perhaps you could provide examples of the data flows?

Jake

your subject is a bit confusing to the message.

1. You enter nth number of customer to a sheet
2. then you want to check how many entries have been done
3. you want to open then nth amount of Forms for each customer on separate sheets???

 

[claireegloff]

Sorry, it is a bit isn't it.

Tab 1 contains an empty table with 25 rows which the end user completes with details of their clients (not necessarily 25).

Tab 2 contains 25 empty forms (61 rows per form columns A:AE) with formulas in the relevant cells retrieving data from tab 1.

What I want to do is have a macro that will hide any forms that do not have a corresponding row in tab 1. So, if there are 5 clients, 20 forms are hidden, 20 client, 5 forms hidden and so on.

Apologies. My knowledge of macros and the language used is very basic.