I need VB scripting

Hello,
New to VB script here. I have a form that I created in Excel with a start and stop date. I have added the VB script to expand the start and stop date on my raw data sheet. I need some script to copy the rest of my form with the date expansion.  Please help me out!
The form cells are C4-E4. Raw data page has the code pasted into Cell D based on the last available cell. Here is the code I have so far.
                                          
                                          
Sub GenerateDates()

Dim FirstDate As Date
Dim LastDate As Date
Dim NextDate As Date
Dim LRow As Long



FirstDate = Worksheets("Form").Range("F4").Value
LastDate = Worksheets("FORM").Range("G4").Value

Worksheets("Raw Data Sheet").Select
LRow = Cells(Rows.Count, 4).End(xlUp).Row
NextDate = FirstDate
'selection of columns within one row
Do Until NextDate > LastDate

ActiveCell.Value = NextDate
    ActiveCell.Offset(1, 0).Select
    NextDate = NextDate + 1
   

Loop

Worksheets("Form").Select


End Sub
Abigail EvansWorkforce Capacity Plan AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sirbountyCommented:
What's not working?  Code tested fine for me.
0
Abigail EvansWorkforce Capacity Plan AnalystAuthor Commented:
Hello! Thank you for your quick response. I would like to add to the code. I have created a form on the "form" page and want to copy the rest of the data around the start date and end date on the "Raw Data Tab". I have attached the file to provide some clarity.

I need to copy the rest of the form data with the date expansion on the Raw Data Tab every time the update button is clicked. I hope that clears it up.
Workflow-Timeline-with-macros2.20.2.xlsm
0
sirbountyCommented:
You want the update button to drop the new date range in column D, but after the data that is already present?
0
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference ithrough the Colors of STEM program.

sirbountyCommented:
Assuming I'm understanding your dilemma, this should resolve it for you...

Sub GenerateDates()

Dim FirstDate As Date
Dim LastDate As Date
Dim NextDate As Date

FirstDate = Worksheets("Form").Range("F4").Value
LastDate = Worksheets("FORM").Range("G4").Value

Worksheets("Raw Data Sheet").Select
Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).Select

NextDate = FirstDate
'selection of columns within one row
Do Until NextDate > LastDate
    ActiveCell.Value = NextDate
    ActiveCell.Offset(1, 0).Select
    NextDate = NextDate + 1
Loop
Worksheets("Form").Select

End Sub

Open in new window

0
Abigail EvansWorkforce Capacity Plan AnalystAuthor Commented:
Thank you for your fast response. I apologize I am not explaining it well.
I have attached what I am trying to accomplish. I need all of the data filled out in the form to be copied and pasted into the raw data sheet as displayed when the update button is pushed.  

My challenge is that I do not know how to get the data over and expand the start and end date at the same time.

I hope that helps explain it better.


Thank you!
0
sirbountyCommented:
Still not sure I completely understand, but let me know if this is what you're trying to accomplish?  Should be close enough to tweak, because your target column data doesn't match up, but presumably you only need it in that form...

Sub GenerateDates()

Dim FirstDate As Date
Dim LastDate As Date
Dim NextDate As Date
Dim LRow As Long

Dim Campaign As String
Dim Site As String
Dim LOB As String
Dim Desks As String
Dim AMPM As String
Dim Details As String
Dim Attrition As String
Dim ID As String

Campaign = Worksheets("Form").Range("C4").Value
Site = Worksheets("Form").Range("D4").Value
LOB = Worksheets("Form").Range("E4").Value
FirstDate = Worksheets("Form").Range("F4").Value
LastDate = Worksheets("FORM").Range("G4").Value
Desks = Worksheets("Form").Range("H4").Value
AMPM = Worksheets("Form").Range("I4").Value
Details = Worksheets("Form").Range("J4").Value
Attrition = Worksheets("Form").Range("K4").Value
ID = Worksheets("Form").Range("L4").Value

Worksheets("Raw Data Sheet").Select
Sheets("Raw Data Sheet").Cells(Rows.Count, "B").End(xlUp).Offset(1, -1).Activate
NextDate = FirstDate

Do Until NextDate > LastDate
    ActiveCell.Value = Campaign
    
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = Site
    
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = LOB
    
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = NextDate
    
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = Attrition
    
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = AMPM
    
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = ID
    
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = Details
    
    NextDate = NextDate + 1
    ActiveCell.Offset(1, -7).Select
Loop

Worksheets("Form").Select


End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NorieAnalyst Assistant Commented:
Abigail

Exactly what data should go from the 'Form' worksheet to the 'Raw Data' sheet?

By the way, there's no need to loop to generate the dates.
Sub GenerateDates()
Dim FirstDate As Date
Dim LastDate As Date
Dim NoDays As Long

    FirstDate = Worksheets("Form").Range("F4").Value
    LastDate = Worksheets("FORM").Range("G4").Value
    
    NoDays = LastDate - FirstDate + 1
    
    With Worksheets("Raw Data Sheet").Cells(Rows.Count, 4).End(xlUp).Offset(1)
        .Value = FirstDate
        .AutoFill .Resize(NoDays)
    End With

End Sub

Open in new window

0
Abigail EvansWorkforce Capacity Plan AnalystAuthor Commented:
Thank you very much for your help! This code did exactly what I needed it to do.

You are very much appreciated.
0
sirbountyCommented:
Awesome - happy to have helped!
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.