loop copy

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Loop - Copy & Paste

Loop - Copy & Paste

resolvedResolved · Low Priority · Version 2007

Emma has attended:
Excel VBA Intro Intermediate course

Loop - Copy & Paste

I am trying to do a Macro that looks at column G, if the cell value is "Yes" or "No" it copies the whole line and pastes in the relevant sheet "Approved/Rejected".

I have managed to get it to do this, but my formula always pastes into row 2. This is a report that I will want to start at row 2, but also, run and start the next available line if line 2 is full.

I remember doing this on the course, but my notes don't cover how to do this

Sub FindYesNo()
Sheets("Buyer accepted").Select
Range("G2").Select
Do Until ActiveCell = ""
If ActiveCell = "Yes" Then
ActiveCell.EntireRow.Select
Selection.Copy
Sheets("Buyer Approved").Select
Range("A1").Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste

Sheets("Buyer accepted").Select
ActiveCell.Offset(0, 6).Select

ElseIf ActiveCell = "No" Then
ActiveCell.EntireRow.Select
Selection.Copy
Sheets("Buyer Rejected").Select
Range("A1").Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste

Sheets("Buyer accepted").Select
ActiveCell.Offset(0, 6).Select

Else
ActiveCell.Font.Color = RGB(128, 0, 128)

End If
ActiveCell.Offset(1, 0).Select

Loop

End Sub

Many Thanks
Emma

RE: Loop - Copy & Paste

Hi Emma

Thanks for getting in touch. The key lines in your code that does this is:

Sheets("Buyer Approved").Select
Range("A1").Select
ActiveCell.Offset(1, 0).Select

and

Sheets("Buyer Rejected").Select
Range("A1").Select
ActiveCell.Offset(1, 0).Select

These will both always go to A1 and then down one cell into A2.

If you replace those lines with

Sheets("Buyer Rejected").Select
Range("a1").End(xlDown).Offset(1, 0).Select

That will jump to the next available row. Do watch out that if row 2 is empty it will jump all the way to the end of the sheet.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Wed 18 Dec 2013: Automatically marked as resolved.

 

Training courses

 

Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

Using Alt in Save Dialog Box

When you are saving (or opening) a file, try these;
(Note the numbers are on the keyboard not the numeric keypad)
1. Go to previous folder Alt+1
2. Go up one folder level Alt+2
3. Search the Web Alt+3
4. Delete selected file Alt+4 or Delete
5. Create a new folder Alt+5
6. Cycle through all views Alt+6 repeatedly
7. Display the Tools menu Alt+7

View all Excel hints and tips


Server loaded in 0.08 secs.