VBA S.O.S.

[tdmcfly]

Here's the breakdown

- Header is stored in Sheet1, a2:ch2
- Sheet 2 and Sheet 3 do not have headers
- Sheet 3 may exist in some workbooks, but might not in others.

- Want to copy header in Sheet 1 to Sheet 2 and 3, and shift down all the rest

So far, I've got this......

----

Sub Add_Header()
    Dim wBook As Workbook
    Set wBook = Workbooks.Open("C:\CCT\TEMP\T\ABCDEFG.xls")
    Sheets("Sheet1").Select
    Range("A1:CH1").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Selection.Insert Shift:=xlDown
    Sheets("Sheet1").Select
    Range("A1:CH1").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Selection.Insert Shift:=xlDown
    wBook.Save
    Sheets("Sheet3").Select
    Range("A1").Select
    Selection.Insert Shift:=xlDown
    wBook.Save
End Sub

----

Getting '1004 errors.. =(


Thanks in advance

[westconn1]

try like

dim sht as worksheet
for each sht in thisworkbook.worksheets
   if sht.name = "Sheet3" then exit for
next
if sht is nothing then thisworkbook.sheets.add.name = "Sheet3"
for s = 2 to 3
   sheets("sheet" & s).cells(1, 1).entirerow.insert
   sheets("sheet" & s).range("a1:ch1").value = sheets("sheet1").range("a1:ch1")
next

this avoids selecting cells and changing between sheets, checks if sheet3 exists and adds if not

[tdmcfly]

Thanks Westconn1!!!

For my code, there are workbooks with 2 sheets (sheet1, sheet2) and some with 3 (sheet1,sheet2,sheet3).. I'm just looking to add the Header from sheet1 to sheet2 and sheet3 (if sheet3 exists), if not only add the header to sheet2, instead of adding a new sheet3

[Bob Phillips]

Keep it simple

Worksheets("Sheet2").Rows(1).Insert
Worksheets("Sheet1").Range("A1:CH1").Copy Worksheets("Sheet2").Range("A1")
On Error Resume Next
Set sh = Worksheets("Sheet3")
On Error Goto 0
If Not sh Is Nothing Then
    sh.Rows(1).Insert
    Worksheets("Sheet1").Range("A1:CH1").Copy sh.Range("A1")
End If