Copy Paste Special Values Macro

[Closed]
Report
Posts
4
Registration date
Monday October 23, 2017
Status
Member
Last seen
October 25, 2017
-
 Blocked Profile -
Hi, I am not good with Macros so I am looking for help creating a macro that takes data from formulas, copies the formulas down to the next row and then does a copy paste special values on the original line. Can someone help me with this?

My cells with formulas are b49:x49, I want to copy those formulas down to b50:x50, then go back to b49:x49 and Paste special values.

Any help would be appreciated. I am not good with this code stuff and tried to do a basic record but could not get it to work.

Thank you!

2 replies


Have you manually recorded the macro yet?
Posts
4
Registration date
Monday October 23, 2017
Status
Member
Last seen
October 25, 2017

Ok so I think my problem is more complex than that. I got the simple macro to work but what I am trying to do is add hours worked to previous hours work via a formula that pulls off of the same template each month. So I want to hard code previous months by running the macro and then have them use the same tab on the file for the current payroll. Does that make sense? Below are hours for 2 employees who enter there data on another tab. I wrote the macro to pull in the current pay period data and then copy the formulas down and hard code. Then I want them to use the template again at the next pay period and run the macro again to then pull in pay period 22. Does that make sense? The formula is a vlookup by name to pull the numbers over. If this is too much I understand. I'm just trying to find a way to make this work. I appreciate your help!

Payroll # EE 1 EE 2 2
2017
1 87
2 72
3 36
4 72
5 60
6 12
7 36
8 64.5
9 72
10 61
11 60
12 24
13 60
14 36
15 49.5
16 48
17 72
18 48
19 63
20 24 72
21 0 36
22
23
24
25
26
Blocked Profile
Paste the macro here, and I will show you what to convert into a variable to make it work.
Posts
4
Registration date
Monday October 23, 2017
Status
Member
Last seen
October 25, 2017

Sub Macro1()
'
' Macro1 Macro
' PSV
'

'
Range("B50:X50").Select
Selection.Copy
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("B51").Select
ActiveSheet.Paste
Range("B50:X50").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
Posts
4
Registration date
Monday October 23, 2017
Status
Member
Last seen
October 25, 2017

Thank you for your help!!!
Like do a copy paste special on the entire tab and then do formulas for on that?

Would a copy tab be easier?
So do a copy paste special in the entire tab and then do formulas off of that?
Blocked Profile
Give that a go, especially if the values rarely change, except for the top tab that you are copying.