I need a data transfer macro

[Kevin.W]

I am trying to make a simple spreadsheet for some people I work with to use each day. I have set up a Data Entry sheet where an individual can input the necessary data.

On the next sheet (tab) I have a simple table where that input data is used to do some calculations, etc and gives the desired results. So, what I need is a simple macro that will lookup the date input on one sheet, search a column of dates on the other sheet, then copy and paste all of the data from the table on into that row.

This doesn't seem to be that difficult in theory but I'm very inexperienced with VBA and I can't find anything out there that does something like this. Please help

 

[lenze]

Hi and welcome to the board!!!
Why do you need 2 sheets? Why not allow them to enter the data to the sheet where the work is done? If it's a priveleges issue, then design a userForm and have it directly input the data when it is filled out. Maybe an example of your data would help. I'm confused when you say "lookup the date input on one sheet, search a column of dates on the other sheet, then copy and paste all of the data from the table on into that row." Will there only be 1 line of data per date?

lenze

 

[Kevin.W]

Sorry if that's a little confusing.

Basically I want the ability to enter today's data on one sheet. On the other sheet this data gets populated into a chart. Below this chart I would like to have a large list of all the data for the year that will be saved. To make it easy for them entering data they would only need to mess with the first sheet and then push a button at the bottom to transfer data.

The macro would match or lookup the date in the colum on the 2nd sheet, copy the data from the chart on that sheet, and paste it all in the appropriate row. I just can't find a macro that will search a column for X, and paste the data along that row. Normally I'm pretty good at finding what I need out on the net but not this time.

I hope that clears it up some, and thanks for the feedback!

 

[Kevin.W]

Oh and yes each data will only have one row of data......it will be a very long row but only one row.

 

[lenze]

Is the date the only thing entered on the 1st sheet? Are there 2 or 3 sheets? How is the data arranged each sheet?? Can you provide a sample shot of what you require? You can use the Find Method in VBA to locate the date on the 2nd Sheet. Once located, you can go from there with copying and pasting the results. To help, we just need more(read detailed) information
lenze

 

[Kevin.W]

Ok think of it like this:

There are 2 sheets. Sheet 1 is simply input data. Nothing macro related needs to be done here. Sheet 2 has a table that is 6 rows of data, 10 columns. This table is taking the input data from the first sheet and doing some calculations, etc to get the real desired output each day. The date of that day will be on this sheet at the top. Down below that summary chart will be a historical data repository for all intensive purposes. Column A will be the date 1/1/2010 --> 12/31/2010.

The macro should find the data from the top of the sheet in that column. Then copy the data from the first row of the table and paste it next to the date in the same row. Copy the data from the second row of the table and paste it out on the same row, beside the previously pasted data, etc until all of the data from that table is pasted in one single row next to the correct date.

Is that a better understanding of what I'm looking for? This spreadsheet will end up being much much more complicated with several other sheets but these 2 sheets will only be used for data entry and data storage and I just was hoping there was an easy macro I could use to transfer all this data to the correct date/location rather than making someone copy and paste it all in the right spot and potentially induce human error.

Thank you for the help!

 

[lenze]

This Does your Data transfer on your 2nd Sheet
Assumptions:
Your Date in in A1
Your 6X10 table is in B2:K7
Your Dates in Column "A" start at A11

Sub TransFer()
Dim C As Range
Set C = Range("A11:A376").Find(Range("$A$1").Text, LookIn:=xlValues)
If Not C Is Nothing Then
Dim i, j As Long
j = 2
For i = 2 To 7
     Cells(i, 2).Resize(1, 10).Copy Cells(C.Row, j)
     j = j + 10
Next i
End If
End Sub

Post back your details if you have trouble changing the ranges
Note: You really don't need to have the dates pre-listed and then found. You can simply move the date to the next available row as the first part of this macro. Just an option.

lenze

 

[Kevin.W]

Awesome Lenze you are reading my mind! But 2 things:

1) I need it to copy the data and paste special: paste values. Currently it is copying and pasting and putting the formulas from the table in the row. That's really the only hitch.

2) If I set up a button on sheet 1 and attach this macro to that button nothing happens.....I'm assuming this is because it is then running the macro on Sheet1 (with the button) as opposed to running it on Sheet2 (with the table and where all the work is taking place). Is there a way to change that?

Once again thank you so much for the help!

 

[lenze]

The below will copy Values

Sub TransFer()
Dim C As Range
Set C = Range("A11:A30").Find(Range("$A$1").Text, LookIn:=xlValues)
If Not C Is Nothing Then
Dim i, j As Long
j = 2
For i = 2 To 7
     Cells(i, 2).Resize(1, 10).Copy
     Cells(C.Row, j).PasteSpecial Paste:=xlValues
     j = j + 10
Next i
End If
Application.CutCopyMode = False
Range("$A$1").Select
End Sub

To run the code from another sheet, you will either need to activate the sheet or include those instructions in the code. Something like this

Sub TransFer()
Dim C As Range
Dim sh As Worksheet
Set sh = Sheets("Sheet2")
Set C = sh.Range("A11:A30").Find(sh.Range("$A$1").Text, LookIn:=xlValues)
If Not C Is Nothing Then
Dim i, j As Long
j = 2
For i = 2 To 7
     sh.Cells(i, 2).Resize(1, 10).Copy
     sh.Cells(C.Row, j).PasteSpecial Paste:=xlValues
     j = j + 10
Next i
End If
Application.CutCopyMode = False
End Sub

HTH
lenze

 

[Kevin.W]

Ok the transfer of the values (copy, paste special, values) works great!

When I try to run it via a button on the other worksheet with your final bit of code, it doesn't work. It is copying the area from B2:K7 on Sheet1, not Sheet2, and then pasting the values form Sheet1 on Sheet1 way down on line 47 even though there is no date?? or anything down that far. Very close though! You are definitely much better at this kind of thing than I could ever hope to be.