CODEWITHSUNDEEP
excelvba
MEC
MEC

Reputation: 233

Copy Data to next empty column

I would like to copy data to another worksheet but I want the macro to determine the next empty column to copy the data to. So starting at rang C5, I want it to determine what is the next empty column (D5, or E5 or F5, up to M5), once it determines that, I want it to copy the data from the "Main" sheet ranges specified below. I know that the column value has to be a variable passed to the row value for this to work but have not been able to figure out how. There is some samples all over the forums but none that I have been able to adapt.

Sub Copy_To_Borrower_DBase()

Worksheets("Borrower Database").Range("D5").Value = 
Sheets("Main").Range("F5").Value 'Borrower Name
Worksheets("Borrower Database").Range("D6:D8").Value = 
Sheets("Main").Range("G6:G8").Value 'Income, Credit and Car Pmt
Worksheets("Borrower Database").Range("D9:D10").Value = 
Sheets("Main").Range("G11:G12").Value 'Borrower Name
Worksheets("Borrower Database").Range("D11").Value = 
Sheets("Main").Range("G15").Value 'Reserves
Worksheets("Borrower Database").Range("D12").Value = 
Sheets("Main").Range("D15").Value 'Credit Score
Worksheets("Borrower Database").Range("D13").Value = 
Sheets("Main").Range("D14").Value 'Rate
Worksheets("Borrower Database").Range("D14").Value = 
Sheets("Main").Range("C14").Value 'Discount Point
Worksheets("Borrower Database").Range("D15").Value = 
Sheets("Main").Range("D17").Value 'More than 1 Borrower

End Sub

Upvotes: 0

Views: 218

Answers (2)

GMalc
GMalc

Reputation: 2628

just cleaned up your code and added the first empty column (lCol).

Sub Copy_To_Borrower_DBase()
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = ThisWorkbook.Sheets("Main")
Set ws2 = ThisWorkbook.Sheets("Borrower Database")

Dim lCol As Long

    With ws2
    lCol = .Cells(5, .Columns.Count).End(xlToLeft).Offset(, 1).Column
        .Cells(5, lCol).Value = ws1.Range("F5").Value 'Borrower Name
        .Range(.Cells(6, lCol), .Cells(8, lCol)).Value = ws1.Range("G6:G8").Value 'Income, Credit and Car Pmt
        .Range(.Cells(9, lCol), .Cells(10, lCol)).Value = ws1.Range("G11:G12").Value 'Borrower Name
        .Cells(11, lCol).Value = ws1.Range("G15").Value 'Reserves
        .Cells(12, lCol).Value = ws1.Range("D15").Value 'Credit Score
        .Cells(13, lCol).Value = ws1.Range("D14").Value 'Rate
        .Cells(14, lCol).Value = ws1.Range("C14").Value 'Discount Point
        .Cells(15, lCol).Value = ws1.Range("D17").Value 'More than 1 Borrower
    End With
End Sub

Upvotes: 0

Scott Holtzman
Scott Holtzman

Reputation: 27239

The question and data are little hard to follow since they don't seem to match 100%, but I have provided this sample to show you how to find the next free colum:

Dim sourceSheet as Worksheet
Dim destSheet as Worksheet

Set sourceSheet = Worksheets("source")
Set destSheet = Worksheets("dest")

getNextAvailableColumn(destSheet).Value = sourceSheet.Range("A1").Value
getNextAvailableColumn(destSheet).Resize(sourceSheet.Range("A2:B10").Rows.Count,sourceSheet.Range("A2:B10").Columns.Count).Value = sourceSheet.Range("A2:B10").Value

Function getNextAvailableColumn(ws as Worksheet) as Range

    Dim nextCol as Range
    With destSheet
        nextCol = .cells(1,.Columns.Count).End(xlLeft).Offset(,1)
    End With

    Set getNextAvailableColumn = nextCol

End Function

Upvotes: 2

Related Questions