• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 26
  • Last Modified:

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
0
Abigail Evans
Asked:
Abigail Evans
  • 5
  • 3
2 Solutions
 
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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
 
NorieVBA ExpertCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now