Paste and cut code

[LiAD]

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

 

[Sam Wilson]

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]

Hi,

I get an error on

Set ws = wb.Worksheets(s)

Any ideas why?

Thanks for your help.

 

[Sam Wilson]

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

Sam

 

[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