0

I basically just need to know how to copy a header from sheet one that goes from A1-O1 into sheet two, three, four, five and so on...they all have the same header. Sheet one is on the right and sheet two is left and increases to the left. I tried this which I found on some website but it says object required. The error is Runtime Error 424

mainworkBook.Sheets(“Sheet1”).Rows(1).EntireRow.Copy
mainworkBook.Sheets(“Sheet2”).Range(“A1”).Select
mainworkBook.Sheets(“Sheet2”).Paste
samred
  • 3
  • 3
  • I don't think Loops will work because The names of sheet aren't 1 2 3 4 5...they have specific names. Please show me how to do it for one and i will copy paste that for 4 more. Thanks – samred Jul 06 '17 at 15:25
  • Any of the answers below will work. They in different ways loop through the variety of worksheets available and don't require you to input their names, that's why it doesn't matter that you don't name then sheet2, sheet3, ..., Etc. – Busse Jul 06 '17 at 15:43

3 Answers3

0

You can do something like this instead of using Select:

For Each Sheet In ThisWorkbook.Sheets
    ThisWorkbook.Sheets("ALL_DATA").Rows(1).Copy Destination:=Worksheets(Sheet.Name).Range("A1")
Next

This will loop through each sheet in your workbook, take the range you provided (row 1 from Sheet1), and paste it to each sheet by referencing the Name property of each Sheet you are looping through.

The error may have been from the workbook variable, as that is the only thing that is unclear.

I would also recommend looking into this post: How to avoid using Select in Excel VBA macros as it is tremendously helpful in avoiding Select/Activate when possible, which is a common occurrence among those who learn VBA through recording Macros.

Let me know if it works for you.

Busse
  • 853
  • 6
  • 15
  • I;m getting this error: Object does not support this property or method. – samred Jul 06 '17 at 15:30
  • I made the line " For Each Sheet In ThisWorkbook.Sheets ThisWorkbook.Sheets("ALL_DATA").Rows(1).Copy Destination:=Worksheets(Sheet.CMV_D).Range("A1") Next" – samred Jul 06 '17 at 15:30
  • Just keep `Sheet.Name` the same. What that does is, loops through all sheets and pulls the name for you. That part wasn't meant to be changed, my apologies. The only one you should change is "Sheet1" – Busse Jul 06 '17 at 15:31
  • For convenience, I have edited my original answer to include your sheet name. Just copy and paste it directly into your code and try it. – Busse Jul 06 '17 at 15:32
0

This is an excellent place to use a loop. For each sheet in the workbook, paste the same header.

Sub forEachWs()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        Call pasteContents(ws)
    Next
End Sub

Sub pasteContents(ws as Worksheet)
     ** Your code goes here
End Sub

EDIT: The ** section could be as such:

Sub pasteContents(ws as Worksheet)
     ActiveWorkbook.Sheets(“Sheet1”).Rows(1).EntireRow.Copy
     ActiveWorkbook.Sheets(ws).Range(“A1”).Select
     ActiveWorkbook.Sheets(ws).Paste
End Sub

Or it could also be...

Sub pasteContents(ws as Worksheet)
     ActiveWorkbook.Sheets("Sheet1").Rows(1).Copy Destination:=Worksheets(ws).Range("A1")
End Sub
ERT
  • 719
  • 5
  • 16
0

A small loop code.let me know if it works.

Sub COPYPASTeHEADER()
Dim K As Integer
For K = 2 To ActiveWorkbook.Sheets.Count
Sheets("All_Data").Range("A1:O1").COPY Sheets(K).Range("A1")
Next
End Sub
Apurv Pawar
  • 424
  • 3
  • 11
  • This works for every sheet except the last sheet. How can I fix this – samred Jul 06 '17 at 19:02
  • Hi Samred, I don't know why its not working. The code runs on sheets, indexed from left to right. The first sheet being "All_Data", copying its header and pasting onto sheets towards right, if the last sheet is at extreme right. It should work. – Apurv Pawar Jul 07 '17 at 06:51