tdmcfly
08-26-2014, 01:16 PM
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
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