Solved

Excel 2010 - Run Macro at current cell location

Posted on 2011-03-24
26
1,091 Views
Last Modified: 2012-05-11
We're entering a lot of data and want to record a macro that inserts a page break at the current cell we've selected - then copies and pastes a standard header that we're using.

The header to copy is at a static location near the top (3 rows) but we don't know how to tell the macro to insert the page break and paste at the current cell that we selected.

Thanks.
0
Comment
Question by:Hank11
  • 11
  • 8
  • 7
26 Comments
 
LVL 31

Expert Comment

by:Rob Henson
ID: 35211180
Why repeat the headers??

Assuming headers in rows 1-3 select A4 and choose Freeze Panes from the Window Menu. The headers will now always be visible.

For printing, in the Page Setup select the sheet tab and select the option for rows to repeat and select header rows.

Cheers
Rob H
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35211185
0
 

Author Comment

by:Hank11
ID: 35211253
Unfortunetly, we will have several different tables with different headers and cannot use the Repeat Row range in page setup.

Instead of inserting a static cell in VBA, can we refer to Active.cell?
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 35211270
Is there a particular reason why you want specific page breaks rather than letting the page breaks sort themselves out eg new page for change of reference/dept etc?

If so let us know what criteria change and then we could write a macro that adds the page breaks after all data entered.

Cheers
Rob
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 35211295
Your question states that you have static headers to copy to the required row. If the macro copies the headers they will be the same for each table. A copy will not give different headers unless they are formula driven.

Please be more specific, some sample data would be good.

Thanks
Rob H
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 35211315
In VBA ActiveCell.Row will return the row of the active cell, so yes a routine could take the row of the active cell before going off to get headers to copy and return to active row to paste.
0
 

Author Comment

by:Hank11
ID: 35211320
Sub Macro14()
'
' Macro14 Macro
'

'
    ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
    Range("A36:L38").Select
    Selection.Copy
    Range("CURRENT SELECTED CELL").Select
    ActiveSheet.Paste
End Sub
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35211326
Hank11: Did you see the link that I gave?

Sid
0
 

Author Comment

by:Hank11
ID: 35211354
Sid,
My expertise in VBA is nil and I'm limited to understaning Macros.

I'm don't know what the link is telling me.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35211361
Ok, let me break it down for you :)

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35211391
Is this what you want?

Sub Sample()
    Dim rng As Range
    
    '~~> Assuming that your headers are in this range
    Set rng = Range("A1:B5")
    
    '~~> This will insert Pagebreak at activecell
    ActiveCell.Offset(1, 0).Select
    ActiveWindow.SelectedSheets.HPageBreaks.Add before:=ActiveCell
    ActiveWindow.SelectedSheets.VPageBreaks.Add before:=ActiveCell
    
    '~~> This will copy the header to activecell
    rng.Copy ActiveCell
End Sub

Open in new window


Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35211403
If your headers are in Range("A36:L38") then the above code becomes

Sub Sample()
    Dim rng As Range
    
    '~~> Assuming that your headers are in this range
    Set rng = Range("A36:L38")
    
    '~~> This will insert Pagebreak at activecell
    ActiveCell.Offset(1, 0).Select
    ActiveWindow.SelectedSheets.HPageBreaks.Add before:=ActiveCell
    ActiveWindow.SelectedSheets.VPageBreaks.Add before:=ActiveCell
    
    '~~> This will copy the header to activecell
    rng.Copy ActiveCell
End Sub

Open in new window


Sid
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 35211469
The code above does what you have asked but......

With the headers still being the same, I don't see why you can't use the Repeat Rows function.

Are you printing specific chunks of the input data? Would a data extract using a filter enable the print to be accomplished?

Cheers
Rob H
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:Hank11
ID: 35211495
Sid,
I select a cell and execute the macro and goes to debug.  See attached image
Debug-Image.bmp
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35211497
Which cell are you selecting?

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35211525
I suspect that you selected the cell in the first column. Try this code.

Sub Sample()
    Dim rng As Range
    
    '~~> Assuming that your headers are in this range
    Set rng = Range("A36:L38")
    
    '~~> This will insert Pagebreak at activecell
    ActiveCell.Offset(1, 0).Select
    
    If ActiveCell.Row <> 1 Then _
    ActiveWindow.SelectedSheets.HPageBreaks.Add before:=ActiveCell
    
    If ActiveCell.Column <> 1 Then
    ActiveWindow.SelectedSheets.VPageBreaks.Add before:=ActiveCell
    
    '~~> This will copy the header to activecell
    rng.Copy ActiveCell
End Sub

Open in new window


Sid
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 35211540
I am guessing column A selected so can't put vpage break before column A.

Put an apostrophe at the start of that line and rerun the macro.

Cheers
Rob H
0
 

Author Comment

by:Hank11
ID: 35211542
The master list part of a construction design document.  It has several tables, but one in particular will have 50 sheets after the data is input.  the spreadsheet goes to a different department that will be responsible for printing it out.  The boss wants us to dumb it down so the girls printing don't have to select different worksheets fo printing.  This would have made it simple by putting each table style on a seperate worksheet and simply using the Repeat Row feature.  

I will ultimately assign the new macro to a button and give the tech an easy tool for him to insert the page break and table header.  Once he's done entering data, the macro will be deleted so the girls printing the doc won't screw things up.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35211569
Hank, I posted the amended code above in ID: 35211525. Have a look at that and tell me if you are getting any errors?

Sid
0
 

Author Comment

by:Hank11
ID: 35211574
Commenting that line out worked.

What do we have to enter to the code to have the first blank cell under the new header ready to enter data.
0
 

Author Comment

by:Hank11
ID: 35211580
Example, if we inserted the new header in A300, we would want the cell active and ready to type in A304 etc...
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35211582
>>>>Commenting that line out worked.

You don't need to comment it :) see my post above.

>>>>What do we have to enter to the code to have the first blank cell under the new header ready to enter data.

What do you mean?

Sid
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 500 total points
ID: 35211599
Is this what you want?

Sub Sample()
    Dim rng As Range, ExitLoop As Boolean
    
    '~~> Assuming that your headers are in this range
    Set rng = Range("A36:L38")
    
    '~~> This will insert Pagebreak at activecell
    ActiveCell.Offset(1, 0).Select
    
    If ActiveCell.Row <> 1 Then _
    ActiveWindow.SelectedSheets.HPageBreaks.Add before:=ActiveCell
    
    If ActiveCell.Column <> 1 Then _
    ActiveWindow.SelectedSheets.VPageBreaks.Add before:=ActiveCell
    
    '~~> This will copy the header to activecell
    rng.Copy ActiveCell
    
    Do While ExitLoop = False
        If Len(Trim(ActiveCell.Value)) = 0 Then ExitLoop = True
        ActiveCell.Offset(1, 0).Select
    Loop
End Sub

Open in new window


Sid
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 35211602
Girls in other dept receive file and click on Print button????

Other than access to different printers and media etc, if page setup is already done what else do they need to do?

Still non comprendez but it is late!

Cheers
Rob H
0
 

Author Closing Comment

by:Hank11
ID: 35211639
Outstanding solution.
Thanks so much Sid.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35211647
Hank11: Rob also worked on this. It would be just fair to split the points with him :)

Sid
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now