• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro to paste data based on date

R

RiaMelissaJaravata

Member
I have two Excel files. One is table.xlsm and other is ivr.xls
I need to copy data from ivr.xls based on the date. Date is on A6:A30 on ivr.xls, and E6:E30 on table.xlsm. I need the data to be pasted on column F of table.xlsm.
I was trying this but it is not working :(

Dim myDate As Date
Dim myValue As Variant

myDate = ivr.Sheets("Data1").Range("A6").Value
myValue = ivr.Sheets("Data1").Range("K6").Value

table.Sheets("Sheet2").Range("E:E").Find(myDate).Offset(0, 1).Value = myValue

Please help :(
 
Hi ,

Do you want all of the data transferred from :

A6:B36 in the Data1 tab in the file ivr.xlsx

to :

E7 in the Sheet1 tab in the file table..xlsm

Narayan
 
Uhm, no. I want to put a start date on table..xlsm (kindly see updated attachment). After putting start date, columns E7:E19 will be filled. Then, it will open file ivr.xlsx, then it will copy data from ivr.xlsx to table..xlsm depending on the dates... Please help me. :)
 

Attachments

  • table..xlsm
    21.2 KB · Views: 1
Yes, if you will check table..xlsm, you will see that if you click command button, it will ask you to put just one date. And there are formulas from E7:E19 that will populate the dates. What I want is, to open ivr.xls and find there the dates from E6:E19, and paste corresponding data to F6:F17 of table..xlsm
 
Hi ,

What is the date you entered ?

I tried it with the file ivr.xlsx you uploaded , and I never got this error , which will appear if the entered date is not found in the file.

Narayan
 
It still shows the error :(

But why did you make the format of the date like this:

Const DATEFORMATSTRING = "dd-mm-yyyy"

The format of my date is:

DATE
3/1/2015
3/2/2015
3/3/2015
3/4/2015
Which is dd/mm/yyyy
 
Hi ,

If your date format is dd/mm/yyyy , why is the code prompting the start date to be entered in the format mm/dd/yyyy ?

Narayan
 

Hi,

otherwise, could be done without any code just by using an advanced filter.
Tip : call it from destination worksheet …
​
 
Hi, it works, but how can I eliminate the "unable to match property of the worksheetfunction class". Because what if my date is from 03/20/2015 up to 04/03/2015. It should just leave 04/01-04/03 blank. But instead, it shows the error "unable to match property of the worksheetfunction class". How can I eliminate that? Please help
 
Narayan, thank you, you're a genius! But one last question, could you still help?
Is that also applicable to copy every 288th cell? I cannot get the logic on how I can do that on every 288th cell :(
Kindly see attachments. I hope you can still help. Thanks!
 

Attachments

  • every 288.xlsx
    14 KB · Views: 0
  • ivr.xlsx
    228.6 KB · Views: 1
Hi ,

We can do it in 2 ways :

1. Either we look at every 288th value as you have posted

or

2. We look at the filtered cells , and take all of them.

In the first way , you need not filter ; you merely need to change the start cell address.

In the second way , you filter on what ever criteria you want , and the code looks at all the visible cells.

Which one do you want ?

We can also have both in place , so that the code first checks to see whether a filter is in place ; if so , it looks at all visible cells. If there is no filter , the code starts from a predefined start cell , and looks at every 288th cell. Is this OK ?

Narayan
 
Hello Narayan, thank you so much for your patience and time.
Hmm as you can see, in every 288th cell is a 00:00 - 00:05 data. And I just need to copy the 00:00 - 00:05 data in every date... Does that help?

But yes, it can also just look for all visible cells, I am hiding all other cells that is not 00:00 - 00:05 data...
 
You must log in or register to reply here.
Back
Top