Macro to copy specific cells to a row based on date

[Jim Sasaki]

Hello,

I need help to get a macro to copy data in specific cells and paste to another worksheet based on date criteria.

From the sample file, I want to copy data on sheet2 (C5:E5) to sheet1, to the row that has the same date as sheet2 (C2)

If somebody already provided the similar solution to mine, please recommend, I cant seem to find the answer to my question.

Thank you so much if anyone would help me out, appreciate.

 

[vletm]

Jim Sasaki
Something like this ...?

 

[Jim Sasaki]

Jim Sasaki
Something like this ...?

Exactly! you're an absolute Excel Ninja sir, Thank you.

 

[Jim Sasaki]

Jim Sasaki
Something like this ...?

I'm trying to understand the code line by line. Can you spare your time to explain because I need to understand to apply to my work.

The code you did is perfect but I'm lost since the For X = 3 to 5 line.

Can it be more simple like copy C5 D5 E5 and if the condition(Date) is met what column to paste data to.

Thank you in advance sir!

 

[Marc L]

Hi !

Can it be more simple like copy C5 D5 E5 and if the condition(Date) is met what column to paste data to.

Another way via Excels basics too
but whatever the columns order in Sheet2 :​

Sub Demo1()
    Dim C, W, R, V, N&
    With Sheet2.[C4].CurrentRegion.Rows
        C = Application.Match(.Item(1), Sheet1.UsedRange.Rows(1), 0)
        W = Application.Index(.Item(2).Value, , 0)
    End With
    If Application.Count(C) = UBound(W) Then
        With Sheet1.UsedRange
                R = Application.Match(Sheet2.[C2].Value2, .Columns(1), 0)
            If IsNumeric(R) Then
                With .Rows(R)
                    V = Application.Index(.Value2, , 0)
                    For N = 1 To UBound(C):  V(C(N)) = W(N):  Next
                   .Value = V
                End With
            End If
        End With
    End If
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Marc L]

Something like this ...?

Hi vletm !

As your code is located in the worksheet Sheet2 module,
you do not ever need any reference to this Sheet2 within your code
as in a worksheet module each Cells or Range reference
without any sheet reference is automaticly set to its worksheet
even if it is not the active worksheet, no matter …

Your code revamped :​

Sub Do_It_r()
    With Sheet1
            y = Application.Match([C2].Value2, .Range("B:B"), 0)
        If IsNumeric(y) Then
            For x% = 3 To 5
                .Cells(y, 3 + (x - 3) * 2) = Cells(5, x)
            Next x
                .Activate
        Else
            MsgBox "Unknown Date!"
        End If
    End With
End Sub

 

[vletm]

Jim Sasaki
Term simple ... hmm? Whatever that would mean ... maybe possible?
For X = 3 to 5
Why need to write three times next code lines?
and
There are can be unknown amount rows of dates and
would there be always date which match?
anyway ... that would mean a lot of if's !

 

[vletm]

Marc L
Sorry Marc L,
but I need ...
Sometimes size matters, but not in these.
This way it is much clear for me.
Ps. Your code gives different result than mine.
Merci ...

 

[Marc L]

Which one, Demo1 or the revamped ?
As on my side all work according to the original attachment …​

 

[vletm]

Marc L
(#6) 'Your code revamped', if compare to mine code (#2).

 

[Marc L]

Same results on my side as you can see on this video …​

 

[vletm]

Marc L
I see that You couldn't see it.
It is not my challenge.

 

[Marc L]

Neither mine, just see the video ! ​

 

[vletm]

Marc L
I've watched it
... and still my 'ps' is as I wrote!

 

[Marc L]

Thanks Jim for the like !

Demo1 checks first if all Sheet2 columns match with Sheet1 headers
(first If) whatever the columns order (like at the end of post #11 video)
then if Sheet2 date matches a date in Sheet1 (second If)
it loads the values to the date row according to the headers …
​

 

[Jim Sasaki]

Jim Sasaki
Term simple ... hmm? Whatever that would mean ... maybe possible?
For X = 3 to 5
Why need to write three times next code lines?
and
There are can be unknown amount rows of dates and
would there be always date which match?
anyway ... that would mean a lot of if's !

The simple term I meant is that in case that I want to change specific cells location to be copied and change destination to paste value, I don't know what part of the VBA to change because I don't understand the language.

What I'm doing now is trying to find that part, but still struggling

Appreciate everyone's work here. Thanks a lot.

 

[vletm]

Jim Sasaki - I want to change specific cells location to be copied and change destination to paste value
Some 'helps':
'y' would figure as row & 'x' would figure as column
Cell A1 (Left Top) would also figure as Cells(1,1)
Cells(y,x) would figure as place('y's row and x's column')

You would test this version ... it'll show 'animated' how/what ...