VBA code to copy a column as value

[onche414]

Hello,

I would like to create a VBA code which will go through a folder where there are excel files and copy as value the column U of the first sheet of each excel file to the column V.

I came up with this for now but I am getting an error and this code is more likely to copy the column U as value on the column U itself while I want to copy it on column V

Sub OpenAndPerformMacros()
Dim strF As String, strP As String
Dim wb As Workbook
Dim ws As Worksheet


'Edit this declaration to your folder name
strP = "Path\New folder"


strF = Dir(strP & "\*.xlsx") 'Change as required


Do While strF <> vbNullString


    Set wb = Workbooks.Open(strP & "\" & strF)
    Set ws = wb.Sheets(1).Column("U") 
ws.Cells.Copy
ws.Cells.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
    wb.Close True
    
    strF = Dir()
Loop

End Sub

 

[Alex Blakenburg]

I only tested the copy part but give this a try:

Sub OpenAndPerformMacros()
Dim strF As String, strP As String
Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range

'Edit this declaration to your folder name
strP = "Path\New folder"

strF = Dir(strP & "\*.xlsx") 'Change as required

Do While strF <> vbNullString
    Set wb = Workbooks.Open(strP & "\" & strF)
    Set ws = wb.Sheets(1)
    Set rng = Intersect(ws.Columns("U"), ws.UsedRange)
    rng.Copy
        rng.Offset(, 1).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    wb.Close True
    
    strF = Dir()
Loop

End Sub

 

[rlv01]

Perhaps something like this .

Sub OpenAndPerformMacros()
    Dim strF As String, strP As String
    Dim wb As Workbook
    Dim ws As Worksheet
    
    
    'Edit this declaration to your folder name
    strP = "Path\New folder"
    strF = Dir(strP & "\*.xlsx") 'Change as required
    
    Do While strF <> vbNullString
        Set wb = Workbooks.Open(strP & "\" & strF)
        Set ws = wb.Sheets(1)
        ws.Columns("V").Value = ws.Columns("U").Value
        wb.Close True
        strF = Dir()
    Loop
End Sub

 

[Alex Blakenburg]

@rlv01 - you get a noticeable delay by doing the whole column. How about adding something like this.

        With Intersect(ws.Columns("U"), ws.UsedRange)
            .Offset(, 1).Value = .Value
        End With

 

[rlv01]

@rlv01 - you get a noticeable delay by doing the whole column. How about adding something like this.

You're right of course. I was just trying to keep things simple for the OP.