Previous Topic Next Topic

Macro/Vba help.

Solved/Closed
PM - Oct 14, 2010 at 05:26 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Oct 19, 2010 at 09:49 AM
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
Related:

2 responses

Answer 1 / 2
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Oct 18, 2010 at 09:32 AM
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
PM
Oct 19, 2010 at 05:31 AM
This works great!! thanks for your help.

PM
0
PM
Oct 19, 2010 at 05:54 AM
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
0
Answer 2 / 2
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Oct 19, 2010 at 09:49 AM
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
0

Similar discussions

Create folder in desktop with vba, if exist, ignore
viriato74 -
praveen -

6 responses