Hi,

Could someone give me a code please that (in this order) in each of sheets 4
to 11:

- Copies/paste values in cols A and K
- Deletes cols S,T and U
- Saves a copy of the file, named New file to the same directory as the
source file

Is this possible?

Thanks
LiAD

Try this on a back-up copy as it'll delete columns!

Sam

Sub test()

Dim wb As Workbook
Dim ws As Worksheet

Set wb = ActiveWorkbook

Dim i As Integer
Dim s As String

For i = 4 To 11
s = "Sheet" & i
Set ws = wb.Worksheets(s)
With ws
.Columns("A:A").Value = .Columns("A:A").Value
.Columns("K:K").Value = .Columns("K:K").Value
.Columns("S:U").Delete
End With
Next i

wb.SaveAs wb.Path & "\Newfile.xls"

End Sub


"LiAD" wrote:

Hi,

Could someone give me a code please that (in this order) in each of sheets 4
to 11:

- Copies/paste values in cols A and K
- Deletes cols S,T and U
- Saves a copy of the file, named New file to the same directory as the
source file

Is this possible?

Thanks
LiAD

Hi,

I get an error on

Set ws = wb.Worksheets(s)

Any ideas why?

Thanks for your help.

"Sam Wilson" wrote:

Try this on a back-up copy as it'll delete columns!

Sam

Sub test()

Dim wb As Workbook
Dim ws As Worksheet

Set wb = ActiveWorkbook

Dim i As Integer
Dim s As String

For i = 4 To 11
s = "Sheet" & i
Set ws = wb.Worksheets(s)
With ws
.Columns("A:A").Value = .Columns("A:A").Value
.Columns("K:K").Value = .Columns("K:K").Value
.Columns("S:U").Delete
End With
Next i

wb.SaveAs wb.Path & "\Newfile.xls"

End Sub


"LiAD" wrote:

Hi,

Could someone give me a code please that (in this order) in each of sheets 4
to 11:

- Copies/paste values in cols A and K
- Deletes cols S,T and U
- Saves a copy of the file, named New file to the same directory as the
source file

Is this possible?

Thanks
LiAD

It's looking for "Sheet4" in the current workbook (assuming it fails on the
first loop) so Sheet4 may not exist.

Sam

"LiAD" wrote:

Hi,

I get an error on

Set ws = wb.Worksheets(s)

Any ideas why?

Thanks for your help.

"Sam Wilson" wrote:

Try this on a back-up copy as it'll delete columns!

Sam

Sub test()

Dim wb As Workbook
Dim ws As Worksheet

Set wb = ActiveWorkbook

Dim i As Integer
Dim s As String

For i = 4 To 11
s = "Sheet" & i
Set ws = wb.Worksheets(s)
With ws
.Columns("A:A").Value = .Columns("A:A").Value
.Columns("K:K").Value = .Columns("K:K").Value
.Columns("S:U").Delete
End With
Next i

wb.SaveAs wb.Path & "\Newfile.xls"

End Sub


"LiAD" wrote:

Hi,

Could someone give me a code please that (in this order) in each of sheets 4
to 11:

- Copies/paste values in cols A and K
- Deletes cols S,T and U
- Saves a copy of the file, named New file to the same directory as the
source file

Is this possible?

Thanks
LiAD

Hi,

The workbook I have has 11 sheets.
I am on sheet 2 when i try to run the macro.
Even if i change the sheet name to Sheet 4 it still doesn't work.

Is there something else in this line that trips up?

Set ws = wb.Worksheets(s)

Thanks

"Sam Wilson" wrote:

It's looking for "Sheet4" in the current workbook (assuming it fails on the
first loop) so Sheet4 may not exist.

Sam

"LiAD" wrote:

Hi,

I get an error on

Set ws = wb.Worksheets(s)

Any ideas why?

Thanks for your help.

"Sam Wilson" wrote:

Try this on a back-up copy as it'll delete columns!

Sam

Sub test()

Dim wb As Workbook
Dim ws As Worksheet

Set wb = ActiveWorkbook

Dim i As Integer
Dim s As String

For i = 4 To 11
s = "Sheet" & i
Set ws = wb.Worksheets(s)
With ws
.Columns("A:A").Value = .Columns("A:A").Value
.Columns("K:K").Value = .Columns("K:K").Value
.Columns("S:U").Delete
End With
Next i

wb.SaveAs wb.Path & "\Newfile.xls"

End Sub


"LiAD" wrote:

Hi,

Could someone give me a code please that (in this order) in each of sheets 4
to 11:

- Copies/paste values in cols A and K
- Deletes cols S,T and U
- Saves a copy of the file, named New file to the same directory as the
source file

Is this possible?

Thanks
LiAD