How to copy header range of one destination worksheet to all worksheets except?

[motilulla]

Hi everybody,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I want to copy header range A1 To K1 of workbook “summary” to all work sheets in range A1 To K1 except worksheets “Final” and “year” <o></o>
<o></o>
Please Help <o></o>
<o></o>
Thanks And Regards,<o></o>
Moti<o></o>

 

[rs2k]

One way is to copy the range A1:K1 and then select your first worksheet tab, press 'Shift' and then click your last required worksheet, so all worksheets in between are selected, then paste in A1, all your selected sheets should have the same headings.

HTH
Colin

 

[motilulla]

Hi rs2k,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Thank you very much for excellent Tip working with keyboard options really save lot of time. I will remember this keyboard trick. It is nice to learn always.<o></o>
<o></o>
As once I finalize summary then I save all work sheets in another workbook so far every time I have to create new 35 to 45 sheets. For that I prefer to get VBA solution to copy header with one button.<o></o>
<o></o>
<o></o>
Thanks And Regards,<o></o>
Moti<o></o>

 

[motilulla]

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I have tried to make a code that copy header row from summary sheet to all the worksheet is in the workbook.<o></o>

Option Explicit<o:p></o:p>
 <o:p></o:p>
Sub Copy_Row_Header_1()<o:p></o:p>
 <o:p></o:p>
Application.CutCopyMode = True<o:p></o:p>
Dim Counter As Long, j As Long<o:p></o:p>
Counter = Sheets.Count<o:p></o:p>
For j = 1 To Counter<o:p></o:p>
    Sheets("summary").Select<o:p></o:p>
    Rows("1:1").Select<o:p></o:p>
    Selection.Copy<o:p></o:p>
    Sheets(j).Select<o:p></o:p>
    Range("A1").Select<o:p></o:p>
    ActiveSheet.Paste<o:p></o:p>
    Range("A1").Select<o:p></o:p>
 <o:p></o:p>
Next j<o:p></o:p>
    <o:p></o:p>
Application.CutCopyMode = False<o:p></o:p>
End Sub<o:p></o:p>

Now I need expert help to modify code that code does not copy header to worksheets “Final” and “year” <o></o>
<o></o>
Please help<o></o>
<o></o>
<o></o>
Thanks And Regards,<o></o>
Moti<o></o>

 

[motilulla]

Hi everybody,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Searching through Internet I find some code modifying them as per my requirement.<o></o>
<o></o>
I was able to get code work with one Condition that setting worksheets “Final” and “Year” at position first and second and “Data“ worksheet at position third. So the code below copy “data” worksheet header to all worksheet are after the data sheet.<o></o>
<o></o>
But my fear is that if “Data” work sheet move to at position forth or other. Then code will fail all header will be mismatch. Because will be copied header of sheet which is at position three.<o></o>
<o></o>
Please any one can help to fix this code to copy header from “Data” sheet to all worksheets are in workbook EXCEPT “Final” and “Year” does not matter position of the sheet.
<o></o>

Sub Copy_Header ()<o:p></o:p>
Dim myCount<o:p></o:p>
Dim i<o:p></o:p>
myCount = Application.Sheets.Count<o:p></o:p>
Sheets(3).Select<o:p></o:p>
Range("A1:K1").Select<o:p></o:p>
Selection.Copy<o:p></o:p>
For i = 4 To myCount<o:p></o:p>
    <o:p></o:p>
   'This line of code Paste Header On Selected Sheet "i" and will continue till myCount<o:p></o:p>
 <o:p></o:p>
    Sheets(i).Select<o:p></o:p>
    Range("A1").Select<o:p></o:p>
    ActiveSheet.Paste<o:p></o:p>
    Range("A1").Select<o:p></o:p>
 <o:p></o:p>
Next i<o:p></o:p>
Sheets("Data").Select<o:p></o:p>
Range("A1").Select<o:p></o:p>
Application.CutCopyMode = False<o:p></o:p>
MsgBox ("HEADER" & Chr(13) & "COPIED" & Chr(13) & "To ALL SHEETS")<o:p></o:p>
End Sub<o:p></o:p>

Please help<o></o>
<o></o>
Thanks And Regards,<o></o>
Moti<o></o>

 

[motilulla]

Hi everybody,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Finally I could fix the code, as I required. The code below restrict to copy header range on sheets “Final”, “Year” And “Data” now does not matter position of the sheet. Wherever are they? <o></o>

[FONT=Arial][SIZE=3]Sub Copy_Header ()<o:p></o:p>[/SIZE][/FONT]
[FONT=Arial][SIZE=3] <o:p></o:p>[/SIZE][/FONT]
[FONT=Arial][SIZE=3]Dim ws As Worksheet<o:p></o:p>[/SIZE][/FONT]
[FONT=Arial][SIZE=3]Application.DisplayAlerts = False<o:p></o:p>[/SIZE][/FONT]
[FONT=Arial][SIZE=3] <o:p></o:p>[/SIZE][/FONT]
[FONT=Arial][SIZE=3]For Each ws In Worksheets<o:p></o:p>[/SIZE][/FONT]
[FONT=Arial][SIZE=3]If ws.Name <> "Data" And ws.Name <> "Final" And ws.Name <> "Year" Then Sheets("data").Range("A1:K1").Copy ws.Range("A1")<o:p></o:p>[/SIZE][/FONT]
[FONT=Arial][SIZE=3] <o:p></o:p>[/SIZE][/FONT]
[FONT=Arial][SIZE=3]Next<o:p></o:p>[/SIZE][/FONT]
[FONT=Arial][SIZE=3] <o:p></o:p>[/SIZE][/FONT]
[FONT=Arial][SIZE=3]Application.DisplayAlerts = True<o:p></o:p>[/SIZE][/FONT]
[FONT=Arial][SIZE=3] <o:p></o:p>[/SIZE][/FONT]
[FONT=Arial][SIZE=3]MsgBox ("HEADER" & Chr(13) & "COPIED" & Chr(13) & "TO ALL SHEETS EXCEPT “FINAL” AND “YEAR” ")<o:p></o:p>[/SIZE][/FONT]
[FONT=Arial][SIZE=3] <o:p></o:p>[/SIZE][/FONT]
[FONT=Arial][SIZE=3]End Sub<o:p></o:p>[/SIZE][/FONT]

Thanks And Regards,<o></o>
Moti<o></o>