range
Train Strikes - 7th, 8th & 9th May 2024
Hide
Contact Us About Resources Consent preferences Sign in

TrustPilot

starstarstarstarstar Excellent

Request Callback We will call you back  0207 987 3777 Call for assistance Your Basket Basket is empty
Callback Sign in Basket
Menu

Call us on 0207 987 3777 or Search our website

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Range

Range

resolvedResolved · High Priority · Version 2010

chandni has attended:
Excel VBA Intermediate course

Range

I have created a Macro, I have to manually add the information in range("C8")in order for the macro to pick up information in another tab. However I realised that if the information in C8 does not exist the active worksheet then everything in the worksheet is copied and pasted to a new worksheet. How do I stop everything from being copied and pasted if the specific information in c8 is not present in the active sheet?

ActiveWorkbook.Worksheets("Filters for template creation").Activate

Supplier = Range("C8").Value

ActiveWorkbook.Worksheets("Lookup").Activate

ActiveSheet.ListObjects("Table3").Range.AutoFilter field:=1, Criteria1:=Supplier

RE: Range

Hi Chandni,


Thank you for the forum question.

I have tested your code and it is working fine. When I tested it and C8 was blank it just didn't show any information in Table1. There is nothing in the code which copy and paste data.

If you want to test if you have C8 in table1 before you filter it, you must loop the table and use a decision code.

I hope this can help you in the right direction.


Kind regards

Jens Bonde
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

RE: Range

Hi Jen,

The code I sent was half, please see below:



If MsgBox("Are you sure that you wish to action this macro ?", vbYesNo, "Confirm") = vbNo Then
GoTo EndProcessedLookup
End If

Worksheets("Lookup").ListObjects("Table3").Range.AutoFilter
Worksheets("Lookup").ListObjects("Table3").Range.AutoFilter

ActiveWorkbook.Worksheets("Filters for template creation").Activate

Supplier = Range("C8").Value

ActiveWorkbook.Worksheets("Lookup").Activate

ActiveSheet.ListObjects("Table3").Range.AutoFilter field:=1, Criteria1:=Supplier


'Open the template
Workbooks.Open ("http://.xlsx")

'Copy data and clear template of data
Workbooks("1._Residual_address_log_trial.xlsm").Worksheets("Lookup").Activate

Range(Cells(3, 1), Cells(3, 8)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Workbooks("R_Template.xlsx").Worksheets("Sheet1").Activate

Cells(3, 1).PasteSpecial xlPasteValues
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Workbooks("1._Residual_address_log_trial.xlsm").Worksheets("Lookup").Activate

Workbooks("R_Template.xlsx").Worksheets("Sheet1").Activate

Cells(3, 1).PasteSpecial xlPasteValues

Workbooks("1._Residual_address_log_trial.xlsm").Worksheets("Lookup").Activate

Range(Cells(3, 17), Cells(3, 22)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Workbooks("R_Template.xlsx").Worksheets("Sheet1").Activate

Cells(3, 9).PasteSpecial xlPasteValues
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy


Workbooks("1._Residual_address_log_trial.xlsm").Worksheets("Lookup").Activate

Worksheets("Lookup").ListObjects("Table3").Range.AutoFilter
Worksheets("Lookup").ListObjects("Table3").Range.AutoFilter

Workbooks("R_Template.xlsx").Worksheets("Sheet1").Activate

EndProcessedLookup:

End Sub

RE: Range

Hi Chandni,


I have created an example, I hope makes sense.

Please find attached workbook.

If you cannot see the attachment please refresh your browser.

Kind regards

Jens Bonde
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

Attached files...

StopIfNotPresent.xlsm

RE: Range

Hi Jen,

Thank you for sending the example. unfortunately it doesn't solve my problem.

I want to know how to create add a code that stops if the data is not present e.g.

if i added "car" in C8 but this is not present in table 3, i want a code that will tell me that this data is not present in table3.

At the moment if i add the wrong information in c8 and the information in not present in table three than the macro is pasting all the information in an excel fill which is not needed. Therefore i want to find out how i can create a code that will tell me that the information add in C8 is not present in table3.

I hope that makes sense.

Thanks for your help

Chandni

RE: Range

Hi Chandni,

I found a tiny mistake in my code, but if you replace the code in my example with the code below, you can see it is working. I have added a message box which will tell you if the supplier is not in the table, when you run the code.

You will just need to amend to you workbook.

If you want Excel to tell you when you add information in C8, that the supplier is not present in the table, without you need to execute code, you can create a worksheet change event, but this is complicated and you will need knowledge of Arrays and Events.

The code:

Sub StopIfNotPresent()



ActiveWorkbook.Worksheets(1).Activate

Supplier = Range("b1").Value

Sheets("Sheet2").Select
Range("D2").Select
Do Until ActiveCell.Value = ""

If ActiveCell.Value = Supplier Then GoTo Continuecode

ActiveCell.Offset(1, 0).Select

Loop

MsgBox "The value is not in the list"
Exit Sub

Continuecode:


ActiveWorkbook.Worksheets(2).Activate

ActiveSheet.ListObjects("Table1").Range.AutoFilter field:=1, Criteria1:=Supplier

End Sub





Kind regards

Jens Bonde
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

RE: Range

Hi,

Thanks for your help, with a few tweaks I was able to make it works.

Thank you

Chandni

 

Training courses

 

Recommend training98.7 %
recommend us
More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

See also:
Hints and tips
Forum topics

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 articles

Please browse our web site to find out more about
training ms excel advanced and other training courses.

Excel tip:

Hiding Formulae in the Formula Bar

It is possible to protect the contents of a cell reference(s) from amendment by applying cell protection. The contents of the cell reference can also be stopped from displaying in the formula bar.

Step 1: Select Format > Cells > Protection.

Step 2: Tick Hidden option. Ensure Locked is ticked

Step 3: Select Tools > Protection > Protect Sheet

(Ensure "Protect worksheet and contents of locked cells" is ticked)


View all Excel hints and tips


Request Callback

We will call you back

0207 987 3777

Call for assistance

Security verified Microsoft Partner

Formerly Best Training
© 2024 STL. All Rights Reserved.
All prices offered for business users and exclude VAT. E&OE

2nd Floor, CA House, 1 Northey Street, Limehouse Basin, London, E14 8BT. United Kingdom

Terms & Conditions Privacy & Cookie Policy Consent preferences

Server loaded in 0.06 secs.