Find Max and Min plus Date of each Max and Min using an excel script

**mickcondon89** · 01-02-2018, 10:55 PM

Hi there,

I have multiple workbooks containing the water level data for a particular bore.

So if the bore is listed 46217, it will have its own workbook and contain a series of columns listing the date the water level was taken, the water level and other details.

What I want to do is create a script that will create a separate excel file containing the max and min water level (the column labeled WLMP__m_), plus the date of each value for each bore/workbook.

There is approx 140 workbooks and I need to complete this for each one.

I have attached one of the files as an example.

Thanks,

Mick

**jindon** · 01-03-2018, 01:13 AM

If Data layouts and sheet naming rule are the same in each workbook, try

Please Login or Register  to view this content.

**mickcondon89** · 01-03-2018, 06:29 PM

Thanks Jindon,

Sorry I'm a fairly basic VBA guy, so if you could explain a few things that would be great.

Do I need to change any directories in this code to suit my own? When I run the script it prompts me to locate a folder - is that folder the location of all excel files?

Thanks so much for your reply!

Mick

**mickcondon89** · 01-03-2018, 06:41 PM

Actually I've just worked it out and it works perfectly! Fantastic!

What do you think is the best way to learn VBA? It's a very handy tool!

**mickcondon89** · 01-03-2018, 09:23 PM

Also Jindon,

If I wanted to extract the first line of data and the last line of data from each spread sheet, would that be possible?

For example the in the spreadsheet provided above, Row 2 and Row 292. These would change depending on the spread sheet.

Thanks for your help

This is what I have so far!

Sub SaveLastLine()
Dim wb As Workbook
Dim ws As Worksheet
Dim FileName As String
Dim lastCol As Integer
Dim lastRow As Integer

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set ws = ActiveSheet ' Here I instantiate the active worksheet
Set wb = Workbooks.Add ' Here I instantiate the new workbook

lastCol = ws.Range("A1").End(xlToRight).Column
lastRow = ws.Range("A1").End(xlDown).Row

ws.Range(ws.Cells(1), ws.Cells(2, lastCol)).Copy wb.Worksheets(1).Range("A1") ' Here I copy the first 3 rows and paste in the first worksheet of your new workbook
ws.Range(ws.Cells(lastRow, 1), ws.Cells(lastRow, lastCol)).Copy wb.Worksheets(1).Range("A3") ' Here I copy the last row and paste

FileName = "yourfilename.xlsx"
wb.SaveAs FileName

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

**jindon** · 01-03-2018, 10:35 PM

1)
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html

2)

Originally Posted by mickcondon89

If I wanted to extract the first line of data and the last line of data from each spread sheet, would that be possible?

How do you want the output to accompany current the results?
If upload a result workbook, it may help.

**scottiex** · 01-03-2018, 11:18 PM

Depends on how you learn.
you can buy one Of the books
https://www.amazon.co.uk/Excel-24-Ho...7988642&sr=8-1
https://play.google.com/store/books/...d=46toCUvklIQC
https://www.amazon.co.uk/Excel-Progr.../dp/0764574124

if you learn that way.

I learn this sort of thing by doing,
So I need interesting tasks to complete and access to google, vbaexpress and its vba articles and maybe a forum

this might be a interesting thing for playing around with without stressing you out much
if it doesn't open note that

"Private Declare Function" is "Private Declare ptrsafe Function" for 64bit excel

https://coral.ie.lehigh.edu/~ted/fil...isc/sample.xls

**mickcondon89** · 01-04-2018, 09:06 PM

Oh sorry about that!

I'll make sure I do that next time.

I would want the output alongside the max and min data. Pretty much in the same way as before. Even a seperate workbook is fine!

**jindon** · 01-04-2018, 09:28 PM

Not really clear.

Please Login or Register  to view this content.

Find Max and Min plus Date of each Max and Min using an excel script

LinkBack

Thread Tools

Rate This Thread

Display

Find Max and Min plus Date of each Max and Min using an excel script

Re: Find Max and Min plus Date of each Max and Min using an excel script

Re: Find Max and Min plus Date of each Max and Min using an excel script

Re: Find Max and Min plus Date of each Max and Min using an excel script

Re: Find Max and Min plus Date of each Max and Min using an excel script

Re: Find Max and Min plus Date of each Max and Min using an excel script

Re: Find Max and Min plus Date of each Max and Min using an excel script

Re: Find Max and Min plus Date of each Max and Min using an excel script

Re: Find Max and Min plus Date of each Max and Min using an excel script

Thread Information

Users Browsing this Thread

Similar Threads

Need VBA script for sending automatic emails when excel cell date equals current date

Need VB script for saving an excel file that appends the start of week date

[SOLVED] VB Script for saving excel file appending weekend's date to the filename

Format Date in Excel Vb Script to Datetime in SQL

Integrating Mainframe emulator script with excel's vba script

Create VBA script to short, dedicated button in excel for script?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions