Excel VBA- Copying data from newest worksheet to a new workbook
Closed
Holwhit
Posts1Registration dateWednesday January 30, 2019StatusMemberLast seenJanuary 30, 2019
-
Jan 30, 2019 at 11:55 AM
1aman1diot -
Jan 30, 2019 at 01:12 PM
Hi all,
Newbie here. I've been fumbling around with VBA and managed to get some parts working, but now I'm stumped.
I have an Excel workbook that gets used for field data collection (Snow Survey Sheets). Every time a technician goes out, a new worksheet (named as the current date) is added to the workbook. This worksheet contains 5 tables which summarize the field data as an average depth, total water equivalent, and site conditions. The summarized field data collected needs to then be entered into a new workbook in a specific format for submission. I'd like to be able to automate the data export to the new workbook.
Right now, I have an export button on the field form in Snow Survey Sheets, that creates a new workbook in the same location as the field form, names it as the current date, and has the submission template set-up. I can't seem to get it to copy over the specific cells with the data I require though. I don't get an error message, it just populates the template with zeroes.
Any help on figuring this out would be appreciated. I've pasted the code below. I apologize in advance, it's probably quite cumbersome, but I'm very new to this have jumped into the deep end of the pool instead of starting with the basics due to time restrictions.
Sub AddNew()
Set NewBook = Workbooks.Add
With NewBook
.Title = "Snow Data"
.Subject = "Snow Data to be Submitted"
.SaveAs Filename:="Snow Data_" & Format(Date, "yyyy-mm-dd") & ".xlsx"
End With
NewBook.Sheets("Sheet1").Range("A21").Value = "Field definitions are as follows:"
NewBook.Sheets("Sheet1").Range("A22").Value = "FIELD NAME"
NewBook.Sheets("Sheet1").Range("A23").Value = "code"
NewBook.Sheets("Sheet1").Range("A24").Value = "Date"
NewBook.Sheets("Sheet1").Range("A25").Value = "Year"
NewBook.Sheets("Sheet1").Range("A26").Value = "Month"
NewBook.Sheets("Sheet1").Range("A27").Value = "Day"
NewBook.Sheets("Sheet1").Range("A28").Value = "SD"
NewBook.Sheets("Sheet1").Range("A29").Value = "SWE"
NewBook.Sheets("Sheet1").Range("A30").Value = "CRUST"
NewBook.Sheets("Sheet1").Range("A32").Value = "Soil_condition"
NewBook.Sheets("Sheet1").Range("A33").Value = "Flag"
NewBook.Sheets("Sheet1").Range("B22").Value = "Notes"
NewBook.Sheets("Sheet1").Range("B23").Value = "1 line per station, case sensitive, must be a valid WISKI Station number."
NewBook.Sheets("Sheet1").Range("B24").Value = "The date of the sample, with no restrictions on format"
NewBook.Sheets("Sheet1").Range("B25").Value = "A 4 digit numerical value for the year of the sample."
NewBook.Sheets("Sheet1").Range("B26").Value = "A 2 digit numerical value for the month of the sample; use 11 for November."
NewBook.Sheets("Sheet1").Range("B27").Value = "A 1 or 2 digit numerical value for the day the sample was taken."
NewBook.Sheets("Sheet1").Range("B28").Value = "Average depth of snow at the station; measured in either CM or 1/10ths of inches; decimal values are accepted."
NewBook.Sheets("Sheet1").Range("B29").Value = "Average water equivalent of snow at the station; measured in either MM or 1/10ths of inches; decimal values are accepted."
NewBook.Sheets("Sheet1").Range("B30").Value = "Crust conditions at the snow course; the field will accept several characters of text or numbers; expected values are either A or B or C or D."
NewBook.Sheets("Sheet1").Range("C31").Value = "A: no crust B:light crust C:snowshoe crust D:man crust"
NewBook.Sheets("Sheet1").Range("B32").Value = "Soil conditions at the snow course; the field will accept several characters of text or numbers; expected values are UD – unfrozen dry, UW – unfrozen wet, or F – frozen."
NewBook.Sheets("Sheet1").Range("B33").Value = "A numeric value is expected; either 0 or 1;"
NewBook.Sheets("Sheet1").Range("B34").Value = "A flag value of ZERO (0) denotes measurements of snow depth and water equivalent are IMPERIAL: 10ths of inches of snow depth and water equivalent;"
NewBook.Sheets("Sheet1").Range("B35").Value = "A flag value of ONE (1) indicates measurements are in METRIC: CM of snow depth and MM of water equivalent."
End With
'Copy Snow Data From Field Form into Submission Form
