Copying cells based on date.

[JonathanMoore]

Hi Team,

I'm looking for some help in copying some data from one sheet to another based on the condition of dates - I've attached an example worksheet


Basically what I'm looking to do is take the date value in Sheet2 - B3, search through Sheet1 - Column B until there is a match. When there is a match copy the values on the same line in Columns E&F into the relevant cells in Sheet 2.

Basically copy the Prices in USD and GBP from sheet 1 to sheet 2 where the dates line up!

Sorry if my explanation is either long-winded or just rubbish!!

Thanks in advance,


- Jon

[Steffen Thomsen]

Hi,

You can use this:

Please Login or Register  to view this content.

Steffen Thomsen

[JonathanMoore]

Hi Steffen,

That code works nicely thanks

However, when I move the code into the actual worksheet (I couldn't upload it as it contains company data) it throws up an error:

Object variable or With block variable not set"

I've modifed the code so that "Sheets1" and "Sheets2" are the correct names, have I missed anything?

Thanks again for your help!


- Jon

[Steffen Thomsen]

Where have you placed the code?

In the sheet module or in a new module?
Do you have any hidden rows/colums in the sheet?

[JonathanMoore]

It's in a new module with no other code in it and there are no hidden rows or columns.

I'm using the actual name of the sheet, i.e. Sheets("SBL Fax") rather than Sheets(1) - could this be the issue?

Thanks,

[Steffen Thomsen]

Try with the sheets(1) instead

[JonathanMoore]

New error

Type Mismatch

at this line:

findStr = CDate(Sheets(17).Range("B3"))

[Steffen Thomsen]

Whats the format of the data stored in that range?

[JonathanMoore]

Sheet(17) cell B3 is calculated using this formula:

Please Login or Register  to view this content.

And the cell format is:

*dd/mm/yyyy

This is the same as the dates in Sheet(18) that it is compared to

[Steffen Thomsen]

And its the right sheetindex?

Sheets(1) refers to the first sheet in the workbook, not neceseraly the sheet witn name Sheet1

In the same way Sheets(17) refers to number 17 worksheet from the left.

[JonathanMoore]

Yeah - looking at the way that they're listed within the VB editor, the two sheets i need are named as follows:

Sheet17 (SBL Fax)
Sheet18 (SBL Ex TDG)

But when I count along the sheets from left to right within excel I get the following

SBL Fax = Sheet11
SBL Ex TDG = Sheet12

If I try and use 11 & 12 within the macro - i get the "Object Variable not set" error again.

[Steffen Thomsen]

Try declaring the variables

Please Login or Register  to view this content.

[JonathanMoore]

That's worked brilliantly

Thanks for your help!


- Jon