VBA/Macro Help. Hopefully easy but I have NO idea what I am doing.

[ned lud]

Below are the macros I have.

Macro 1 simply takes a table on the "frontpage" tab at (A21:E73) and sorts it by the E column values. Works perfectly.

Macro 3 takes the values in cells A17:AF17 on the "frontpage" tab and copies them into the "sheet 3" tab in the next blank row. Also works perfectly.

The problem I am having is that Marco 3 does not quite work. I get #REF! in every pasted cell instead of the values that were in A17>AF17.

Here is what I am hoping you can help me with:

1) I want the the copy/pasting to work so the values are shown on page three.

2) I'd like to combine both macros so, when I assign it to a button, both step happen at the same time.

Thank you very much for any help you can provide. I assume this is easy but I know nothing about VBA.

------------------------------------------

Sub macro1()
'
' macro1 Macro
'
' Keyboard Shortcut: Ctrl+a
'
ActiveWindow.SmallScroll Down:=18
Range("A21:E73").Select
ActiveWorkbook.Worksheets("Front Page").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Front Page").Sort.SortFields.Add2 Key:=Range( _
"E22:E73"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Front Page").Sort
.SetRange Range("A21:E73")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWindow.SmallScroll Down:=-18
Range("E2").Select
End Sub
Sub Macro3()
'
' Macro3 Macro
'
' Keyboard Shortcut: Ctrl+s
'

Range("A17:af17").Copy Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

End Sub

 

[dermie_72]

sounds like the ranges are different but you could try this instead of copy paste:

Dim LR as Long
LR = Sheets("Sheet3").Cells(Rows.count, "A").End(xlUp).Row +1
Sheets("Sheet3").Range("A" & LR & ":AF" & LR).value = Range("A17:af17").Value

See if that works for you.

 

[kvsrinivasamurthy]

Try. Change this

Range("A17:af17").Copy Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

as

Range("A17:af17").Copy
 Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues

 

[ned lud]

sounds like the ranges are different but you could try this instead of copy paste:

Dim LR as Long
LR = Sheets("Sheet3").Cells(Rows.count, "A").End(xlUp).Row +1
Sheets("Sheet3").Range("A" & LR & ":AF" & LR).value = Range("A17:af17").Value

See if that works for you.

This works. Thank you very much.

 

[ned lud]

Try. Change this

Range("A17:af17").Copy Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

as

Range("A17:af17").Copy
 Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues

This works as well. Thank you very much.