Copy and paste certain cells based on certain conditions

[Ash133]

Hi,

I am very new to VBA and would be happy if someone was to refer me to another post where this was answered or where I can get more information so I can learn more however I have the following scenario that I am hoping to solve.

I have two worksheets Invoice Summary and Outstanding Accounts.

If cell D4 = OUTSTANDING then I want to be able to copy cells A4 to I4 to the Outstanding Accounts tab and paste it into row A4. I have further data in the Outstanding Accounts tab past column I so can not use a copy row function.

I would also like if possible for each time the macro is run that it look at what the next blank row is on Outstanding Accounts tab and paste any new data below that so for example if 5 rows came in the first run then it would post in A9 the next run.

Thanks so much in advance

 

[My Aswer Is This]

You said:
If cell D4 = OUTSTANDING then I want to be able to copy cells A4 to I4 to the Outstanding Accounts tab and paste it into row A4.

So you want the range pasted into Range("A4:A12") is that correct? And next time start pasting in A13.
Is this correct. Do we paste any formulas and formatting also or just values?

 

[My Aswer Is This]

Just to let you know why I'm a little confused is you said: "paste it into row A4"

We have no row A4

In Excel we have rows like Row(1) Row(10) etc.
And we have columns like Columns ("A") and Columns("C")
And we have Ranges like ("A4") and Range("C6")

But no Row(A4)

 

[Ash133]

Just to let you know why I'm a little confused is you said: "paste it into row A4"

We have no row A4

In Excel we have rows like Row(1) Row(10) etc.
And we have columns like Columns ("A") and Columns("C")
And we have Ranges like ("A4") and Range("C6")

But no Row(A4)

Sorry I mean that I would like it to paste into Row 4 starting from cell A4...

 

[My Aswer Is This]

Try this vba script.
Read the script and make sure those sheet names are correct. If not modify to your needs.

Sub Copy_Data()
Application.ScreenUpdating = False
Dim Lastrow As Long
Lastrow = Sheets("Outstanding Accounts").Cells(Rows.Count, "A").End(xlUp).Row + 1
If Sheets("Invoice Summary").Range("D4").Value = "OUTSTANDING" Then
Sheets("Invoice Summary").Range("A4:I4").Copy Sheets("Outstanding Accounts").Cells(Lastrow, 1)
End If
Application.ScreenUpdating = False
End Sub

 

[My Aswer Is This]

Now if you wanted this to happen automatically when you enter the value "OUTSTANDING" into range "D4"
Of sheet named "Invoice Summary<strike></strike>" you could use this script.

Put this script in the sheet named"Invoice Summary<strike></strike>"

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

<strike></strike>

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D4")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim ans As Long
Dim Lastrow As Long
Lastrow = Sheets("Outstanding Accounts").Cells(Rows.Count, "A").End(xlUp).Row + 1
If Target.Value = "OUTSTANDING" Then
Range(Cells(4, 1), Cells(4, 9)).Copy Sheets("Outstanding Accounts").Range("A" & Lastrow)
End If
End If
End Sub