Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Totally Stuck...Help Please!
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Totally Stuck...Help Please!
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!
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Totally confused - please help! | Excel Worksheet Functions | |||
Totally Stuck...Help Please! | Excel Programming | |||
Totally Stuck...Help Please! | Excel Programming | |||
totally newbie to VBA need help | Excel Programming | |||
Highlighting blanks via GO TO SPECIAL is not highlighting blank cells - HELP, I'm totally stuck. | Excel Discussion (Misc queries) |