VBA to transfer data from one sheet to another

[chrismanc]

Hi,
I'm looking for some vba help.
I have an excel forecasting file that I'm working one - on one sheet (bulk) I have data for about 200 products on which I generate different forecasts. The other sheet (single) provides a graphical view for one product - the user selects which product they want to look at from a drop down, formulas pull the relevant data from the first tab. The idea is that the user reviews the various generated forecasts and if necessary inputs a manual forecast. I then want the user to trigger a macro which would copy that manual forecast and paste (values) the data into the relevant row on the bulk sheet. The copy and paste values bits are straight forward enough but I am struggling with how to find the correct location for the paste.
Any help would be much appreciated
Thanks
Chris

 

[SteveO59L]

try posting a sample of your data, and what you would like to do with it

 

[chrismanc]

Bulk sheet would look something like
Calculated forecast Jan - Feb - Mar - Apr - May - Jun Manual forecast Jan - Feb - Mar - Apr - May - Jun
Product
Blue Widgits 100 - 200 - 200 - 300 - 300 - 400 Manual forecast cells blank at this point
Red Widgits 25 - 50 - 50 - 75 - 100 - 100

Single sheet would look something like this

Product - Blue Widgits

Months - jan - feb - mar - apr - may - jun etc
Calculated forecasts 100 - 200 - 200 - 300 - 300 - 400
Manual Forecast 150 - 200 - 250 - 300 - 350 - 400 - this row is manually inputted by the user

I then want the user to run a macro which copies the data in the manual forecast cells, goes to the bulk sheet, finds the row for Blue Widgits and paste values the data into the manual forecast cells

is that clearer?

 

[Fluff]

You can find the relevant row using something like this

Sub Findrow()
   
   Dim Fnd As Range
   
   Set Fnd = Sheets("Sheet1").Range("A:A").find(Sheets("Sheet2").Range("A1").Value, , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then
      MsgBox Fnd.Row
   End If
End Sub

 

[chrismanc]

Thanks Fluff - that works beautifully in terms of telling me which row on the sheet I need. What I need it do now is to go to that location on the second sheet, move up one row, and then across to column AA where I would paste the copied data.

I don't think my original description of wanting to find a location was clear enough - its find and go to, then follow a set pattern to reach a specific cell to paste in

Again, any help gratefully received

 

[Fluff]

How about

Sub Findrow()
   
   Dim Fnd As Range
   
   Set Fnd = Sheets("Sheet1").Range("A:A").find(Sheets("Sheet2").Range("A1").Value, , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then
      Set Fnd = Fnd.Offset(-1, 25)
      MsgBox Fnd.Address
   End If
End Sub

 

[chrismanc]

Rather than ending with a msgbox, I want the macro to select the cell which has been found - I'll then paste/values once the cell is selected

Any thoughts?