Hank Isaacs
asked on
Excel 2010 - Run Macro at current cell location
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.
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.
See if this link helps :)
https://www.experts-exchange.com/questions/26902875/Page-Break-via-VBA.html
Sid
https://www.experts-exchange.com/questions/26902875/Page-Break-via-VBA.html
Sid
ASKER
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?
Instead of inserting a static cell in VBA, can we refer to Active.cell?
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
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
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
Please be more specific, some sample data would be good.
Thanks
Rob H
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.
ASKER
Sub Macro14()
'
' Macro14 Macro
'
'
ActiveWindow.SelectedSheet s.HPageBre aks.Add Before:=ActiveCell
Range("A36:L38").Select
Selection.Copy
Range("CURRENT SELECTED CELL").Select
ActiveSheet.Paste
End Sub
'
' Macro14 Macro
'
'
ActiveWindow.SelectedSheet
Range("A36:L38").Select
Selection.Copy
Range("CURRENT SELECTED CELL").Select
ActiveSheet.Paste
End Sub
Hank11: Did you see the link that I gave?
Sid
Sid
ASKER
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.
My expertise in VBA is nil and I'm limited to understaning Macros.
I'm don't know what the link is telling me.
Ok, let me break it down for you :)
Sid
Sid
Is this what you want?
Sid
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
Sid
If your headers are in Range("A36:L38") then the above code becomes
Sid
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
Sid
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
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
ASKER
Which cell are you selecting?
Sid
Sid
I suspect that you selected the cell in the first column. Try this code.
Sid
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
Sid
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
Put an apostrophe at the start of that line and rerun the macro.
Cheers
Rob H
ASKER
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.
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.
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
Sid
ASKER
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.
What do we have to enter to the code to have the first blank cell under the new header ready to enter data.
ASKER
Example, if we inserted the new header in A300, we would want the cell active and ready to type in A304 etc...
>>>>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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
Outstanding solution.
Thanks so much Sid.
Thanks so much Sid.
Hank11: Rob also worked on this. It would be just fair to split the points with him :)
Sid
Sid
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