Macro to automatically generate report

fabriciofcmiranda

Board Regular
Joined
Jun 27, 2006
Messages
62
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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Board Regular
Joined
Jun 27, 2006
Messages
62
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

Board Regular
Joined
Jun 27, 2006
Messages
62
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:

Code:
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
 

Forum statistics

Threads
1,137,332
Messages
5,680,875
Members
419,937
Latest member
Talic

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top