Microsoft Excel - Code to paste in adjacent column if data in current

Hello

Would be glad of some help in creating a code to copy and paste data within a workbook for comparison.  I have created the code below which works and does what I want it to do, but I want to add to this so the macro will actually paste in the column to the right hand side of this if there is already data in this column...  and repeatedly, so you could potentially have the data pasted in a long continuous line as it pastes in the next clear column to the right...


Sub budget()
'
' budget Macro
'

'
    Sheets("Dashboard").Range("C5").Copy
    Sheets("Budget Setting").Range("E4").PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
      SkipBlanks:=False, Transpose:=False
    Sheets("Summary").Range("BudgetCategoryLookup[[#All],[Column1]]").Copy
    Sheets("Budget Setting").Range("E5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
    Range("A1").Select
End Sub


Hope this makes sense.  Look forward to hearing back.
Many thanks
Peter

Hello pete,
Just to be clear, you would like to find the first empty column in row4 and use that as a reference for your pastes.
Harry

Hello Harry

No the reference for the paste is first empty column after column D.  So first paste E4 and E5 but if not empty paste in F4 and F5 but if not empty paste in G4 and G5 and so on.....

Many thanks
Peter

Hi pete,
This assumes that there will be something in ColumnD.  For some reason it gave some problems trying to go the other way.  You will have to change the sheets names and range names (highlighted) back to match your original code as I changed them to test the code.

Option Explicit

Sub budget()
'
' budget Macro
'

'
    Dim rngFind As Range
    Dim wsTwo As Worksheet

    Set wsTwo = Worksheets("Sheet2")

    With wsTwo
      Set rngFind = Range("ZZ4").End(xlToLeft).Offset(0, 1)
    End With

    Sheets("Sheet1").Range("C5").Copy
    rngFind.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Sheets("Sheet1").Range("D5").Copy
    rngFind.Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("A1").Select
End Sub

Regards
Harry

Thanks Harry that is helpful - it works.  But do you know why it returns back to column D when it reaches the set Range column (although there is data in row 4 column D)?

Also how would I add an If to this -  i.e. If the area to paste to already has data in Then clear contents first.....

Appreciate your help.
Regards
Peter

Hi pete,
Not sure that I understand what you mean on both counts.
On the first one, I am not sure what columnD you are talking about and what you mean by 'it returns back to'.  However, on reflection I have modified my code a bit so that it works regardless of where the macro is started from and also done away with the copy/paste.  Same changes in sheets and ranges to get back to your original as before.

Option Explicit

Sub budget()
'
' budget Macro
'

'
    Dim rngFind As Range
    Dim wsTwo As Worksheet

    Set wsTwo = Worksheets("Sheet2")

    With wsTwo
      Set rngFind = wsTwo.Range("ZZ4").End(xlToLeft).Offset(0, 1)
    End With
    rngFind.FormulaR1C1 = Sheets("Sheet1").Range("C5").FormulaR1C1
    rngFind.Offset(1, 0).Value = Sheets("Sheet1").Range("D5").Value
    Range("A1").Select
End Sub

On the second, I thought that the idea was to find cells without entries and to populate those - ergo there will be nothing to clear!  Perhaps if you could try the explanation again.
Regards
Harry

Harry

Thanks, that is working better however

rngFind.Offset(1, 0).Value = Sheets("Sheet1").Range("D5").Value

I need this to read values in cells D4 to D16 on Summary tab and have adjusted like this

rngFind.Offset(1, 0).Value = Sheets("Summary").Range("D4:D16").Value

but it doesn't seem to work, any ideas?

Pete

Hi pete,
The two ranges have to be the same size.  This code would allow you to use a named range for your source if you wanted to.  Same cavaets about changing ranges and variables to suit your workbook.

Option Explicit

Sub budget()
'
' budget Macro
'

'
    Dim rngFind As Range
    Dim wsTwo As Worksheet
    Dim intRows As Integer

    Set wsTwo = Worksheets("Sheet2")
    intRows = Sheets("Sheet1").Range("D5:D13").Rows.Count
    
    With wsTwo
      Set rngFind = wsTwo.Range("ZZ4").End(xlToLeft).Offset(0, 1)
    End With
    rngFind.FormulaR1C1 = Sheets("Sheet1").Range("C5").FormulaR1C1
    rngFind.Resize(intRows, 1).Offset(1, 0).Value = Sheets("Sheet1").Range("D5:D13").Value
    Range("A1").Select
End Sub

Regards
Harry

Harry

That is working fine and many thanks for your help!

Regards
Peter