• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro to Loop Columns

T

Tom90

Member
Hi Can anyone help, I have been going round and round trying to adapt what Marcos I had to suit but with no success and I am now out my depth a bit so need a push in the right direction if possible.

Background, I have a file that I update every month (File attached), you see that each column has data in in with the current month has a formula in it (P7) what I want to do is at the end of each month is have a macro that will copy the formula from one column to the next then go back and copy and paste values to remove the formula from the column that I have just copy form. This will put my formula in the new column/month, that part is should be ok but now the hard part is that I have 38 blocks of columns and as it moves every month I need to be able for it to pick up the range so that I don’t have to change the macro ranges every month

Above what I want to do is copy all the P7 and move it to P8 then go back to P7 and copy paste values to remove the formula about 38 times the next month I will do the same but for P8 to P9 so you can see how I need to be able to pick up the ranges without have to rewrite the macro every month. Don’t know if there is a way round but if there I am sure that you are out there somewhere on the site

Thanks Tom90
 

Attachments

  • Capture.PNG
    Capture.PNG
    20.6 KB · Views: 12
This should help get us started. Will depend a little on your exact setup, but should be able to tweak it.
Code: 
Sub CopyPasteLock()
Dim lastCol As Long
Dim lastRow As Long
Dim curMonth As Range
 
'Making an assumption here
'Assume that first row of data is in row 2
'and that there is data all the way from col A
'to the curr month data
With Range("A2")
    lastCol = .End(xlToRight).Column
    lastRow = .End(xlDown).Row
End With
 
'Copy the last month range and paste as needed
Set curMonth = Range(Cells(2, lastCol), Cells(lastRow, lastCol))
curMonth.Copy
curMonth.Offset(0, 1).PasteSpecial (xlPasteFormulas)
curMonth.PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
 
 
End Sub
 
Thanks Luke,
Thats works fine but the one thning that I am having trouble with if you look at the attachment you will see that I need it to loop/ move to the set of monthly data, and as I said I have about 38 blocks of monthly data, have you any idea how to move/loop it along

Tom90
 
Ah, sorry about that. I did not understand that portion before. In that case, perhaps this will do it.
Code: 
Sub CopyPasteLock()
Dim x As Range
Dim a As Range
'First, find all the formulas
 
Set x = Range("1:100").SpecialCells(xlFormulas)
 
Application.ScreenUpdating = False
'Loop through each block of formulas and copy them 1 column to the right
For Each a In x.Areas
    a.Copy
    a.Offset(0, 1).PasteSpecial (xlPasteFormulas)
    a.PasteSpecial (xlPasteValues)
    Application.CutCopyMode = False
Next a
Application.ScreenUpdating = False
End Sub
 
Thanks Again Luke,
Managed to get things working with the file that I had attached, as it worked well I am using it on another file that I am doing work on but there is a differents where the first code that you sent me looks for the last column/row and copy and pastes it, how would I set it up to find the last two columns and copy and paste them to the next two columns, so instead of the code that you provided looking for the last column and copying and paste it to next column I want it to do the last two columns and copy and paste it to the next to columns.

Tom90
 
Assuming both columns have formulas, just need to change this line to have a 2 column offset:
a.Offset(0, 2).PasteSpecial (xlPasteFormulas)
 
Luke, I had alreardy did that but it only copys the one column and move to two places what I need to do with this file is find the two last columns with formulas in it then copy and paste them to the next two columns.

Tom
 
Just to clarify, if we have columns A:Z, and A through D have static values, E:F have formulas, you want those formulas copies to G:H?
 
Hmm. Tom, try running this code with a debug message, and let me know what the message box says. I'm not exactly sure atm why it's not working.
Code: 
Sub CopyPasteLock()
Dim x As Range
Dim a As Range
'First, find all the formulas
 
Set x = Range("1:100").SpecialCells(xlFormulas)
 
Application.ScreenUpdating = False
'Loop through each block of formulas and copy them 1 column to the right
For Each a In x.Areas
    a.Copy
    'Debug message
    MsgBox "I have selected range: " & a.Address & vbNewLine & _
            "I will paste to: " & a.Offset(0, 2).Address
    a.Offset(0, 2).PasteSpecial (xlPasteFormulas)
    a.PasteSpecial (xlPasteValues)
    Application.CutCopyMode = False
Next a
Application.ScreenUpdating = False
End Sub
 
Hi Luke, sorry for the late reply but have been at a meeting all day so this is my catch up time. I run the code and it worked fine did what it said it was going to do, I like the touch with the mesage box as you can check that it is the correct columns that you are going to paste into, so once again you have earend your Excel Ninja thanks for your help :cool:

Tom
 
No worries Tom, just glad that it got figured out finally. Using MsgBox and Debug.Print are a couple nice ways to be able to check on your code and make sure things are doign what you think they are doing.
 
Hi Abhi,
I'm not quite sure what you mean. Could you give some range references as an example, or start a new thread with uploaded workbook?
 
Say in case my data runs from Col.A upto Col.J (might vary as per the data extracts), I would like to loop through column headers to find specific match from Col.J (or any variable column as per data) and run upto Col.A..
 
I'm still confused. The original thread was about pasting formulas from one column to next and pasting the old values. How do you paste the values from col J to (somewhere) in your workbook?
 
You must log in or register to reply here.
Back
Top