Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Totally Stuck...Help Please!
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 -- Carlee |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Totally Stuck...Help Please!
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. "Carlee" wrote: In your first miracle, is it possible to save the new workbook created as "Copreco Reading" -- Carlee "JLatham" wrote: Glad the first wand-wave worked. Now, I presume that we are now dealing with copying row 2 from the new workbook, where we just copied the heading row and one row of data into (Sheet1) and are now going to copy that to yet a 3rd workbook? If that's the case we can modify the previous code just a little to see if that other workbook is open, open it if it isn't, find a specific sheet in it and take what we pasted into row 2 of the new book into the last row on the sheet in your master book. While I'm dealing with that, I'll wait for your response to this. Got a couple of VERY hot irons in the fire right now, so I can deal with those until I get notice you've replied and then I can put the code together properly for you. "Carlee" wrote: This worked beautifully. Now...I have yet another issue... This new book and data, I have created (thanks to you) will be sent via email to a user. I need to be able to extract the data sent (minus column headings) and import it into the last line of a master file. Can you wave your magic wand, once again? -- Carlee "JLatham" wrote: First thing I see is that the With Sheets() instruction has a different sheet name than you mention in your description. That sheet name should be the actual name of the sheet that your source data is on. You would also need a sheet named sheet16 in the workbook for it to function properly. But this all takes place in the same workbook, not between two separate workbooks (.xls files). The code here will copy 1st and last row of data on a sheet to Sheet1 in a new workbook. It works to some degree because we know that in a new workbook there is always a sheet named "Sheet1" and that any sheet has a row 1 and a row 2. I show this code within a regular Sub declaration, but you could copy the code between Sub ... and End Sub and replace the code in your Button2_Click routine with it. It works based on Button2 being in the same workbook with the source data. Where you see an instruction end with " _" it means that the instruction continues on the next row. Don't worry - Excel will deal with it just fine. I just wanted you to know what it means. Two things that may need changing: the Const sourceSheet should be set to = the exact name of the sheet that you want to copy row 1 and last row from. Later in the code where you see "A" - change that (if needed) to refer to a column that will always have information in that last row so that the program can find it. That line of code will find the last cell in the referenced column with information in that column. Sub CopyToNewWorkbook() Const sourceSheet = "Site Master Log" ' change?? Dim sourceBook As String Dim destBook As String Dim sourceRange As Range Dim destRange As Range 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" wrote: 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 -- Carlee |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Totally Stuck...Help Please!
I would prefer to have the program create a new workbook each time.
-- Carlee "JLatham" wrote: 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. "Carlee" wrote: In your first miracle, is it possible to save the new workbook created as "Copreco Reading" -- Carlee "JLatham" wrote: Glad the first wand-wave worked. Now, I presume that we are now dealing with copying row 2 from the new workbook, where we just copied the heading row and one row of data into (Sheet1) and are now going to copy that to yet a 3rd workbook? If that's the case we can modify the previous code just a little to see if that other workbook is open, open it if it isn't, find a specific sheet in it and take what we pasted into row 2 of the new book into the last row on the sheet in your master book. While I'm dealing with that, I'll wait for your response to this. Got a couple of VERY hot irons in the fire right now, so I can deal with those until I get notice you've replied and then I can put the code together properly for you. "Carlee" wrote: This worked beautifully. Now...I have yet another issue... This new book and data, I have created (thanks to you) will be sent via email to a user. I need to be able to extract the data sent (minus column headings) and import it into the last line of a master file. Can you wave your magic wand, once again? -- Carlee "JLatham" wrote: First thing I see is that the With Sheets() instruction has a different sheet name than you mention in your description. That sheet name should be the actual name of the sheet that your source data is on. You would also need a sheet named sheet16 in the workbook for it to function properly. But this all takes place in the same workbook, not between two separate workbooks (.xls files). The code here will copy 1st and last row of data on a sheet to Sheet1 in a new workbook. It works to some degree because we know that in a new workbook there is always a sheet named "Sheet1" and that any sheet has a row 1 and a row 2. I show this code within a regular Sub declaration, but you could copy the code between Sub ... and End Sub and replace the code in your Button2_Click routine with it. It works based on Button2 being in the same workbook with the source data. Where you see an instruction end with " _" it means that the instruction continues on the next row. Don't worry - Excel will deal with it just fine. I just wanted you to know what it means. Two things that may need changing: the Const sourceSheet should be set to = the exact name of the sheet that you want to copy row 1 and last row from. Later in the code where you see "A" - change that (if needed) to refer to a column that will always have information in that last row so that the program can find it. That line of code will find the last cell in the referenced column with information in that column. Sub CopyToNewWorkbook() Const sourceSheet = "Site Master Log" ' change?? Dim sourceBook As String Dim destBook As String Dim sourceRange As Range Dim destRange As Range 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" wrote: 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 -- Carlee |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Totally Stuck...Help Please!
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 -- urkec |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Totally Stuck...Help Please!
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 "JLatham" wrote: Try this - be sure and change the added Const values as needed for your actual setup. Everything you need to deal with should now be declared as a Const so that you can make those changes without having to go any further into the code. It is also set up to continue to work properly when you finally move up to Excel 2007 or later. In saving the new workbook with just the 2 rows of data (header and data) it will not prompt/warn you that you may be overwriting a file of the same name - it just does it. The Master workbook will be opened for use if it is not open at the start of all of this, and it will be closed before the process is complete unless it turns out that the master sheet in that workbook is full, in which case the new data won't get written to it, you'll get an alert message and the Master workbook will remain open. As before, if you want to associate it with a button in the source workbook, just copy the code between the Sub and End Sub statements into the button's _Click() event code area. Sub CopyToNewWorkbook() 'these have to do with THIS workbook 'name of the sheet to get data from Const sourceSheet = "Site Master 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:\folder\folder\MasterFile.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 "Carlee" wrote: In your first miracle, is it possible to save the new workbook created as "Copreco Reading" -- Carlee "JLatham" wrote: Glad the first wand-wave worked. Now, I presume that we are now dealing with copying row 2 from the new workbook, where we just copied the heading row and one row of data into (Sheet1) and are now going to copy that to yet a 3rd workbook? If that's the case we can modify the previous code just a little to see if that other workbook is open, open it if it isn't, find a specific sheet in it and take what we pasted into row 2 of the new book into the last row on the sheet in your master book. While I'm dealing with that, I'll wait for your response to this. Got a couple of VERY hot irons in the fire right now, so I can deal with those until I get notice you've replied and then I can put the code together properly for you. "Carlee" wrote: This worked beautifully. Now...I have yet another issue... This new book and data, I have created (thanks to you) will be sent via email to a user. I need to be able to extract the data sent (minus column headings) and import it into the last line of a master file. Can you wave your magic wand, once again? -- Carlee "JLatham" wrote: First thing I see is that the With Sheets() instruction has a different sheet name than you mention in your description. That sheet name should be the actual name of the sheet that your source data is on. You would also need a sheet named sheet16 in the workbook for it to function properly. But this all takes place in the same workbook, not between two separate workbooks (.xls files). The code here will copy 1st and last row of data on a sheet to Sheet1 in a new workbook. It works to some degree because we know that in a new workbook there is always a sheet named "Sheet1" and that any sheet has a row 1 and a row 2. I show this code within a regular Sub declaration, but you could copy the code between Sub ... and End Sub and replace the code in your Button2_Click routine with it. It works based on Button2 being in the same workbook with the source data. Where you see an instruction end with " _" it means that the instruction continues on the next row. Don't worry - Excel will deal with it just fine. I just wanted you to know what it means. Two things that may need changing: the Const sourceSheet should be set to = the exact name of the sheet that you want to copy row 1 and last row from. Later in the code where you see "A" - change that (if needed) to refer to a column that will always have information in that last row so that the program can find it. That line of code will find the last cell in the referenced column with information in that column. Sub CopyToNewWorkbook() Const sourceSheet = "Site Master Log" ' change?? Dim sourceBook As String Dim destBook As String Dim sourceRange As Range Dim destRange As Range 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" wrote: 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 -- Carlee |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Totally Stuck...Help Please!
I think part of my problem is that i am not sure how to declare the 'Cont'.
Thanks for being patient. It is appreciated. -- Carlee "JLatham" wrote: Try this - be sure and change the added Const values as needed for your actual setup. Everything you need to deal with should now be declared as a Const so that you can make those changes without having to go any further into the code. It is also set up to continue to work properly when you finally move up to Excel 2007 or later. In saving the new workbook with just the 2 rows of data (header and data) it will not prompt/warn you that you may be overwriting a file of the same name - it just does it. The Master workbook will be opened for use if it is not open at the start of all of this, and it will be closed before the process is complete unless it turns out that the master sheet in that workbook is full, in which case the new data won't get written to it, you'll get an alert message and the Master workbook will remain open. As before, if you want to associate it with a button in the source workbook, just copy the code between the Sub and End Sub statements into the button's _Click() event code area. Sub CopyToNewWorkbook() 'these have to do with THIS workbook 'name of the sheet to get data from Const sourceSheet = "Site Master 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:\folder\folder\MasterFile.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 "Carlee" wrote: In your first miracle, is it possible to save the new workbook created as "Copreco Reading" -- Carlee "JLatham" wrote: Glad the first wand-wave worked. Now, I presume that we are now dealing with copying row 2 from the new workbook, where we just copied the heading row and one row of data into (Sheet1) and are now going to copy that to yet a 3rd workbook? If that's the case we can modify the previous code just a little to see if that other workbook is open, open it if it isn't, find a specific sheet in it and take what we pasted into row 2 of the new book into the last row on the sheet in your master book. While I'm dealing with that, I'll wait for your response to this. Got a couple of VERY hot irons in the fire right now, so I can deal with those until I get notice you've replied and then I can put the code together properly for you. "Carlee" wrote: This worked beautifully. Now...I have yet another issue... This new book and data, I have created (thanks to you) will be sent via email to a user. I need to be able to extract the data sent (minus column headings) and import it into the last line of a master file. Can you wave your magic wand, once again? -- Carlee "JLatham" wrote: First thing I see is that the With Sheets() instruction has a different sheet name than you mention in your description. That sheet name should be the actual name of the sheet that your source data is on. You would also need a sheet named sheet16 in the workbook for it to function properly. But this all takes place in the same workbook, not between two separate workbooks (.xls files). The code here will copy 1st and last row of data on a sheet to Sheet1 in a new workbook. It works to some degree because we know that in a new workbook there is always a sheet named "Sheet1" and that any sheet has a row 1 and a row 2. I show this code within a regular Sub declaration, but you could copy the code between Sub ... and End Sub and replace the code in your Button2_Click routine with it. It works based on Button2 being in the same workbook with the source data. Where you see an instruction end with " _" it means that the instruction continues on the next row. Don't worry - Excel will deal with it just fine. I just wanted you to know what it means. Two things that may need changing: the Const sourceSheet should be set to = the exact name of the sheet that you want to copy row 1 and last row from. Later in the code where you see "A" - change that (if needed) to refer to a column that will always have information in that last row so that the program can find it. That line of code will find the last cell in the referenced column with information in that column. Sub CopyToNewWorkbook() Const sourceSheet = "Site Master Log" ' change?? Dim sourceBook As String Dim destBook As String Dim sourceRange As Range Dim destRange As Range 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" wrote: 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 -- Carlee |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Totally Stuck...Help Please!
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. "Carlee" wrote: I think part of my problem is that i am not sure how to declare the 'Cont'. Thanks for being patient. It is appreciated. -- Carlee "JLatham" wrote: Try this - be sure and change the added Const values as needed for your actual setup. Everything you need to deal with should now be declared as a Const so that you can make those changes without having to go any further into the code. It is also set up to continue to work properly when you finally move up to Excel 2007 or later. In saving the new workbook with just the 2 rows of data (header and data) it will not prompt/warn you that you may be overwriting a file of the same name - it just does it. The Master workbook will be opened for use if it is not open at the start of all of this, and it will be closed before the process is complete unless it turns out that the master sheet in that workbook is full, in which case the new data won't get written to it, you'll get an alert message and the Master workbook will remain open. As before, if you want to associate it with a button in the source workbook, just copy the code between the Sub and End Sub statements into the button's _Click() event code area. Sub CopyToNewWorkbook() 'these have to do with THIS workbook 'name of the sheet to get data from Const sourceSheet = "Site Master 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:\folder\folder\MasterFile.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 "Carlee" wrote: In your first miracle, is it possible to save the new workbook created as "Copreco Reading" -- Carlee "JLatham" wrote: Glad the first wand-wave worked. Now, I presume that we are now dealing with copying row 2 from the new workbook, where we just copied the heading row and one row of data into (Sheet1) and are now going to copy that to yet a 3rd workbook? If that's the case we can modify the previous code just a little to see if that other workbook is open, open it if it isn't, find a specific sheet in it and take what we pasted into row 2 of the new book into the last row on the sheet in your master book. While I'm dealing with that, I'll wait for your response to this. Got a couple of VERY hot irons in the fire right now, so I can deal with those until I get notice you've replied and then I can put the code together properly for you. "Carlee" wrote: This worked beautifully. Now...I have yet another issue... This new book and data, I have created (thanks to you) will be sent via email to a user. I need to be able to extract the data sent (minus column headings) and import it into the last line of a master file. Can you wave your magic wand, once again? -- Carlee "JLatham" wrote: First thing I see is that the With Sheets() instruction has a different sheet name than you mention in your description. That sheet name should be the actual name of the sheet that your source data is on. You would also need a sheet named sheet16 in the workbook for it to function properly. But this all takes place in the same workbook, not between two separate workbooks (.xls files). The code here will copy 1st and last row of data on a sheet to Sheet1 in a new workbook. It works to some degree because we know that in a new workbook there is always a sheet named "Sheet1" and that any sheet has a row 1 and a row 2. I show this code within a regular Sub declaration, but you could copy the code between Sub ... and End Sub and replace the code in your Button2_Click routine with it. It works based on Button2 being in the same workbook with the source data. Where you see an instruction end with " _" it means that the instruction continues on the next row. Don't worry - Excel will deal with it just fine. I just wanted you to know what it means. Two things that may need changing: the Const sourceSheet should be set to = the exact name of the sheet that you want to copy row 1 and last row from. Later in the code where you see "A" - change that (if needed) to refer to a column that will always have information in that last row so that the program can find it. That line of code will find the last cell in the referenced column with information in that column. Sub CopyToNewWorkbook() Const sourceSheet = "Site Master Log" ' change?? Dim sourceBook As String Dim destBook As String Dim sourceRange As Range Dim destRange As Range 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" wrote: 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 -- Carlee |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Totally Stuck...Help Please!
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!!!! -- Carlee "JLatham" wrote: 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. "Carlee" wrote: I think part of my problem is that i am not sure how to declare the 'Cont'. Thanks for being patient. It is appreciated. -- Carlee "JLatham" wrote: Try this - be sure and change the added Const values as needed for your actual setup. Everything you need to deal with should now be declared as a Const so that you can make those changes without having to go any further into the code. It is also set up to continue to work properly when you finally move up to Excel 2007 or later. In saving the new workbook with just the 2 rows of data (header and data) it will not prompt/warn you that you may be overwriting a file of the same name - it just does it. The Master workbook will be opened for use if it is not open at the start of all of this, and it will be closed before the process is complete unless it turns out that the master sheet in that workbook is full, in which case the new data won't get written to it, you'll get an alert message and the Master workbook will remain open. As before, if you want to associate it with a button in the source workbook, just copy the code between the Sub and End Sub statements into the button's _Click() event code area. Sub CopyToNewWorkbook() 'these have to do with THIS workbook 'name of the sheet to get data from Const sourceSheet = "Site Master 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:\folder\folder\MasterFile.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 "Carlee" wrote: In your first miracle, is it possible to save the new workbook created as "Copreco Reading" -- Carlee "JLatham" wrote: Glad the first wand-wave worked. Now, I presume that we are now dealing with copying row 2 from the new workbook, where we just copied the heading row and one row of data into (Sheet1) and are now going to copy that to yet a 3rd workbook? If that's the case we can modify the previous code just a little to see if that other workbook is open, open it if it isn't, find a specific sheet in it and take what we pasted into row 2 of the new book into the last row on the sheet in your master book. While I'm dealing with that, I'll wait for your response to this. Got a couple of VERY hot irons in the fire right now, so I can deal with those until I get notice you've replied and then I can put the code together properly for you. "Carlee" wrote: This worked beautifully. Now...I have yet another issue... This new book and data, I have created (thanks to you) will be sent via email to a user. I need to be able to extract the data sent (minus column headings) and import it into the last line of a master file. Can you wave your magic wand, once again? -- Carlee "JLatham" wrote: First thing I see is that the With Sheets() instruction has a different sheet name than you mention in your description. That sheet name should be the actual name of the sheet that your source data is on. You would also need a sheet named sheet16 in the workbook for it to function properly. But this all takes place in the same workbook, not between two separate workbooks (.xls files). The code here will copy 1st and last row of data on a sheet to Sheet1 in a new workbook. It works to some degree because we know that in a new workbook there is always a sheet named "Sheet1" and that any sheet has a row 1 and a row 2. I show this code within a regular Sub declaration, but you could copy the code between Sub ... and End Sub and replace the code in your Button2_Click routine with it. It works based on Button2 being in the same workbook with the source data. Where you see an instruction end with " _" it means that the instruction continues on the next row. Don't worry - Excel will deal with it just fine. I just wanted you to know what it means. Two things that may need changing: the Const sourceSheet should be set to = the exact name of the sheet that you want to copy row 1 and last row from. Later in the code where you see "A" - change that (if needed) to refer to a column that will always have information in that last row so that the program can find it. That line of code will find the last cell in the referenced column with information in that column. Sub CopyToNewWorkbook() Const sourceSheet = "Site Master Log" ' change?? Dim sourceBook As String Dim destBook As String Dim sourceRange As Range Dim destRange As Range 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" wrote: 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() |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Totally Stuck...Help Please!
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. "Carlee" wrote: 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!!!! -- Carlee "JLatham" wrote: 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. "Carlee" wrote: I think part of my problem is that i am not sure how to declare the 'Cont'. Thanks for being patient. It is appreciated. -- Carlee "JLatham" wrote: Try this - be sure and change the added Const values as needed for your actual setup. Everything you need to deal with should now be declared as a Const so that you can make those changes without having to go any further into the code. It is also set up to continue to work properly when you finally move up to Excel 2007 or later. In saving the new workbook with just the 2 rows of data (header and data) it will not prompt/warn you that you may be overwriting a file of the same name - it just does it. The Master workbook will be opened for use if it is not open at the start of all of this, and it will be closed before the process is complete unless it turns out that the master sheet in that workbook is full, in which case the new data won't get written to it, you'll get an alert message and the Master workbook will remain open. As before, if you want to associate it with a button in the source workbook, just copy the code between the Sub and End Sub statements into the button's _Click() event code area. Sub CopyToNewWorkbook() 'these have to do with THIS workbook 'name of the sheet to get data from Const sourceSheet = "Site Master 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:\folder\folder\MasterFile.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 "Carlee" wrote: In your first miracle, is it possible to save the new workbook created as "Copreco Reading" -- Carlee "JLatham" wrote: Glad the first wand-wave worked. Now, I presume that we are now dealing with copying row 2 from the new workbook, where we just copied the heading row and one row of data into (Sheet1) and are now going to copy that to yet a 3rd workbook? If that's the case we can modify the previous code just a little to see if that other workbook is open, open it if it isn't, find a specific sheet in it and take what we pasted into row 2 of the new book into the last row on the sheet in your master book. While I'm dealing with that, I'll wait for your response to this. Got a couple of VERY hot irons in the fire right now, so I can deal with those until I get notice you've replied and then I can put the code together properly for you. "Carlee" wrote: This worked beautifully. Now...I have yet another issue... This new book and data, I have created (thanks to you) will be sent via email to a user. I need to be able to extract the data sent (minus column headings) and import it into the last line of a master file. Can you wave your magic wand, once again? -- Carlee "JLatham" wrote: First thing I see is that the With Sheets() instruction has a different sheet name than you mention in your description. That sheet name should be the actual name of the sheet that your source data is on. You would also need a sheet named sheet16 in the workbook for it to function properly. But this all takes place in the same workbook, not between two separate workbooks (.xls files). The code here will copy 1st and last row of data on a sheet to Sheet1 in a new workbook. It works to some degree because we know that in a new workbook there is always a sheet named "Sheet1" and that any sheet has a row 1 and a row 2. I show this code within a regular Sub declaration, but you could copy the code between Sub ... and End Sub and replace the code in your Button2_Click routine with it. It works based on Button2 being in the same workbook with the source data. Where you see an instruction end with " _" it means that the instruction continues on the next row. Don't worry - Excel will deal with it just fine. I just wanted you to know what it means. Two things that may need changing: the Const sourceSheet should be set to = the exact name of the sheet that you want to copy row 1 and last row from. Later in the code where you see "A" - change that (if needed) to refer to a column that will always have information in that last row so that the program can find it. That line of code will find the last cell in the referenced column with information in that column. Sub CopyToNewWorkbook() Const sourceSheet = "Site Master Log" ' change?? Dim sourceBook As String Dim destBook As String Dim sourceRange As Range Dim destRange As Range 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" wrote: 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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Totally Stuck...Help Please!
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 -- Carlee "JLatham" wrote: 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. "Carlee" wrote: 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!!!! -- Carlee "JLatham" wrote: 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. "Carlee" wrote: I think part of my problem is that i am not sure how to declare the 'Cont'. Thanks for being patient. It is appreciated. -- Carlee "JLatham" wrote: Try this - be sure and change the added Const values as needed for your actual setup. Everything you need to deal with should now be declared as a Const so that you can make those changes without having to go any further into the code. It is also set up to continue to work properly when you finally move up to Excel 2007 or later. In saving the new workbook with just the 2 rows of data (header and data) it will not prompt/warn you that you may be overwriting a file of the same name - it just does it. The Master workbook will be opened for use if it is not open at the start of all of this, and it will be closed before the process is complete unless it turns out that the master sheet in that workbook is full, in which case the new data won't get written to it, you'll get an alert message and the Master workbook will remain open. As before, if you want to associate it with a button in the source workbook, just copy the code between the Sub and End Sub statements into the button's _Click() event code area. Sub CopyToNewWorkbook() 'these have to do with THIS workbook 'name of the sheet to get data from Const sourceSheet = "Site Master 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:\folder\folder\MasterFile.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 "Carlee" wrote: In your first miracle, is it possible to save the new workbook created as "Copreco Reading" -- Carlee "JLatham" wrote: Glad the first wand-wave worked. Now, I presume that we are now dealing with copying row 2 from the new workbook, where we just copied the heading row and one row of data into (Sheet1) and are now going to copy that to yet a 3rd workbook? If that's the case we can modify the previous code just a little to see if that other workbook is open, open it if it isn't, find a specific sheet in it and take what we pasted into row 2 of the new book into the last row on the sheet in your master book. While I'm dealing with that, I'll wait for your response to this. Got a couple of VERY hot irons in the fire right now, so I can deal with those until I get notice you've replied and then I can put the code together properly for you. "Carlee" wrote: This worked beautifully. Now...I have yet another issue... This new book and data, I have created (thanks to you) will be sent via email to a user. I need to be able to extract the data sent (minus column headings) and import it into the last line of a master file. Can you wave your magic wand, once again? -- Carlee "JLatham" wrote: First thing I see is that the With Sheets() instruction has a different sheet name than you mention in your description. That sheet name should be the actual name of the sheet that your source data is on. You would also need a sheet named sheet16 in the workbook for it to function properly. But this all takes place in the same workbook, not between two separate workbooks (.xls files). The code here will copy 1st and last row of data on a sheet to Sheet1 in a new workbook. It works to some degree because we know that in a new workbook there is always a sheet named "Sheet1" and that any sheet has a row 1 and a row 2. I show this code within a regular Sub declaration, but you could copy the code between Sub ... and End Sub and replace the code in your Button2_Click routine with it. It works based on Button2 being in the same workbook with the source data. Where you see an instruction end with " _" it means that the instruction continues on the next row. Don't worry - Excel will deal with it just fine. I just wanted you to know what it means. Two things that may need changing: the Const sourceSheet should be set to = the exact name of the sheet that you want to copy row 1 and last row from. Later in the code where you see "A" - change that (if needed) to refer to a column that will always have information in that last row so that the program can find it. That line of code will find the last cell in the referenced column with information in that column. Sub CopyToNewWorkbook() Const sourceSheet = "Site Master Log" ' change?? Dim sourceBook As String Dim destBook As String Dim sourceRange As Range Dim destRange As Range 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" wrote: Hi there, |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Totally Stuck...Help Please!
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. "Carlee" wrote: 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 -- Carlee "JLatham" wrote: 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. "Carlee" wrote: 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!!!! -- Carlee "JLatham" wrote: 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. "Carlee" wrote: I think part of my problem is that i am not sure how to declare the 'Cont'. Thanks for being patient. It is appreciated. -- Carlee "JLatham" wrote: Try this - be sure and change the added Const values as needed for your actual setup. Everything you need to deal with should now be declared as a Const so that you can make those changes without having to go any further into the code. It is also set up to continue to work properly when you finally move up to Excel 2007 or later. In saving the new workbook with just the 2 rows of data (header and data) it will not prompt/warn you that you may be overwriting a file of the same name - it just does it. The Master workbook will be opened for use if it is not open at the start of all of this, and it will be closed before the process is complete unless it turns out that the master sheet in that workbook is full, in which case the new data won't get written to it, you'll get an alert message and the Master workbook will remain open. As before, if you want to associate it with a button in the source workbook, just copy the code between the Sub and End Sub statements into the button's _Click() event code area. Sub CopyToNewWorkbook() 'these have to do with THIS workbook 'name of the sheet to get data from Const sourceSheet = "Site Master 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:\folder\folder\MasterFile.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") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Totally Stuck...Help Please! | Excel Programming | |||
Totally Stuck...Help Please! | Excel Programming | |||
Totally Stuck...Help Please! | Excel Programming | |||
Totally Stuck...Help Please! | Excel Programming | |||
Highlighting blanks via GO TO SPECIAL is not highlighting blank cells - HELP, I'm totally stuck. | Excel Discussion (Misc queries) |