• 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 copy some cells which contents are changing every day, and paste_value that into matching c

M

mahmut67

New Member
I have a worksheet which accumulates daily sales data of the shops from SAP. On my annual report table, I have name of the shops on columns headings (B6;Q6), and date of tha 365 days on rows headings (A7;A371) .In the line (a4;Q4) above that table, I have a row which matches to the table by shops name and date order. This row is linked to worksheet SAP DATA and gets the date and sales figures of the day. I want a macro that looks at the date on this linked row and finds the same date in my table and copy paste the sales figures as vale on that row of the table. (A sample workbook is attached). My knowledge on the subject matter limited to record and playback macros. I hope my poor english is understood. I would apreciate any help. Thanks in advance.
 
mahmut67 said:
I have a worksheet which accumulates daily sales data of the shops from SAP. On my annual report table, I have name of the shops on columns headings (B6;Q6), and date of tha 365 days on rows headings (A7;A371) .In the line (a4;Q4) above that table, I have a row which matches to the table by shops name and date order. This row is linked to worksheet SAP DATA and gets the date and sales figures of the day. I want a macro that looks at the date on this linked row and finds the same date in my table and copy paste the sales figures as vale on that row of the table. (A sample workbook is attached). My knowledge on the subject matter limited to record and playback macros. I hope my poor english is understood. I would apreciate any help. Thanks in advance.
Click to expand...
 

Attachments

  • SAMPLE.xlsx
    46.4 KB · Views: 0
  • SAMPLE.xlsx
    46.3 KB · Views: 0
Dear Chirayu, I have pasted the formula in A1 cell. Nothing has happened. Since I am ignorent in the field of VBA, I did not understand how to use the tip. I tried to upload the sample workbook twice. May be I don't know how to upload it. Thank you for your interest on my problem.
 
Oh the formula in my signature is just part of my signature. Not a solution. Attached the file. Doesn't need macro.

Will work if:
1) Data always in columns B & C of SAP Data sheet
2) B2 has date in SAP Data Sheet
3) All you do then is every day just value paste the new row of data
 

Attachments

  • SAMPLE.xlsx
    59.1 KB · Views: 0
Dear Chirayu, thank you for your time and effort you spent on my question.
In order to do the step4 by a macro, I have recorded the following makro.

Range("B217:Q217").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

But the fixed line nr. 217 in "Range("B217:Q217").Select" must be changed every day. I can find the new number every day by this formula on the excel sheet:
"=MATCH(B1;A1:A371;0)
which will return different result depending on the date on the SAP DATA sheet. (for present it gives 217)
My question is what can I do so the maco will take the result of the MATCH formula and adjust
the macro line "Range("B217:Q217").Select" automaticly every day.
 
Code: 
Sub ValuePaster()

Dim SAPDate As Date

'Change range if different
SAPDate = Worksheets("SAP DATA").Range("B1").Value

'Change Sheet name if different
Worksheets("2015 Actuals").Select

'Find the date
Range("A6").Select
Do Until ActiveCell.Value = SAPDate
    ActiveCell.Offset(1, 0).Select
Loop

'Change Columns if Range is more
Range("B" & ActiveCell.Row & ":Q" & ActiveCell.Row).Select
Selection.Copy
Selection.PasteSpecial xlPasteValues
Application.CutCopyMode = False
Range("A" & ActiveCell.Row).Select

End Sub
 
You must log in or register to reply here.
Back
Top