Send data to another workbook

S

Suzanne

Is this possible?

Active Workbook = the open workbook (variable document name)
Active Worksheet = Data Entry
Surveyed Buildings = a worksheet within a closed workbook (2009 Surveys) a
different folder

Activeworksheet("A2") = building #
Activeworksheet("B2") = survey date
Activeworksheet("C2") = next scheduled survey cycle (1 year, 2 year, 3 year)

Surveyed Buildings("R:R") = building #
Surveyed Buildings("S:S") = survey date
Surveyed Buildings("T:T") = next scheduled survey cycle
Surveyed Buildings("U:U") = next scheduled survey

If Activeworksheet ("A2") matches a building number in Surveyed Buildings
("R:R"), place values from Activeworksheet(B2, C2) in corresponding cells of
Surveyed Buildings: otherwise place the data on a new row

Calculate the Surveyed Buildings next survey date: survey date(S:S) + 1
year, 2 year, or 3 year

Also! If the next scheduled survey date is calculated on the
Activeworksheet, can the data be sent to Outlook (on the calendar or a new
task?)
 
J

JLGWhiz

I don't quite understand how you want to handle the future survey cycles,
but I assumed that columns S, T and U were where you wanted them. If not,
you can play around with this and re-post for more help.

Sub survey()
Dim sh As Worksheet, lr As Long, c As Range
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 18).End(xlUp).Row
Set rng = sh.Range("R2:R" & lr)
For Each c In rng
If c.Value = Range("A2").Value Then
c.Offset(0, 1) = Range("B2").Value + 365
c.Offset(0, 2) = Range("B2").Value + 730
c.Offset(0, 3) = Range("B2").Value + 1095
Else
Range("R" & lr + 1) = Range("A2").Value
Range("R" & lr + 1).Offset(0, 1) = _
Range("B2").Value + 365
Range("R" & lr + 1).Offset(0, 2) = _
Range("B2").Value + 730
Range("R" & lr + 1).Offset(0, 3) = _
Range("B2").Value + 1095
End If
Next
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top