IF condition copy paste Range(ActiveCell.Offset(,) in new sheet

It would be easier for anyone trying to help if you posted your workbook

To attach a file - click REPLY ... GO ADVANCED ... look below for MANAGE ATTACHMENTS etc

ManageAttachments3.jpg

Thanks for the workbook.

each table are regulary updated from the internet with a time running macro.

1. Please paste the time running macro into your reply (enclosed in code tags) by:
- clicking on # icon
- then:
[CODE ] paste your code here [/CODE ]

2. Is the macro in a sheet module or a general module?

3. Also please go to post#01
- click Edit Post
- select your code text
- click on the # icon

thanks

no worries

Let's test each stage and then put it all together at the end:

TEST1: Does VBA to correctly identify what to copy?

VBA below:
- loops thru' all tables in sheet "Processing"
- returns 2 message boxes List of tables to NOT copy, list of tables to copy)
- message boxes list table name, table range, cell to be "conditionally" tested, value of that cell

Please run the VBA and let me know if the results are what you expect - if results are not correct please explain what is wrong
thanks

Place in a GENERAL module

Please Login or Register  to view this content.

I know you do not want message boxes

The message boxes are to help me test the VBA - I do not have the "live" file
The message boxes will not be in the final code

Did the message boxes correctly identify which tables should be copied?

This code:
- loops thru' each table on sheet "Processing"
- tests value of bottom left cell is < -.1 or >.1
- inserts rows in sheet "STOCKVIEW"
- then copies tables passing test to sheet "STOCKVIEW"

Let me know if this is giving you what you want and then we can make it run every 10 minutes

Please Login or Register  to view this content.

and it stops on stockview.Rows("1:" & tRows).Insert Shift:=xlDown

if table contains 10 rows + heading
then this:

Please Login or Register  to view this content.

is same as:

Please Login or Register  to view this content.

I am not sure I understand what you are telling me
- is VBA is inserting the 10 rows and then not pasting anything?

instead of:

Please Login or Register  to view this content.

try:

Please Login or Register  to view this content.

The VBA works for me, but there must be something different in your worksheet

I gave you the code with the message boxes in post#06 to help you to tell me what the code is doing wrong
I have amended that code below so that it does not include the long table name.

Before running the code, make a note of the tables that "pass" the condition.
You will need to check what the message box gives for:
- table range
- cell address (bottom left cell)
- cell value

Is the error in the table range?
Is the error in the cell address?
Is the error in the condition?

thanks

Please Login or Register  to view this content.

block when #N/A value

Wrap another condition around the other condition like this:

Please Login or Register  to view this content.

One way to automate everything:

1 Cancel the direct update and you run it from macro instead
(see Sub RefreshAllTables below)
2 Add a timer
(Sub StartTimer)
3 Provide a method to stop the timer
(Sub StopTimer)
4 Start the timer when the file is opened
(Private Sub Workbook_Open)
5 Stop the timer when the file is closed
(Private Sub Workbook_BeforeClose)
6 Call the relevant subs
(sub RefreshAndCopy)

THIS ALL IN THE SAME General MODULE:

Please Login or Register  to view this content.

THIS IN THE ThisWorkbook MODULE:

Please Login or Register  to view this content.

(condition2 = condition for D cells)

Q1. a) or b)?
a) IF condition1 AND condition2 satisfied then copy table
b) IF condition1 OR condition 2 satisfied then copy table

Q2. what is the condition for the D cells?

Can do like this ?
condition 1 OR condition 2
condition 2 = If( tCell.Value < -0.1 AND A-cell <-0.01) Or (tCell.Value > 0.1 AND A-cell >0.01)

or too complex ?

Here is simple method to follow:
- add a Boolean variable (= True or False variable)
- create 2 separate conditions like this (using your notation) :

Please Login or Register  to view this content.

To avoid having 2 variables - this code should work if tCell is in columnD and a-cell is in columnA, of SAME row)

Please Login or Register  to view this content.

Sorry, I do not understand those conditions
It will be much easier if you provide examples

Please attach a workbook with 20 tables
- 15 that should be copied
- 5 that should not be copied

Try to cover all the conditions for "copy" and "do not copy"

Please explain next to each table why it should\should not be copied

thank you

Thanks, will look at your file tomorrow and post reply by 10am (UK)

delayed this morning, will post later...

Try this
- variable names amended to aCell (columnA) and Dcell (columnD) to make it easier to follow
- 5 variables added D1,D2,D3,D4 and D5
- conditions where table copied are now simplified into 4 separate conditions

Run in attached workbook with {CTRL} t

Please Login or Register  to view this content.

How about same technique as before:

Please Login or Register  to view this content.

deleted by kev_

It is probably because D1 etc are not decimal values

so replace:

Please Login or Register  to view this content.

with:

Please Login or Register  to view this content.

This will allow the code to run, and then if values are not valid IsNumeric takes care of them

ok, it move on, but now ask debug on D5 = Abs(D1 - D4)

See RED for solution to your question (again same technique)

But have you thought about removing all #N/A values from data tables and clearing those cell values?
see code in this colour
If you remove all #N/A values then IsNumeric tests would not be required

Please Login or Register  to view this content.

which message?

You are welcome 😀