Help needed!

[tdmcfly]

Hi all,

In the current workbook (a.xlsm)

I want to achieve the following:
- Open up file B.xls
- Delete sheet 1 row 1 of B.xls (which is a blank row)
- After deletion, INSERT Range A1:CH1 from sheet1 to Sheet2, while shifting everything down. If there is a sheet3 within B.xls, insert A1:CH1 to Sheet3 as well.
- Save the file

So far I have this code in "a.XLSM':

Sub Delete_Row_add_header()
    Dim wBook As Workbook
    Dim sh As Worksheet
    Set wBook = Workbooks.Open("O:\AAA\BBB\CCC\b.xls")
   wBook.Worksheets("Sheet1").Rows("1:1").Delete Shift:=xlUp
   wBook.Worksheets("Sheet2").Rows(1).Insert
   wBook.Worksheets("Sheet1").Range("A1:CH1").Copy Worksheets("Sheet2").Range("A1")
On Error Resume Next
Set sh = wBook.Sheets("Sheet3")
If Not sh Is Nothing Then
    sh.Rows(1).Insert
    Worksheets("Sheet1").Range("A1:CH1").Copy sh.Range("A1")
End If
wBook.Save
End Sub

It deletes the blank row, and jumps to saving the file and does not copy a1:ch1 to sheet2 and sheet3..


Your inputs are greatly appreciated!!

Thanks in advance

[Bob Phillips]

Sub Delete_Row_add_header() 
    Dim wBook As Workbook 
    Dim sh As Worksheet 
    Set wBook = Workbooks.Open("O:\AAA\BBB\CCC\b.xls") 
    With wBook

		.Worksheets("Sheet1").Rows("1:1").Delete Shift:=xlUp 
		.Worksheets("Sheet2").Rows(1).Insert 
		.Worksheets("Sheet1").Range("A1:CH1").Copy .Worksheets("Sheet2").Range("A1") 
		On Error Resume Next 
		Set sh = .Worksheets("Sheet3") 
		If Not sh Is Nothing Then 
			sh.Rows(1).Insert 
			.Worksheets("Sheet1").Range("A1:CH1").Copy sh.Range("A1") 
		End If 
		.Save 
	End With
End Sub