PDA

View Full Version : Help needed!



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

Bob Phillips
08-26-2014, 02:23 PM
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