Macro/Vba help. [Solved/Closed]

Report
-
Posts
2675
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 5, 2020
-
Hello,

i need help to do solve the following problem:

Sheet1 contains a value for today. e.g £1000.00 (Cell A10)

Sheet3 contains every date for the month in column A

what i need is a macro/vba code that can do the following..

Copy the value in Sheet1 cell A14,

then go to sheet3 and search for todays date in column A,

then tab across to column C and paste the value in.

I hope someone can help!

Thanks in advance.

PM

Excel 2003

2 replies

Posts
2675
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 5, 2020
448
Hi PM,

This is what I have come up with:

Sub test() 
Sheets("sheet3").Select 
Set c = Range("A1:A365") 
    For Each cell In c 
        If cell.Value = Range("B1").Value Then 
            Sheets("sheet1").Range("A14").Copy 
            cell.Offset(0, 2).PasteSpecial 
    End If 
        Next 
Application.CutCopyMode = False 
End Sub

The fifth line of the code:
If cell.Value = Range("B1").Value Then
refers to the cell B1 of sheet3. Change this cell to whatever you prefer as long as it has the folowing formula in it: =TODAY().

I have no idea how cell A10 fits into the story, maybe a typo (A10 = A14).

Best regards,
Trowa
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

This works great!! thanks for your help.

PM
Hi Trowa,

Just another quick question.

How would i amend your coding to use different worksheets in other workbooks.

E.g Sheet3 was located in another file called Date.

Thanks
Posts
2675
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 5, 2020
448
Hi PM,

Sheet1 is saved in the file called Source.
Sheet3 is saved in the file called Date.
Make sure both workbooks are opened.

Then use this code:
Sub test()
Workbooks("Date.xls").Sheets("sheet3").Activate
Set c = Range("A1:A365")
    For Each cell In c
        If cell.Value = Range("B1").Value Then
            Workbooks("Source.xls").Sheets("sheet1").Range("A14").Copy
            cell.Offset(0, 2).PasteSpecial
    End If
        Next
Application.CutCopyMode = False
End Sub

Thanks for the feedback.

Best regards,
Trowa

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!