ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Copy worksheet question (https://www.excelbanter.com/excel-worksheet-functions/37948-copy-worksheet-question.html)

Greegan
Copy worksheet question
 
How would I have the Header copied over when I copy the worksheet I've
selected - but in a macro?

I have it now so I click a menu button that's linked to the macro which
copies the worksheet and has very few issues showing up, except the header
is not copying over.

I understand this is how its supposed to happen, but outside of recording a
macro where I'm copying info from one sheet's Header to the other I don't
know what else I can do. Can anyone assist me with this?

Thanks in advance

G

Dave Peterson
If your macro does the same thing as edit|Move or copy sheet, then you should be
seeing the headers, too.

But it sounds like you're copying some of the cells--not all. Maybe you can
amend the macro to include those header rows???

You may want to post that portion of the code that does the copying.

Greegan wrote:

How would I have the Header copied over when I copy the worksheet I've
selected - but in a macro?

I have it now so I click a menu button that's linked to the macro which
copies the worksheet and has very few issues showing up, except the header
is not copying over.

I understand this is how its supposed to happen, but outside of recording a
macro where I'm copying info from one sheet's Header to the other I don't
know what else I can do. Can anyone assist me with this?

Thanks in advance

G

--

Dave Peterson

Greegan
All i did for this is record an RIGHT CLICK on the tab"Insert Template DO
NOT DELETEmove/copycreate copy, and then i named the tab TEMP
and I believe I zoomed the size to 80 % but that's about it

any help is appreciated

Here it is...

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 7/29/2005 by sbeattie
'

'
Dim nSht As Worksheet
Set nSht = Sheets.Add
nSht.Move befo=Sheets(Sheets.Count)
ActiveSheet.Name = "Temp"
Range("A1").Select
' test passed
Application.CutCopyMode = False
With ActiveWindow
.DisplayGridlines = False
.DisplayHeadings = False
End With
Range("A1").Select
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 80
.PrintErrors = xlPrintErrorsDisplayed
End With
Sheets("Insert Template DO NOT DELETE").Select
Cells.Select
Selection.Copy
Sheets("Temp").Select
Cells.Select
ActiveSheet.Paste
Range("A1:H2").Select
End Sub



"Dave Peterson" wrote in message
...
If your macro does the same thing as edit|Move or copy sheet, then you
should be
seeing the headers, too.

But it sounds like you're copying some of the cells--not all. Maybe you
can
amend the macro to include those header rows???

You may want to post that portion of the code that does the copying.

Greegan wrote:

How would I have the Header copied over when I copy the worksheet I've
selected - but in a macro?

I have it now so I click a menu button that's linked to the macro which
copies the worksheet and has very few issues showing up, except the
header
is not copying over.

I understand this is how its supposed to happen, but outside of recording
a
macro where I'm copying info from one sheet's Header to the other I don't
know what else I can do. Can anyone assist me with this?

Thanks in advance

G

--

Dave Peterson

Norman Jones
Hi Greegan,

Try replacing your code with:

Sub InsertSheet()

Sheets("Insert Template DO NOT DELETE").Copy _
After:=Sheets(Sheets.Count)
With ActiveWindow
.DisplayGridlines = False
.DisplayHeadings = True
.Zoom = 80
End With

ActiveSheet.Name = "Temp"

End Sub

---
Regards,
Norman



"Greegan" wrote in message
...
All i did for this is record an RIGHT CLICK on the tab"Insert Template DO
NOT DELETEmove/copycreate copy, and then i named the tab TEMP
and I believe I zoomed the size to 80 % but that's about it

any help is appreciated

Here it is...

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 7/29/2005 by sbeattie
'

'
Dim nSht As Worksheet
Set nSht = Sheets.Add
nSht.Move befo=Sheets(Sheets.Count)
ActiveSheet.Name = "Temp"
Range("A1").Select
' test passed
Application.CutCopyMode = False
With ActiveWindow
.DisplayGridlines = False
.DisplayHeadings = False
End With
Range("A1").Select
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 80
.PrintErrors = xlPrintErrorsDisplayed
End With
Sheets("Insert Template DO NOT DELETE").Select
Cells.Select
Selection.Copy
Sheets("Temp").Select
Cells.Select
ActiveSheet.Paste
Range("A1:H2").Select
End Sub



"Dave Peterson" wrote in message
...
If your macro does the same thing as edit|Move or copy sheet, then you
should be
seeing the headers, too.

But it sounds like you're copying some of the cells--not all. Maybe you
can
amend the macro to include those header rows???

You may want to post that portion of the code that does the copying.

Greegan wrote:

How would I have the Header copied over when I copy the worksheet I've
selected - but in a macro?

I have it now so I click a menu button that's linked to the macro which
copies the worksheet and has very few issues showing up, except the
header
is not copying over.

I understand this is how its supposed to happen, but outside of
recording a
macro where I'm copying info from one sheet's Header to the other I
don't
know what else I can do. Can anyone assist me with this?

Thanks in advance

G

--

Dave Peterson

Greegan
That's awesome man! thanks a lot.
"Norman Jones" wrote in message
...
Hi Greegan,

Try replacing your code with:

Sub InsertSheet()

Sheets("Insert Template DO NOT DELETE").Copy _
After:=Sheets(Sheets.Count)
With ActiveWindow
.DisplayGridlines = False
.DisplayHeadings = True
.Zoom = 80
End With

ActiveSheet.Name = "Temp"

End Sub

---
Regards,
Norman



"Greegan" wrote in message
...
All i did for this is record an RIGHT CLICK on the tab"Insert Template DO
NOT DELETEmove/copycreate copy, and then i named the tab TEMP
and I believe I zoomed the size to 80 % but that's about it

any help is appreciated

Here it is...

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 7/29/2005 by sbeattie
'

'
Dim nSht As Worksheet
Set nSht = Sheets.Add
nSht.Move befo=Sheets(Sheets.Count)
ActiveSheet.Name = "Temp"
Range("A1").Select
' test passed
Application.CutCopyMode = False
With ActiveWindow
.DisplayGridlines = False
.DisplayHeadings = False
End With
Range("A1").Select
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 80
.PrintErrors = xlPrintErrorsDisplayed
End With
Sheets("Insert Template DO NOT DELETE").Select
Cells.Select
Selection.Copy
Sheets("Temp").Select
Cells.Select
ActiveSheet.Paste
Range("A1:H2").Select
End Sub



"Dave Peterson" wrote in message
...
If your macro does the same thing as edit|Move or copy sheet, then you
should be
seeing the headers, too.

But it sounds like you're copying some of the cells--not all. Maybe you
can
amend the macro to include those header rows???

You may want to post that portion of the code that does the copying.

Greegan wrote:

How would I have the Header copied over when I copy the worksheet I've
selected - but in a macro?

I have it now so I click a menu button that's linked to the macro which
copies the worksheet and has very few issues showing up, except the
header
is not copying over.

I understand this is how its supposed to happen, but outside of
recording a
macro where I'm copying info from one sheet's Header to the other I
don't
know what else I can do. Can anyone assist me with this?

Thanks in advance

G

--

Dave Peterson


All times are GMT +1. The time now is 11:31 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com