Macro to move paste cell value one cell to the right if target cell has value already

[Vonsteiner]

Hello everyone,
I have a question similar to the previous thread "Macro to copy cell value down until next non-blank cell".

I need to be able to paste a specific value from another workbook into a cell, but if the target cell has a value already then I need to move one cell to the right and paste there and so on. In some cases there might be 20 cells already with values, so I would need the data pasted into the 21 cell.

Workbooks.Open Filename:="C:\Users\XXXXXXX\M2MCD.xlsx"
    
Dim cdb As Workbook
Dim cds, cdc As Worksheet
Dim erSubConn, erSubDisco, erIntConn, erIntDisco As Integer
Dim wrSubConn, wrSubDisco, wrIntConn, wrIntDisco As Integer
Dim irSubConn, irSubDisco, irIntConn, irIntDisco As Integer
Dim orSubConn, orSubDisco, orIntConn, orIntDisco As Integer
Dim erVidConn, erVidDisco, erPhoConn, erPhoDisco As Integer
Dim wrVidConn, wrVidDisco, wrPhoConn, wrPhoDisco As Integer
Dim irVidConn, irVidDisco, irPhoConn, irPhoDisco As Integer
Dim orVidConn, orVidDisco, orPhoConn, orPhoDisco As Integer

Dim ecSubConn, ecSubDisco, ecIntConn, ecIntDisco As Integer
Dim wcSubConn, wcSubDisco, wcIntConn, wcIntDisco As Integer
Dim icSubConn, icSubDisco, icIntConn, icIntDisco As Integer
Dim ocSubConn, ocSubDisco, ocIntConn, ocIntDisco As Integer
Dim ecVidConn, ecVidDisco, ecPhoConn, ecPhoDisco As Integer
Dim wcVidConn, wcVidDisco, wcPhoConn, wcPhoDisco As Integer
Dim icVidConn, icVidDisco, icPhoConn, icPhoDisco As Integer
Dim ocVidConn, ocVidDisco, ocPhoConn, ocPhoDisco As Integer

Dim rConn, rDisco As Integer
Dim cConn, cDisco As Integer
Dim iConn, iDisco As Integer


Set cdb = Workbooks("M2MCD")
Set cds = cdb.Worksheets("Res DIV Summary")
Set cdc = cdb.Worksheets("Comm DIV Summary")

'Resi

erSubConn = cds.Cells(3, 4).Value
wrSubConn = cds.Cells(4, 4).Value
irSubConn = cds.Cells(5, 4).Value + cds.Cells(6, 4).Value
orSubConn = cds.Cells(7, 4).Value

erIntConn = cds.Cells(3, 10).Value
wrIntConn = cds.Cells(4, 10).Value
irIntConn = cds.Cells(5, 10).Value + cds.Cells(6, 10).Value
orIntConn = cds.Cells(7, 10).Value

erVidConn = cds.Cells(3, 7).Value
wrVidConn = cds.Cells(4, 7).Value
irVidConn = cds.Cells(5, 7).Value + cds.Cells(6, 7).Value
orVidConn = cds.Cells(7, 7).Value

erPhoConn = cds.Cells(3, 20).Value
wrPhoConn = cds.Cells(4, 20).Value
irPhoConn = cds.Cells(5, 20).Value + cds.Cells(6, 20).Value
orPhoConn = cds.Cells(7, 20).Value

erSubDisco = cds.Cells(3, 5).Value
wrSubDisco = cds.Cells(4, 5).Value
irSubDisco = cds.Cells(5, 5).Value + cds.Cells(6, 5).Value
orSubDisco = cds.Cells(7, 5).Value

erIntDisco = cds.Cells(3, 11).Value
wrIntDisco = cds.Cells(4, 11).Value
irIntDisco = cds.Cells(5, 11).Value + cds.Cells(6, 11).Value
orIntDisco = cds.Cells(7, 11).Value

erVidDisco = cds.Cells(3, 8).Value
wrVidDisco = cds.Cells(4, 8).Value
irVidDisco = cds.Cells(5, 8).Value + cds.Cells(6, 8).Value
orVidDisco = cds.Cells(7, 8).Value

erPhoDisco = cds.Cells(3, 21).Value
wrPhoDisco = cds.Cells(4, 21).Value
irPhoDisco = cds.Cells(5, 21).Value + cds.Cells(6, 21).Value
orPhoDisco = cds.Cells(7, 21).Value

'Commercial

ecSubConn = cdc.Cells(3, 4).Value
wcSubConn = cdc.Cells(4, 4).Value
icSubConn = cdc.Cells(5, 4).Value + cdc.Cells(6, 4).Value
ocSubConn = cdc.Cells(7, 4).Value

ecIntConn = cdc.Cells(3, 10).Value
wcIntConn = cdc.Cells(4, 10).Value
icIntConn = cdc.Cells(5, 10).Value + cdc.Cells(6, 10).Value
ocIntConn = cdc.Cells(7, 10).Value

ecVidConn = cdc.Cells(3, 7).Value
wcVidConn = cdc.Cells(4, 7).Value
icVidConn = cdc.Cells(5, 7).Value + cdc.Cells(6, 7).Value
ocVidConn = cdc.Cells(7, 7).Value

ecPhoConn = cdc.Cells(3, 20).Value
wcPhoConn = cdc.Cells(4, 20).Value
icPhoConn = cdc.Cells(5, 20).Value + cdc.Cells(6, 20).Value
ocPhoConn = cdc.Cells(7, 20).Value

ecSubDisco = cdc.Cells(3, 5).Value
wcSubDisco = cdc.Cells(4, 5).Value
icSubDisco = cdc.Cells(5, 5).Value + cdc.Cells(6, 5).Value
ocSubDisco = cdc.Cells(7, 5).Value

ecIntDisco = cdc.Cells(3, 11).Value
wcIntDisco = cdc.Cells(4, 11).Value
icIntDisco = cdc.Cells(5, 11).Value + cdc.Cells(6, 11).Value
ocIntDisco = cdc.Cells(7, 11).Value

ecVidDisco = cdc.Cells(3, 8).Value
wcVidDisco = cdc.Cells(4, 8).Value
icVidDisco = cdc.Cells(5, 8).Value + cdc.Cells(6, 8).Value
ocVidDisco = cdc.Cells(7, 8).Value

ecPhoDisco = cdc.Cells(3, 21).Value
wcPhoDisco = cdc.Cells(4, 21).Value
icPhoDisco = cdc.Cells(5, 21).Value + cdc.Cells(6, 21).Value
ocPhoDisco = cdc.Cells(7, 21).Value

Workbooks.Open Filename:="C:\Users\XXXXXXX\2021 Budget Connects and Disconnects - New Format.xlsx"

Set summ = Workbooks("2021 Budget Connects and Disconnects - New Format")

summ.Activate

summ.Sheets("East Daily Data").Cells(11, 8).Value = erSubConn + ecSubConn

Basically I need to input the value (the last line of code above) into cell AJ9 or AJ10, etc...of the workbook and sheet named above. Any help would be greatly appreciated. Thank you.

 

[igold]

I am not sure what all the code which you have provided has to do with your basic question, something like this would put the value "12" to the right of the last column of data on Row 9.

Sub LastColumn()
    Dim lCol As Long
    lCol = Cells(9, Columns.Count).End(xlToLeft).Column + 1
    Cells(9, lCol).Value = 12
End Sub

Additionally, you may not realize it, but this line of code of yours

Dim erSubConn, erSubDisco, erIntConn, erIntDisco As Integer

is declaring erSubConn as a variant, erSubDisco as variant, erIntConn, as a variant and then erIntDisco as Interger. You must declare each variable separately. You can do it on the same line but each must declared. The same would hold true with this line of yours

Dim cds, cdc As Worksheet

it declares cds as a variant and cdc as Worksheet. I could be wrong but I don't think that is your intention.

 

[Vonsteiner]

Thanks igold. The value I need to input is different each day. All those integers change each day and I need to capture the value each day as a separate value in a separate cell. This is why I need the code to move to the next empty cell in the row.

 

[igold]

You are welcome. Hopefully you can take what I provided and work it in to fit your needs.