Copying and Pasting Value based on Date

[Hashidd]

Hi,

This is the first time I am trying to VBA. Watched some videos and did some reading, but I am still not able to accomplish what i need. So I thank in advance for all your help.

1. SHEET - "Data Input" has a drop down date for the user to select (CELL C2).
2. Once date is selected they will proceed to input the data for each one of the companies on columns C4 D4 E4 and F4.
3. They will hit the button at the end to "Copy and Paste as Values" the data that was inserted in the corresponding database sheet.

My problem is that I want to match the date on C2 with the same date on row 1 in each of the sheets.

(Sorry if this is confusing - I have attached a sample file)

 

[p45cal]

Try this in your sample file to replace your existing CopySaldoREal macro:

Sub CopySaldoREal()
Dim DestColm As Range
With Sheets("Data Input")
  myDate = .Range("C2").Value
  For Each Colm In .Range("C5:F50").Columns
    Select Case Colm.Cells(1).Offset(-1).Value
      Case "XY"
        Set DestnSht = Sheets("DataBase XY")
      Case "XZ"
        Set DestnSht = Sheets("DataBase XZ")
      Case "XP"
        Set DestnSht = Sheets("DataBase XP")
      Case "XE"
        Set DestnSht = Sheets("DataBase XE")
    End Select
    Set DestColm = DestnSht.Rows(1).Find(what:=myDate, LookIn:=xlFormulas, lookat:=xlWhole, searchformat:=False)
    If DestColm Is Nothing Then
      MsgBox "Date not found on sheet " & DestnSht.Name
    Else
      Colm.Copy
      DestColm.Offset(1).PasteSpecial xlPasteAll
      DestColm.Offset(1).PasteSpecial xlPasteValues
    End If
  Next Colm
  Application.CutCopyMode = False
End With
End Sub

 

[Hashidd]

Thank you so much! It worked perfectly! I wish i could hit the like button more than once!