Copy & Paste from Workbook A to Workbook B Based on Date and value of cell

[Jalica]

Hi All,

Using VBA ... Is it possible when Workbook B is opened to copy a range of data from Workbook A to Workbook B based on two parameters?

Parameters are:
Todays Date is greater than 31/03/2018
Cell B22 = ""

If both are true open and Unprotect Workbook A and copy Sheet("QTR1") Range("B22:C221 & LastRow") paste to Workbook B Sheet ("QTR2") Range("B22:C221") then close Workbook A

Any help would be appreciated.

[sintek]

Hi Jalica

Which code houses the macro...WorkBook B?

So if in wb B those parameters are met it must open wb A and copy from A to B

[Jalica]

Yes ... the code would be placed in Workbook B to pull data from Workbook A

Eventually there will be four workbooks (one for each quarter of the year)

So ...

Code would be placed in Workbook D to pull data from Workbook C after the 30/09/2018
Code would be placed in Workbook C to pull data from Workbook B after the 30/06/2018
Code would be placed in Workbook B to pull data from Workbook A after the 31/03/2018

I hope I've explained this OK

[sintek]

See if this is what you are after...

Please Login or Register  to view this content.

[Jalica]

Thank you ... I will try this later
I will obviously need to change the filename to my filename
Is there anything else that I need to change i.e path details etc

[sintek]

Path is the path of wb B
So if wb A is in same path then that remains...
Change workbook A name to required name.

[Jalica]

Hi Sintek,

Thanks for your help it has been most useful ... however I am now getting a debug error.

In ThisWorkbook I have the following code:

Please Login or Register  to view this content.

and in Sheet1(QTR2 Audit Tracker) I have the following code:

Please Login or Register  to view this content.

I've highlighted the line where the debug error is ...Any Idea how to resolve this?

[sintek]

What error appears...And why is this code housed in sheet module....
what changes must take place before all these macros run...

[Jalica]

The error I get is Run-time error '9': Subscript out of range

I've attached the two files so that you can see what they do, if that's ok with you ...

I am fairly new to VBA coding, my code is probably not the best but it achieves what I want it too.

Maybe you could suggest an alternative or better way if you have the time to look at it ....


Many thanks in advance

[sintek]

I can only assume it error because the wb that the worksheet_Change event code refers to is not the active workbook...

Edit...
Add the red line to your code and see what happens

Please Login or Register  to view this content.

[Jalica]

Hi Sintek,

Tried adding the line of code but it didn't work.
I think the error is caused because of this:

When I open wb1 (QTR2) I unprotect it
Then I open wb2 (QTR1) and unprotect it
Then I copy from wb2 (QTR1)
Then I paste to wb1 (QTR2) - This triggers the workbook change event and tries to unprotect wb1 (QTR2) which has already been unprotected

Is there a way to add something to the worksheet change code below that says if worksheets ("QTR2 Audit Tracker") is protected then unprotect else continue to call subs?

Please Login or Register  to view this content.

[Jalica]

Hi Sintek,

Thank you for all your help.
I think I've now cracked it. I added On Error Resume Next to the Worksheet Change code as below and it now seems to be working as I want it too:

Please Login or Register  to view this content.

[sintek]

Just remember to add

Please Login or Register  to view this content.

after the error checker to disable

Or make use of code to check sheet protection i.e. Untested

Please Login or Register  to view this content.