CODEWITHSUNDEEP
excelvba
Tyeson
Tyeson

Reputation: 1

Copy cell range and paste in another worksheet based on the date?

I've scoured the interwebs for any kind of solution and I keep coming up empty so hopefully someone can help me out. I have two sheets, Sheet1 and Inventory. In Sheet1, the user enters the date in B1. In range C4:C200 I have a list of supplies and in range D4:D200 a user enters the number of each of the supplies on hand. In Inventory, the list of supplies is in range A1:A200, and b1:z1 list dates.

I'm trying to create a macro that will look in Sheet1 for the date entered in B1, let's say 4/1/19, copy D4:D200, then look in Inventory, find 4/1/19 in rows b1:z1 and paste the copied data beneath the correct date. So if 4/1/19 is in cell E1, the values would be pasted in E2.

While I'm decent with cell formulas and functions, I'm new to macros, so I'm not sure what to do.. Any help is greatly appreciated!

Upvotes: 0

Views: 45

Answers (1)

Steven
Steven

Reputation: 1

I tried to write some very basic code in such way you can easily read it. It is not the most sophisticated code but it will do the job. Just a few things i noticed: The number of supplies are in a list 197 long, the sheet Inventory states lists of 200 items...well, you can easily adjust the macro below. Copy past the code in a new module and run it. If you encounter any problems please post the complete workbook and i will have a look. Make sure that cell a1 on inventory is not empty.

Sub DoYourThing()
Dim c As Integer

  c = findHorizontal("Inventory", 1, Sheets("Sheet1").Cells(1, 2).Value)
  'now we know what column the date is in
  For i = 2 To 200
    Sheets("Inventory").Cells(i, c) = Sheets("Sheet1").Cells(i + 2, 5)
  Next i
End Sub

Function findHorizontal(Sheet As String, row As Integer, Value As Variant) As Integer
'searches a row from left to right until the cells are empty
Dim i As Integer

  i = 1

  Do While Not IsEmpty(Sheets(Sheet).Cells(row, i))
    If Sheets(Sheet).Cells(row, i) = Value Then
      findHorizontal = i
      Exit Function
    End If
    i = i + 1
  Loop

  findHorizontal = -1
End Function

Upvotes: 0

Related Questions