Why does this macro not work?

  • Thread starter Thread starter Bob Vance
  • Start date Start date
B

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
 
try this
Range(ActiveCell.Offset(0, -11).Address, ActiveCell.Offset(6000, -18).Address).Select
 
Ok what about pasting back in the same place as value only? TIA>Bob

David said:
try this:
Range(ActiveCell.Offset(0, -11).Address,
Click to expand...
ActiveCell.Offset(6000, -18).Address).Select
 
Hello Bob,

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

Regards,

Daniel M.
 
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
 
This will convert all to values in the used range of the worksheet.

Sub makevalues()
With ActiveSheet.UsedRange
..Value = .Value
End With
End Sub
 
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
 
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.
 
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
 
Which 8 columns?
Sub makevalues()
With ActiveSheet.columns("1:8")'UsedRange
.Value = .Value
End With
End Sub
 
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
 
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
 
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
 
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
Click to expand...
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")
 
Back
Top