CODEWITHSUNDEEP
excelvba
D. Ross
D. Ross

Reputation: 23

Copying a column from one workbook to another, finding the first empty column in the target worksheet

I have a workbook that tracks long-term trends in experimental data. Independant workbooks for various experiments generate 2-3 columns of data that need to be copied to this tracking workbook. I would probably go for something like this:

Workbooks(source_book).Worksheets(source_sheet).Range(Source_Range_Variable).Copy
Workbooks(target_book).Worksheets(target_sheet).Range(Target_Range_Variable).PasteSpecial xlPasteValues

My issue is that I have no idea how to find "Target_Range_Variable" which would be the first empty column in the target sheet. I have some ideas on how to set "Source_Range_Variable" because it can be one or two columns by using nested if's to find if columns are populated and going from there. Inelegant for sure.

Sorry I don't have any real code, but I truly don't know how to start. There are several hundred columns already, and there will be several hundred more. If it wasn't so big, I would brute force my way by nesting if statements until it finds an empty column.

Note: I'm very inexperienced with this, forgive me if I miss anything obvious.

Upvotes: 2

Views: 23

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149305

You can use Cells.Find method to find the last column. Here is an example (Untested)

Sub Sample()
    '
    '~~> Rest of code
    '
    
    Workbooks(source_book).Worksheets(source_sheet).Range(Source_Range_Variable).Copy
    
    Dim LastCol As Long
    Dim ColName As String
    
    '~~> Get the last column number
    LastCol = LastColumn(Workbooks(target_book).Worksheets(target_sheet))
    
    '~~> Column number to column letter
    ColName = Split(Cells(, LastCol).Address, "$")(1)
    
    '~~> Your final range. Ex "A1"
    Target_Range_Variable = ColName & 1
    
    Workbooks(target_book).Worksheets(target_sheet).Range(Target_Range_Variable).PasteSpecial xlPasteValues
End Sub

Private Function LastColumn(wks As Worksheet) As Long
    If Application.WorksheetFunction.CountA(wks.Cells) <> 0 Then
        LastColumn = wks.Cells.Find(What:="*", _
                     After:=wks.Range("A1"), _
                     Lookat:=xlPart, _
                     LookIn:=xlFormulas, _
                     SearchOrder:=xlByColumns, _
                     SearchDirection:=xlPrevious, _
                     MatchCase:=False).Column
    Else
        LastColumn = 1
    End If
End Function

Upvotes: 2

Related Questions