• 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/paste a column based on a cell value

S

stounino

New Member
Hi, I am trying to adapt the code I found here to my needs. I want to copy the string "G7:G369" in WS "data_rev&stats" based on the value of the cell "G5" (this value will change everyday with a +1 increment) to the WS "MTD_data_rev&stats" 2 cells below the corresponding value found in the range "5:5". The idea is to have historical data day by day throughout the month.

So I manage to copy the string I want but I cannot manage to select the cell located below the matching value.

Could anyone help me out to finish the code? Thanks a lot! Let me know if I need to clarify :D

Code: 
Sub export_MTD()

Dim myDate As Variant
Dim myValues As String
myDate = Worksheets("day_data_rev&stats").Range("G5").Value
myValues = Worksheets("day_data_rev&stats").Range("G7:G369").Copy

'Note that this will overwrite whatever was already in the col
Worksheets("MTD_data_rev&stats").Range("5:5").Find(myDate).Offset(2, 0).Select

End Sub

This is the source sheet with the incremental value in yellow

67070

This is the destination sheet

67069
 
stounino
You are trying to select something from other sheet ... that won't work.
Copying works as in the original code.
If You really would like to do selection,
then You should before that code ... select that sheet
Worksheets("MTD_data_rev&stats").Select
and after above ... You could do selection from that sheet.
>> Range("5:5") is fifth row
 
Hi there, thanks for the help.

I tried with this again but I am definitely doing something wrong to pase in the cell I want. Any help is much appreciated!

Code: 
Sub export_MTD()

Dim myDate As Variant
Dim myValues As String
myDate = Worksheets("day_data_rev&stats").Range("G5").Value
myValues = Worksheets("day_data_rev&stats").Range("G7:G369").Copy

'Note that this will overwrite whatever was already in the col
Worksheets("MTD_data_rev&stats").Select
Range("5:5").Find(myDate).Offset(2, 0).Value = myValues

End Sub
 
vletm said:
stounino
One thing which I can see at once is
that You did something else
than I tried to explain
as well as You have tried to do.
Click to expand...

I am not sure to understand what yous say here.

I want to paste the values I copied in the cell located 2 cells below reference number that I looked up.
 
Your want was:
So I manage to copy the string I want but I cannot manage to select the cell located below the matching value.

Your original code was ... about selecting
Code: 
'Note that this will overwrite whatever was already in the col
Worksheets("MTD_data_rev&stats").Range("5:5").Find(myDate).Offset(2, 0).Select

Your the newest code was - with selecting sheet as that needs ... but next line was something different?
Code: 
'Note that this will overwrite whatever was already in the col
Worksheets("MTD_data_rev&stats").Select
Range("5:5").Find(myDate).Offset(2, 0).Value = myValues

... and, now, You want something else:
I want to paste the values I copied in the cell located 2 cells below reference number that I looked up.

Do You know - what do You want to do?
 
Sorry if the code does not reflect what I want, I am new to VBA. What I want to do is this:

In the WS "data_rev&stats"
1. define the variable "myDate" with the valu of the cell G5
2. copy the string "G7:G369"

In the WS "MTD_data_rev&stats"
3. look for the the value "myDate" on line 5
4. Paste in the column where the myDate was found - 2 cells below

Is it more clear? Thanks a lot.
 
... then ...
be careful, be patient,
here my hints:
1) go again to Your given 'here'-link ( #1 reply)
2) copy that #2 reply's code
3) paste it to Your file
4) verify that You have Your named 'sheet's
5) modify 'sheet's as You're using to Your copied code
6) modify 'range's as You're using to Your copied code
7) modify 'offset'-value as You're using to Your copied code
'even one miss - will effect the result'
Excel won't start to guess.
If it works
... then You've done everything okay
... otherwise ... check that everything has written as it should be written.
 
That's my result. It works with one value only. I cannot find why it does not work with the full string G7:G369.

Code: 
Sub export_MTD()

Dim myDate As Date
Dim myValue As Variant
myDate = Worksheets("day_data_rev&stats").Range("G5").Value
myValue = Worksheets("day_data_rev&stats").Range("G7:G369").Value

'Note that this will overwrite whatever was already in col B
Worksheets("MTD_data_rev&stats").Range("5:5").Find(myDate).Offset(2, 0).Value = myValue

End Sub
 
How many values could myValue have?
Your could check that with Watch-option then You're running Your code step-by-step.
 
Did You check that?
... if not - then You would do it.

... anyway
I would do that part ... other way!
and
I won't mess You 'my way'.
as well as
without a sample file, I cannot test myself nothing.
 
I tried something else like that, but still I am not comfortable with the find function.

Here is a sample attached, I put dummy figures because the data is confidential.

Thanks a lot
 

Attachments

  • Sample.xlsm
    56.8 KB · Views: 4
I could it make someway working ...
I needed to change eg Q5's value to number...
as well as
some change with Your code
eg because Your G5 ... seems to be something else than ... date as in Your code.
... and so on ... check that code
as I wrote - I would do this other 'my way'.
= not modify others code normally!
 

Attachments

  • Sample.xlsm
    59.1 KB · Views: 3
Good morning,

Thanks for sharing the macro. I was missing the fact that line 5 has to be treated as an integer.

Thanks a lot for the help, it makes much more sense!
 
Hi, that's how I ended up, it works perfectly

Code: 
Sub export_MTD()

    Dim myDate As Integer
    Dim myValue As Variant
    myDate = Worksheets("day_data_rev&stats").Range("G5").Value
    myValue = Worksheets("day_data_rev&stats").Range("G7:G369").Copy
    
    Worksheets("MTD_data_rev&stats").Range("5:5").find(myDate).Offset(2, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Application.CutCopyMode = False
    
End Sub
 
You must log in or register to reply here.
Back
Top