Copy Paste Special Values Macro

Closed
usrvl Posts 4 Registration date Monday October 23, 2017 Status Member Last seen October 25, 2017 - Oct 23, 2017 at 12:17 PM
 Blocked Profile - Oct 25, 2017 at 05:08 PM
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

Blocked Profile
Oct 23, 2017 at 04:16 PM
Have you manually recorded the macro yet?
0
usrvl Posts 4 Registration date Monday October 23, 2017 Status Member Last seen October 25, 2017
Oct 23, 2017 at 05:23 PM
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
0
Blocked Profile
Oct 24, 2017 at 04:38 PM
Paste the macro here, and I will show you what to convert into a variable to make it work.
0
usrvl Posts 4 Registration date Monday October 23, 2017 Status Member Last seen October 25, 2017
Oct 25, 2017 at 09:40 AM
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
0
usrvl Posts 4 Registration date Monday October 23, 2017 Status Member Last seen October 25, 2017
Oct 25, 2017 at 09:40 AM
Thank you for your help!!!
0
Like do a copy paste special on the entire tab and then do formulas for on that?
0
Blocked Profile
Oct 25, 2017 at 04:53 PM
Would a copy tab be easier?
0
So do a copy paste special in the entire tab and then do formulas off of that?
0
Blocked Profile
Oct 25, 2017 at 05:08 PM
Give that a go, especially if the values rarely change, except for the top tab that you are copying.
0