Macro to automatically generate report

[fabriciofcmiranda]

Hi, everyone.

I need to program a macro to generate a report performing the following actions:

1. Ask me to select the row where the input information is;
2. After I select the row, the macro needs to copy the information on each collumn of that row and paste it into the right cell of a report sheet (this report sheet is a template situated on another sheet of the same workbook that is formatted as a report).

The idea here is to automatically generate a report for each row of information that I have. Today, I have to manually copy and paste each information, what gives me a hard time and increases the possibility of mistakes.

I'd really appreciate if someone could help me with this one.

Best regards,
Fabricio Miranda

 

[Andrew Poulsom]

Couldn't you use formulas with the INDEX function to get your data into the template? You would just need to have the row number as a variable.

If you are still stuck, tell us where the data is and what goes where on your template sheet.

 

[fabriciofcmiranda]

Thank you so much for your help, Andrew. It worked perfectly! Your post showed me that, sometimes, the answer is a lot simpler than we can imagine and that macros are not always the easiest and more effective way to solve Excel problems.
Once more, thank you very much for your help and attention!
Best regards,
Fabricio

 

[fabriciofcmiranda]

The solution really worked, but I'm still trying to automate the process as much as possible. So, I'm trying to program a macro to do this:

1. Ask the user to point at a specific cell;
2. Get the row number of the cell;
3. Paste the row number into another cell, that is targeted by the INDEX formula.

The macro looks like this:

Sub GetRowNumber()
'
' Macro recorded 22/8/2006 by BDM
'
    Dim rng As Range
    ActiveSheet.Unprotect Password:="password"
    On Error Resume Next
    
    Set rng = Application.InputBox(prompt:="Select the cell where the information is.", _
    Title:="Cell Selection", Default:=Selection.Address, Type:=8)
    On Error GoTo 0
    If Not rng Is Nothing Then
      If Not rng.Address = ActiveCell.Address Then rng.Select
    Else
      Exit Sub
    End If
    
    'THIS IS WHERE I NEED HELP
    
    Selection.EntireRow.Hidden = False
    Application.CutCopyMode = False
    Range("report_beggining").Select
    ActiveSheet.Protect Password:="password"
End Sub

I have two questions:

1. What is the code that I need to get the row number of the cell that was selected in the beggining?

2. When the cell that is selected is on another sheet, it gives me an error message on the code "If Not rng.Address = ActiveCell.Address Then rng.Select". Why does this happen?

Thank you very much for your help!

Best regards,
FM