VBA help

[mr_hiboy]

Hi,

I've just completed a rather large product/pricing model and realised that unless I have something that allows the actual data (formula) to be pasted as values, I will have do a lot of manual copy/pasting everything I open a file.

This is way beyond my VBA skills (even been generous to myself by saying I have VBA skills!)

This is what I need to do (I've mocked up the file too).


Starting column M, and setting a counter at 10

Select M12:M63

Copy, Paste Values Only in N12:N63

Then count from column N, across 7 columns to column T; so count would now be 17

Select T12:T63

Copy, Paste Values Only in U12:U63

Repeat this, i.e. count across 7 columns (would take you to column AA), copy column 12:63, paste values into next column (AB) 12:63 (adding 7 to the counter each time)

Do this until count =MAX(ProdList!L9:L28) - this is because I have various files, all with different number of products.

https://dl.dropboxusercontent.com/u/9071274/VBA to paste values across range with counter.xlsx

Any help here would hugely appreciated.

thanks

 

[Deepak]

As i understand you are in need of something like this. Where i didn't get "Do this until count =MAX(ProdList!L9:L28) "

Option Explicit

Sub Copy_Value()
Dim cRow As Long, col As Long
Dim i As Long

cRow = Application.Max(Sheets("ProdList").Range("L9:L28"))
col = cRow * 7
'i = 13 'Column M
For i = 13 To col Step 7
    Range(Cells(12, i + 1), Cells(63, i + 1)).Value = Range(Cells(12, i), Cells(63, i)).Value
Next
End Sub