asked on

Excel VBA Macro - copy past column values only, formulas

1. I need to know if the vba below is the best way to copy and paste the formula results (values) right over the same cell locations as the formulas.

2. In addition, I want to only copy and paste the columns starting at row 4 b/c of special formulas in rows 1-3 that must not be affected.

3. Also, is it better to construct the vba code to point to named ranges instead of actual columns so that if the columns are ever moved, the vba will follow those columns...if so, I guess this would take care of item #2 listed above b/c in the named range I could do an offset function to select the whole column excluding rows 1-3.

Sub CopyCol()

      Sheets("Blotter").Columns("H").Copy
      Sheets("Blotter").Columns("H").PasteSpecial xlPasteValues

      Sheets("Blotter").Columns("I").Copy
      Sheets("Blotter").Columns("I").PasteSpecial xlPasteValues

      Sheets("Blotter").Columns("O").Copy
      Sheets("Blotter").Columns("O").PasteSpecial xlPasteValues

      Sheets("Blotter").Columns("P").Copy
      Sheets("Blotter").Columns("P").PasteSpecial xlPasteValues

      Sheets("Blotter").Columns("Q").Copy
      Sheets("Blotter").Columns("Q").PasteSpecial xlPasteValues

      Sheets("Blotter").Columns("R").Copy
      Sheets("Blotter").Columns("R").PasteSpecial xlPasteValues

      Sheets("Blotter").Columns("U").Copy
      Sheets("Blotter").Columns("U").PasteSpecial xlPasteValues

End Sub

Shums Faruk

Hi,

Please try below:

Sub CopyPaste()
Dim Ws As Worksheet
Dim LR As Long
Application.ScreenUpdating = False
Set Ws = Worksheets("Blotter")
LR = Ws.Range("H" & Rows.Count).End(xlUp).Row
Ws.Range("H4:H" & LR).Value = Ws.Range("H4:H" & LR).Value
Ws.Range("I4:I" & LR).Value = Ws.Range("I4:I" & LR).Value
Ws.Range("O4:R" & LR).Value = Ws.Range("O4:R" & LR).Value
Ws.Range("U4:U" & LR).Value = Ws.Range("U4:U" & LR).Value
Ws.Range("H3").Select
Application.ScreenUpdating = True
End Sub

Open in new window

IO_Dork

ASKER

LVL, does this attach item 3 on my list? if I move one or more of the columns over to another spot in the worksheet will the macro follow? For example, if the formulas in column H are moved over to column A, will the macro follow and point to column A? Or do we have to invoke named ranges?

Shums Faruk

No.
You need to specify every time, when you change columns.

Shums Faruk

If you want to copy and paste special values of named change then try below, change the NameRange:

Sub PasteRanges()
    Range("DataCopy").Copy
    Range("DataCopy")(1, 1).PasteSpecial xlPasteValues
    Application.CutCopyMode = False
End Sub

Open in new window

SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

IO_Dork

ASKER

ok, I'll look into creating named ranges, in the mean time can you modify the below code to select the last populated cell in column C instead of leaving cursor at H3?

Sub CopyPaste()
Dim Ws As Worksheet
Dim LR As Long
Application.ScreenUpdating = False
Set Ws = Worksheets("Blotter")
LR = Ws.Range("H" & Rows.Count).End(xlUp).Row
Ws.Range("H4:H" & LR).Value = Ws.Range("H4:H" & LR).Value
Ws.Range("I4:I" & LR).Value = Ws.Range("I4:I" & LR).Value
Ws.Range("O4:R" & LR).Value = Ws.Range("O4:R" & LR).Value
Ws.Range("U4:U" & LR).Value = Ws.Range("U4:U" & LR).Value
Ws.Range("H3").Select
Application.ScreenUpdating = True
End Sub

Shums Faruk

Yes,

Try below:

Sub CopyPaste()
Dim Ws As Worksheet
Dim LR As Long
Application.ScreenUpdating = False
Set Ws = Worksheets("Blotter")
LR = Ws.Range("H" & Rows.Count).End(xlUp).Row
Ws.Range("H4:H" & LR).Value = Ws.Range("H4:H" & LR).Value
Ws.Range("I4:I" & LR).Value = Ws.Range("I4:I" & LR).Value
Ws.Range("O4:R" & LR).Value = Ws.Range("O4:R" & LR).Value
Ws.Range("U4:U" & LR).Value = Ws.Range("U4:U" & LR).Value
Application.Goto Ws.Range("C" & LR), scroll:=True
Application.ScreenUpdating = True
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

Shums Faruk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

IO_Dork

ASKER

excellent solution and fast.

Shums Faruk

You're Welcome! Glad we're able to help