CODEWITHSUNDEEP
excelvba
user3782816
user3782816

Reputation: 171

Iterating over columns in Excel

I wrote a simple VBA script that opens all of the .xls files in a given folder, and copies Column A. My desire is to paste that information into a unique column of my workbook. I can't seem to iterate over columns very easily, so in the loop below I am just pasting everything into the same column. I just can't figure out how to update which column I want to paste into. If I was iterating over rows it would be simple it would be Range("A"&LastRow), but columns seem to be trickier. In the loop below everything gets pasted into Column A of the destination workbook (variable wkbDest).

Dim wkbDest As Workbook
Dim wkbSource As Workbook 'Source workbooks
Set wkbDest = ThisWorkbook
Const strPath As String = "C:\SecretPath\"
Const sheetName As String = "MacroResults"
Const xlsPattern As String = "*.xls"
ChDir strPath
strExtension = Dir(xlsPattern)
Dim lastColumn As Long 'variable that will be the counter to iterate over the columns
lastColumn = 0
wkbDest.Sheets(sheetName).Cells.Clear
wkbDest.Sheets(sheetName).Range("A1").Value = "Extraction Results"
Do While strExtension <> ""
    Set wkbSource = Workbooks.Open(strPath & strExtension)
    With wkbSource
    lastColumn = lastColumn + 1
    wkbSource.Sheets("Test_1").Columns("A:A").Copy

I have tried wkbDest.Sheets(sheetName).Range("A1").OffSet(0,lastColumn).PasteSpecial and instead of Range Columns(lastColumn), but nothing seems to work.

    wkbDest.Sheets(sheetName).Range("A1").PasteSpecial xlPasteValues
    .Close savechanges:=True
    End With
    strExtension = Dir()
Loop

Upvotes: 0

Views: 52

Answers (1)

donPablo
donPablo

Reputation: 1959

add this to your code, replacing the paste line.

' convert the lastColumn into its letter, and then use it
Dim vArr as Variant, Col_Letter as string
vArr = Split(Cells(1, lastColumn ).Address(True, False), "$")
Col_Letter = vArr(0)

wkbDest.Sheets(sheetName).Range(Col_Letter & "1").PasteSpecial xlPasteValues

Upvotes: 1

Related Questions