Why does this macro not work?

[Bob Vance]

I want to copy the data then paste in back in the same place on its values,
losing all formulas
--
Cells(1, Cells(1, 256).End(xlToLeft).Column + 1).Select


Range(ActiveCell.Offset(0, -11), ActiveCell.Offset(6000, -18)).Select
Selection.Copy
Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(6000, 7)).Select
ActiveSheet.PasteSpecial.PasteValues



Thanks in advance.........Bob Vance

 

[David]

try this
Range(ActiveCell.Offset(0, -11).Address, ActiveCell.Offset(6000, -18).Address).Select

 

[Bob Vance]

Ok what about pasting back in the same place as value only? TIA>Bob

try this:
Range(ActiveCell.Offset(0, -11).Address,

ActiveCell.Offset(6000, -18).Address).Select

 

[Daniel.M]

Hello Bob,

With WhateverRangeYouPutHere ' and no need to select and/or copy
.Value = .Value
End With

Regards,

Daniel M.

 

[Bob Vance]

This is working but how do I change .Paste to PasteValue Only?

Cells(1, Cells(1, 256).End(xlToLeft).Column + 1).Select

Range(ActiveCell.Offset(0, -11), ActiveCell.Offset(6000, -18)).Select
Selection.Copy
Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(6000, 7)).Select
ActiveSheet.Paste
End Sub

 

[Don Guillett]

This will convert all to values in the used range of the worksheet.

Sub makevalues()
With ActiveSheet.UsedRange
..Value = .Value
End With
End Sub

 

[Bob Vance]

Sorry only want that 8 columns selected to turn to values want to keep the
rest till the end of each month before I copy over.TIA.Bob

 

[Daniel.M]

Bob,

You do NOT copy and paste values, you use VBA Excel commands to copy the values.

ActiveCell.Resize(6000, 7).Value = _
ActiveCell.Offset(0, -11).Resize(6000, 7).Value

Regards,

Daniel M.

 

[Bob Vance]

ActiveCell.Offset(71, -9).Copy
ActiveCell.Offset(0, 0).PasteSpecial Paste:=xlPasteValues
I have this is a macro and it seems to work, copying a cell and pasting it
as a Value Only!...TIA....Bob

 

[Don Guillett]

Which 8 columns?
Sub makevalues()
With ActiveSheet.columns("1:8")'UsedRange
.Value = .Value
End With
End Sub

 

[Don Guillett]

Don't use below.Use this after you determine which 8 columns

Sub makevalues()
x = Cells(Rows.Count, "a")
With Range(Cells(1, 1), Cells(x, 8))
..Value = .Value
End With
End Sub

 

[Bob Vance]

I want to be able to go back 11 columns to 18 columns and
copy/paste/formats/value anywhere on the sheet from row 1 the formula can
go to the very bottom of the sheet to the last cell..........TIA Bob

 

[Bob Vance]

This script gets me to the end of my work and then I want to go back -11
columns to -18 for the copy/paste/format/ value back in to their original
cells
Cells(1, Cells(1, 256).End(xlToLeft).Column + 1).Select
Thanks Bob

 

[Don Guillett]

This will do that but then what do you want to do with it? Selections aren't
usually necessary.

Cells(1, Cells(1, 256).End(xlToLeft).Column + 1).Select

Cells(1, Cells(1, 256).End(xlToLeft).Column + 1).Offset(0, -4).Select
no selection necessary to copy to cell b45
Cells(1, Cells(1, 256).End(xlToLeft).Column + 1).Offset(0, -4).copy
range("b45")