Totally Stuck...Help Please!

[Guest]

Hi there,
I am pretty new to MS Excel, but this is what i want to do. I have asked
for help on this already, but haven't had much luck. I apprevciate everyones
patiences.

I have a 'Site Master Log' sheet which contains site readings. There are
many site readings.

I want to be able to click a button on my user form and have the last row of
'Site Master Log' copied (including column headers) and pasted into new
workbook. This new workbook will be used as an attachment for a subsequent
email.

The code i was given (see below) stated the 'subscript was out of range',
but i don't know what that means.

Thanks for your help

Sub Button2_Click()
With Sheets("Site Reading Log") 'source
..Rows(.Cells(Rows.Count, "a").End(xlUp).Row).Copy Sheets("sheet16").Rows(1)
'destination

End With


End Sub

 

[Guest]

Well, yes it is, but remember that then you'd constantly be overwriting the
existing Copreco Reading.xls file with the new one, if that's your intent,
then might be easier just to open the existing "Copreco Reading.xls" workbook
and overwrite the 2nd row of data.

 

[Guest]

I would prefer to have the program create a new workbook each time.

 

[Guest]

Sub Button2_Click()

With Sheets("Site Reading Log") 'source
.Rows(.Cells(Rows.Count, "a").End(xlUp).Row).Copy Sheets("sheet16").Rows(1)
'destination

End With


End Sub

I think you should break your code in steps to see which line gives you an
error:


Sub Button2_Click()

'Go to the first cell of the source sheet
Set rng = Worksheets("Source").Range("A1")

'Simulate ctrl + down arrow
'to get the index of the last row
rowToCopy = rng.End(xlDown).Row

'Copy the last row
Worksheets("Source").Rows(rowToCopy).Copy _
Worksheets("Dest").Rows(1)

End Sub

 

[Guest]

Thanks for the code. I am having some trouble figuring out what to do.
Below is the code I am using to copy the last line of the 'Site Reading Log'
to the new workbook. With this code, the new workbook is opend, but just
named 'Book'.

Where should the code you gave me (Const newWorkbookName = "Copreco Daily
Reading.xls") be placed so that it names the new workbook?

Sub ExportCoprecoReadingData()
Const sourceSheet = "Site Reading Log" ' source

Dim sourceBook As String
Dim destBook As String
Dim sourceRange As Range
Dim destRange As Range
Dim ans As Variant

Application.ScreenUpdating = False
sourceBook = ThisWorkbook.Name
Workbooks.Add ' create new book
destBook = ActiveWorkbook.Name
Windows(sourceBook).Activate
Worksheets(sourceSheet).Select

Set sourceRange = ActiveSheet.Rows("1:1")
Set destRange = Workbooks(destBook).Worksheets("Sheet1").Rows("1:1")
destRange.Value = sourceRange.Value

'change "A" to column with data in last row
Range("A" & Rows.Count).End(xlUp).Select
Set sourceRange = ActiveSheet.Rows(ActiveCell.Row & ":" & ActiveCell.Row)
Set destRange = Workbooks(destBook).Worksheets("Sheet1").Rows("2:2")
destRange.Value = sourceRange.Value
Set sourceRange = Nothing
Set destRange = Nothing

End Sub

Carlee

 

[Guest]

I think part of my problem is that i am not sure how to declare the 'Cont'.
Thanks for being patient. It is appreciated.

 

[Guest]

Just replace everything I gave you before with what I gave you this last
time. Then all you have to do is edit that code to make the various Const
objects refer to either real workbooks, worksheets or cells at your end. All
of those are string constants, so just change what is between the " " symbols
on the right side of the = sign.

You surely changed
Const sourceSheet = "Site Master Log"
before, right? Do it again? And I moved the "A" reference up out of the
code where I told you to change it before, so if you had to change it
previously, change it in this Const declaration:
Const sourceKeyColumn = "A" ' change A as required

I presumed when you said you wanted to save the newly created book as
"Copreco Reading" that you wanted to save the whole workbook, not just change
the name of a worksheet in it. So, Previously we did a Workbooks.Add which
created Book#.xls (where # is 1, 2 or some other number, depending on things)
But now we are going to save that book which has the header row and 1 row of
data in it using whatever name you assign to newWorkbookName, which I've
currently set to be "Copreco Reading.xls". So when we save the Book#.xls
file, it ceases to exist, becoming Copreco Reading.xls during the save
process.

Finally you're going to have to enter the full path to where ever that file
is that you want to keep adding entries to the end of and going to have to
give the name of the sheet that those entries are kept on. That is what
Const masterBook
and
Const masterSheet
are for
so if that workbook were in the root directory on your C: drive, and it was
named myMasterFile.xls
Then the one line should look like
Const masterBook = "C:\myMasterFile.xls"
Browse to it using either My Computer or My Network Places to be sure you
get the full path to it.

Finally, within that workbook there is some sheet that you want to put the
data into, and you need to set the constant masterSheet to that sheet's name.
If that sheet is simply 'Sheet1', fine, then make the declaration read as:
Const masterSheet = "Sheet1"

Hope this helps.

 

[Guest]

Hi there,
Thank you for being so kind with your time. I will take what you have
posted and see how it goes. If i have trouble, i will post again.

Many thanks again....you have no idea how much you are helping me out!!!!

 

[Guest]

As long as you're willing to try, I'm willing to continue trying to help.
May end up with you sending me at least the one initial workbook with the
code in it to me and me setting things up - but give the changes I've posted
a try.

 

[Guest]

Success!!!

So...I got it all working...I am so excited! But...there is a hitch.

The process you've helped me occures all at once, but what I need to do is
separate the processes, doing them individually. This is probably not the
most efficient, but what I need.

Part 1: User creates a submission using the UserForm I have created, and
clicks Submit
Data is inserted int he Site Reading Log (I have this
working already)
Copy the last row of Site Reading Log, to a New Workbook
Save New Workbook as Copreco Site Reading

User will take the saved 'Copreco Site Reading' workbook containing the one
row and email it to Headquarters as an attachment.

Part 2: Occurs at Head Quarters
When Head Quarters user opens email and finds sent Site Reading Log, he/she
will paste it to his/her Desktop and then:

Open Copreco Master Log (Spreadsheet held at Copreco
containing hundreds of rows)
Import Row of Data contained in Site Reading Log
(emailed workbook) to empty row at
bottom of worksheet

Issue:

is it possible to break apart the code you have provided to accomplish the
task individaully as i have described?


Here is the code i have working:

Sub CopyToNewWorkbook()
'name of the sheet to get data from
Const sourceSheet = "Site Reading Log" ' change??
'column that always have value in it in last row
Const sourceKeyColumn = "A"
'****
'This is the name you want to give to the
'NEW workbook created each time to put new data
'into as set up this code will overwrite any
'existing file of this name without any warning.
Const newWorkbookName = "Copreco Reading.xls"
'****
'these have to do with the MASTER workbook that
'keeps all data in it
'if this book is on a network drive/system
'then use the path to it as shown in My Network Places
'so that anyone using the workbook can use it reliably
'without having same drive mapping in effect, as:
' Const masterBook = "\\SharedSystem\folder\MasterFile.xls"
'need the full path and name here
'***change as required***
Const masterBook = "C:\\Bioteq\CoprecoMaster.xls"
'sheet in Master Workbook to add data to
' Change as required
Const masterSheet = "MasterSheet"

Dim sourceBook As String
Dim destBook As String
Dim sourceRange As Range
Dim destRange As Range
Dim masterBookLastRow As Long
Dim MaxLastRow As Long

'set up to use pre- and post-Excel 2007 files
If Val(Left(Application.Version, 2)) < 12 Then
'pre Excel 2007
MaxLastRow = Rows.Count
Else
'Excel 2007 (or later)
MaxLastRow = Rows.CountLarge
End If
Application.ScreenUpdating = False
sourceBook = ThisWorkbook.Name
Workbooks.Add ' create new book
destBook = ActiveWorkbook.Name
Windows(sourceBook).Activate
Worksheets(sourceSheet).Select

Set sourceRange = ActiveSheet.Rows("1:1")
Set destRange = Workbooks(destBook).Worksheets( _
"Sheet1").Rows("1:1")
destRange.Value = sourceRange.Value
Range(sourceKeyColumn & MaxLastRow).End(xlUp).Select
Set sourceRange = ActiveSheet.Rows( _
ActiveCell.Row & ":" & ActiveCell.Row)
Set destRange = Workbooks(destBook).Worksheets( _
"Sheet1").Rows("2:2")
destRange.Value = sourceRange.Value
Set destRange = Nothing
'save new workbook, but don't nag
'user with "file exists" message
Application.DisplayAlerts = False
With Workbooks(destBook)
.SaveAs newWorkbookName ' rename it
.Close
End With
Application.DisplayAlerts = True
'next trick
'larger bunny from smaller hat
'reuse destBook
destBook = Right(masterBook, Len(masterBook) - _
InStrRev(masterBook, Application.PathSeparator))
'see if Master Book is already open
On Error Resume Next
Windows(destBook).Activate
If Err <> 0 Then
'wasn't open, open it
Err.Clear
On Error GoTo 0
Workbooks.Open Filename:=masterBook
End If
On Error GoTo 0

masterBookLastRow = ActiveWorkbook.Sheets(masterSheet). _
Range(sourceKeyColumn & MaxLastRow).End(xlUp).Row + 1
If masterBookLastRow > MaxLastRow Then
MsgBox "Master Sheet is Full. Cannot add data." _
& vbOKOnly + vbCritical, "Aborting"
'exits, leaving both workbooks open
GoTo ExitCTNW
End If
Set destRange = Workbooks(destBook).Worksheets( _
masterSheet).Rows(masterBookLastRow & ":" & _
masterBookLastRow)
destRange.Value = sourceRange.Value ' new data added
Set destRange = Nothing
Application.DisplayAlerts = False
With Workbooks(destBook)
.Save
.Close
End With
ExitCTNW:
Set sourceRange = Nothing
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

 

[Guest]

Contact me at (remove spaces) HelpFrom @ jlathamsite.com
There is much to discuss here and we're going to have to re-arrange things
and create some stuff and find out just how things are set up at the
headquarters.

I probably won't even get to see the email until tomorrow evening either.

There's nothing left here for anyone following the thread to 'learn' - the
basic problem has been solved, and now we're looking at providing a custom
solution for your specific multi-location setup.