Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop
Excel 2010 Win 7
I have been trying to teach myself VBA. I am somewhat familiar with programming but I can't figure this out. Can someone point me in the right direction? Thanks Daphne I have a spreadsheet with information about members of a club. I need to copy some of the information to a new workbook depending on the value of cell D in each row. For example if the value of cell A in row 1 is "T" then copy cells B1 to E1 to cell B1 of the first empty row in a new workbook but if the value is "F" then go to the next row and repeat the routine until an empty row is found. A B C D E -------------------------------------------- T N Jim Smith 250-746-8888 F RN Susie Jones 250-749-2288 T RN Vanna White 250-743-1245 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop
On 10/22/2010 12:33 PM, Daphne Eze wrote:
Excel 2010 Win 7 I have been trying to teach myself VBA. I am somewhat familiar with programming but I can't figure this out. Can someone point me in the right direction? Thanks Daphne I have a spreadsheet with information about members of a club. I need to copy some of the information to a new workbook depending on the value of cell D in each row. For example if the value of cell A in row 1 is "T" then copy cells B1 to E1 to cell B1 of the first empty row in a new workbook but if the value is "F" then go to the next row and repeat the routine until an empty row is found. A B C D E -------------------------------------------- T N Jim Smith 250-746-8888 F RN Susie Jones 250-749-2288 T RN Vanna White 250-743-1245 Here are some pieces of example code that might help to get you started with the basics, I'm sure other people know a lot more about Excel here but this will probably get you close to what you need. For starters, do you know how to select a file or open a file from a file browse dialog? Do either of these help to select the target workbook where the data will be copied? http://www.exceltip.com/st/Select_fi...Excel/448.html Public Function FindFile() As String 'open browse window to select target workbook where data will be copied Dim fn As String fn = Application.GetOpenFilename("Excel-files,*.xls", 1, "Select File To Copy Data To", , False) If fn = "" Then MsgBox "Nothing Chosen" Else 'now that you have the name, you can open it or do something else 'return filename FindFile = fn 'show user filename they selected (just for testing MsgBox "You selected the file """ & fn & """", vbOKOnly, "Target Workbook" End If End Function Sub OpenOneSelectedExcelFile() Dim fn As Variant fn = Application.GetOpenFilename("Excel-files,*.xls", 1, "Select One File To Open", , False) If TypeName(fn) = "Boolean" Then ' the user didn't select a file Exit Sub End If Debug.Print "Selected file: " & fn Workbooks.Open fn End Sub '-------------------------------------------------------------------------- http://en.allexperts.com/q/Excel-105...a-workbook.htm This code example allow you to select a second worksheet from an opened worksheet that contains this code, and copy data from the first to the selected sheets. Public Function FindFile() As String 'browse to select target workbook where data will be copied 'right now it only shows files with the xls file extension Dim fn As String fn = Application.GetOpenFilename("Excel-files,*.xls", 1, "Select File To Copy Data To", , False) If fn = "" Then exit function Else 'return filename FindFile = fn End If End Function Public Sub CopyDataToTargetBook() 'declare variables Dim sh1 As Worksheet, sh2 As Worksheet, bk2 As Workbook Dim row As Long, col As Long '-------------------------------------------------------- 'refer to sheet in open book data will be copied from Set sh1 = ActiveSheet Application.ScreenUpdating = False '-------------------------------------------------------- 'open sheet data will be copied to Set bk2 = Workbooks.Open(FindFile) 'target sheet where data will be copied Set sh2 = bk2.Sheets(1) 'arbitrary copy code example, not using your conditions For row = 1 To 8 For col = 1 To 3 sh2.Cells(row, col).Value = sh1.Cells(row, col) Next Next 'save the book with the copied data bk2.Close Savechanges:=True Application.ScreenUpdating = True '--------------------------------------------------------- set sh1=nothing set sh2=nothing set bk2=nothing End Sub Does that get you started? Mike |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop
I'm sorry. I don't think I was clear enough on what I needed. I know how to open or create another workbook or worksheet. What I don't know how to do is find out what the value of cell "A1" is and then if the value is "T" copy the row to a new workbook or worksheet But if the value of cell "A1" is "F" skip that row and move down a row and continue the process until all the rows with data in them have been processed. On 2010-10-22 2:57 PM, Mike S wrote: On 10/22/2010 12:33 PM, Daphne Eze wrote: Excel 2010 Win 7 I have been trying to teach myself VBA. I am somewhat familiar with programming but I can't figure this out. Can someone point me in the right direction? Thanks Daphne I have a spreadsheet with information about members of a club. I need to copy some of the information to a new workbook depending on the value of cell D in each row. For example if the value of cell A in row 1 is "T" then copy cells B1 to E1 to cell B1 of the first empty row in a new workbook but if the value is "F" then go to the next row and repeat the routine until an empty row is found. A B C D E -------------------------------------------- T N Jim Smith 250-746-8888 F RN Susie Jones 250-749-2288 T RN Vanna White 250-743-1245 Here are some pieces of example code that might help to get you started with the basics, I'm sure other people know a lot more about Excel here but this will probably get you close to what you need. For starters, do you know how to select a file or open a file from a file browse dialog? Do either of these help to select the target workbook where the data will be copied? http://www.exceltip.com/st/Select_fi...Excel/448.html Public Function FindFile() As String 'open browse window to select target workbook where data will be copied Dim fn As String fn = Application.GetOpenFilename("Excel-files,*.xls", 1, "Select File To Copy Data To", , False) If fn = "" Then MsgBox "Nothing Chosen" Else 'now that you have the name, you can open it or do something else 'return filename FindFile = fn 'show user filename they selected (just for testing MsgBox "You selected the file """ & fn & """", vbOKOnly, "Target Workbook" End If End Function Sub OpenOneSelectedExcelFile() Dim fn As Variant fn = Application.GetOpenFilename("Excel-files,*.xls", 1, "Select One File To Open", , False) If TypeName(fn) = "Boolean" Then ' the user didn't select a file Exit Sub End If Debug.Print "Selected file: " & fn Workbooks.Open fn End Sub '-------------------------------------------------------------------------- http://en.allexperts.com/q/Excel-105...a-workbook.htm This code example allow you to select a second worksheet from an opened worksheet that contains this code, and copy data from the first to the selected sheets. Public Function FindFile() As String 'browse to select target workbook where data will be copied 'right now it only shows files with the xls file extension Dim fn As String fn = Application.GetOpenFilename("Excel-files,*.xls", 1, "Select File To Copy Data To", , False) If fn = "" Then exit function Else 'return filename FindFile = fn End If End Function Public Sub CopyDataToTargetBook() 'declare variables Dim sh1 As Worksheet, sh2 As Worksheet, bk2 As Workbook Dim row As Long, col As Long '-------------------------------------------------------- 'refer to sheet in open book data will be copied from Set sh1 = ActiveSheet Application.ScreenUpdating = False '-------------------------------------------------------- 'open sheet data will be copied to Set bk2 = Workbooks.Open(FindFile) 'target sheet where data will be copied Set sh2 = bk2.Sheets(1) 'arbitrary copy code example, not using your conditions For row = 1 To 8 For col = 1 To 3 sh2.Cells(row, col).Value = sh1.Cells(row, col) Next Next 'save the book with the copied data bk2.Close Savechanges:=True Application.ScreenUpdating = True '--------------------------------------------------------- set sh1=nothing set sh2=nothing set bk2=nothing End Sub Does that get you started? Mike |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop
On 10/22/2010 4:24 PM, Daphne Eze wrote:
I'm sorry. I don't think I was clear enough on what I needed. I know how to open or create another workbook or worksheet. The first code example lets you select another worksheet that already exists. What I don't know how to do is find out what the value of cell "A1" is and then if the value is "T" copy the row to a new workbook or worksheet The CopyDataToTargetBook code example shows you how to read and copy the contents of a cell. But if the value of cell "A1" is "F" skip that row and move down a row and continue the process until all the rows with data in them have been processed. That will be easy once you get all of the basics in place. Do you know how to enter code and run it? Do you know how to step through it one line at at time so you can watch what's happening very closely? And do you know how to use the debug.print immediate window? Mike On 2010-10-22 2:57 PM, Mike S wrote: On 10/22/2010 12:33 PM, Daphne Eze wrote: Excel 2010 Win 7 I have been trying to teach myself VBA. I am somewhat familiar with programming but I can't figure this out. Can someone point me in the right direction? Thanks Daphne I have a spreadsheet with information about members of a club. I need to copy some of the information to a new workbook depending on the value of cell D in each row. For example if the value of cell A in row 1 is "T" then copy cells B1 to E1 to cell B1 of the first empty row in a new workbook but if the value is "F" then go to the next row and repeat the routine until an empty row is found. A B C D E -------------------------------------------- T N Jim Smith 250-746-8888 F RN Susie Jones 250-749-2288 T RN Vanna White 250-743-1245 Here are some pieces of example code that might help to get you started with the basics, I'm sure other people know a lot more about Excel here but this will probably get you close to what you need. For starters, do you know how to select a file or open a file from a file browse dialog? Do either of these help to select the target workbook where the data will be copied? http://www.exceltip.com/st/Select_fi...Excel/448.html Public Function FindFile() As String 'open browse window to select target workbook where data will be copied Dim fn As String fn = Application.GetOpenFilename("Excel-files,*.xls", 1, "Select File To Copy Data To", , False) If fn = "" Then MsgBox "Nothing Chosen" Else 'now that you have the name, you can open it or do something else 'return filename FindFile = fn 'show user filename they selected (just for testing MsgBox "You selected the file """ & fn & """", vbOKOnly, "Target Workbook" End If End Function Sub OpenOneSelectedExcelFile() Dim fn As Variant fn = Application.GetOpenFilename("Excel-files,*.xls", 1, "Select One File To Open", , False) If TypeName(fn) = "Boolean" Then ' the user didn't select a file Exit Sub End If Debug.Print "Selected file: " & fn Workbooks.Open fn End Sub '-------------------------------------------------------------------------- http://en.allexperts.com/q/Excel-105...a-workbook.htm This code example allow you to select a second worksheet from an opened worksheet that contains this code, and copy data from the first to the selected sheets. Public Function FindFile() As String 'browse to select target workbook where data will be copied 'right now it only shows files with the xls file extension Dim fn As String fn = Application.GetOpenFilename("Excel-files,*.xls", 1, "Select File To Copy Data To", , False) If fn = "" Then exit function Else 'return filename FindFile = fn End If End Function Public Sub CopyDataToTargetBook() 'declare variables Dim sh1 As Worksheet, sh2 As Worksheet, bk2 As Workbook Dim row As Long, col As Long '-------------------------------------------------------- 'refer to sheet in open book data will be copied from Set sh1 = ActiveSheet Application.ScreenUpdating = False '-------------------------------------------------------- 'open sheet data will be copied to Set bk2 = Workbooks.Open(FindFile) 'target sheet where data will be copied Set sh2 = bk2.Sheets(1) 'arbitrary copy code example, not using your conditions For row = 1 To 8 For col = 1 To 3 sh2.Cells(row, col).Value = sh1.Cells(row, col) Next Next 'save the book with the copied data bk2.Close Savechanges:=True Application.ScreenUpdating = True '--------------------------------------------------------- set sh1=nothing set sh2=nothing set bk2=nothing End Sub Does that get you started? Mike |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop
On Oct 22, 5:24*pm, Daphne Eze wrote:
I'm sorry. I don't think I was clear enough on what I needed. I know how to open or create another workbook or worksheet. What I don't know how to do is find out what the value of cell "A1" is and then if the value is "T" copy the row to a new workbook or worksheet But if the value of cell "A1" is "F" skip that row and move down a row and continue the process until all the rows with data in them have been processed. On 2010-10-22 2:57 PM, Mike S wrote: On 10/22/2010 12:33 PM, Daphne Eze wrote: Excel 2010 Win 7 I have been trying to teach myself VBA. I am somewhat familiar with programming but I can't figure this out. Can someone point me in the right direction? Thanks Daphne I have a spreadsheet with information about members of a club. I need to copy some of the information to a new workbook depending on the value of cell D in each row. For example if the value of cell A in row 1 is "T" then copy cells B1 to E1 to cell B1 of the first empty row in a new workbook but if the value is "F" then go to the next row and repeat the routine until an empty row is found. A B C D E -------------------------------------------- T N Jim Smith 250-746-8888 F RN Susie Jones 250-749-2288 T RN Vanna White 250-743-1245 Here are some pieces of example code that might help to get you started with the basics, I'm sure other people know a lot more about Excel here but this will probably get you close to what you need. For starters, do you know how to select a file or open a file from a file browse dialog? Do either of these help to select the target workbook where the data will be copied? http://www.exceltip.com/st/Select_fi...n_Microsoft_Ex... Public Function FindFile() As String 'open browse window to select target workbook where data will be copied Dim fn As String fn = Application.GetOpenFilename("Excel-files,*.xls", 1, "Select File To Copy Data To", , False) If fn = "" Then MsgBox "Nothing Chosen" Else 'now that you have the name, you can open it or do something else 'return filename FindFile = fn 'show user filename they selected (just for testing MsgBox "You selected the file """ & fn & """", vbOKOnly, "Target Workbook" End If End Function Sub OpenOneSelectedExcelFile() Dim fn As Variant fn = Application.GetOpenFilename("Excel-files,*.xls", 1, "Select One File To Open", , False) If TypeName(fn) = "Boolean" Then ' the user didn't select a file Exit Sub End If Debug.Print "Selected file: " & fn Workbooks.Open fn End Sub '-------------------------------------------------------------------------- http://en.allexperts.com/q/Excel-105...-data-workbook... This code example allow you to select a second worksheet from an opened worksheet that contains this code, and copy data from the first to the selected sheets. Public Function FindFile() As String 'browse to select target workbook where data will be copied 'right now it only shows files with the xls file extension Dim fn As String fn = Application.GetOpenFilename("Excel-files,*.xls", 1, "Select File To Copy Data To", , False) If fn = "" Then exit function Else 'return filename FindFile = fn End If End Function Public Sub CopyDataToTargetBook() 'declare variables Dim sh1 As Worksheet, sh2 As Worksheet, bk2 As Workbook Dim row As Long, col As Long '-------------------------------------------------------- 'refer to sheet in open book data will be copied from Set sh1 = ActiveSheet Application.ScreenUpdating = False '-------------------------------------------------------- 'open sheet data will be copied to Set bk2 = Workbooks.Open(FindFile) 'target sheet where data will be copied Set sh2 = bk2.Sheets(1) 'arbitrary copy code example, not using your conditions For row = 1 To 8 For col = 1 To 3 sh2.Cells(row, col).Value = sh1.Cells(row, col) Next Next 'save the book with the copied data bk2.Close Savechanges:=True Application.ScreenUpdating = True '--------------------------------------------------------- set sh1=nothing set sh2=nothing set bk2=nothing End Sub Does that get you started? Mike Here is how I would do it. I am sort of old school and I like loops. There are many better ways to do this, but it seems easier to understand using fundamental programming constructs. Once you have the concept down, you can make it as fancy as you want. This code copies from one sheet called "data" to another sheet called "file". You can modify this to copy anywhere. ' First find out how many rows are filled with data in your sheet ' Once you run this you will know your last row of data For k = 1 To 10000 If IsEmpty(Cells(k, 1)) = True Then endrow = k - 1 Exit For End If Next Cells(10, 10) = endrow ' this is put a value on the sheet so you can verify that the code has actually found the last row of data ' Now that you have endrow, you can check the values of each cell is column 1 to see if it equals "T" row_data = 0 ' row_data will be used for copying to the proper row on the next sheet For k = 1 To endrow ' this covers all data in the table If Cells(k, 1) = "T" Then ' check to see if cell value = "T" row_data = row_data + 1 'if it does, increment rwo_data Sheets("file").Cells(row_data, 1) = Sheets("data").Cells(k, 2) ' copy over cells. The copied cells will be in row k, the destination row will be row_data Sheets("file").Cells(row_data, 2) = Sheets("data").Cells(k, 3) Sheets("file").Cells(row_data, 3) = Sheets("data").Cells(k, 4) End If Next |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop
On 10/23/2010 8:54 AM, Andrew wrote:
On Oct 22, 5:24 pm, Daphne wrote: I'm sorry. I don't think I was clear enough on what I needed. I know how to open or create another workbook or worksheet. What I don't know how to do is find out what the value of cell "A1" is and then if the value is "T" copy the row to a new workbook or worksheet But if the value of cell "A1" is "F" skip that row and move down a row and continue the process until all the rows with data in them have been processed. On 2010-10-22 2:57 PM, Mike S wrote: On 10/22/2010 12:33 PM, Daphne Eze wrote: Excel 2010 Win 7 I have been trying to teach myself VBA. I am somewhat familiar with programming but I can't figure this out. Can someone point me in the right direction? Thanks Daphne I have a spreadsheet with information about members of a club. I need to copy some of the information to a new workbook depending on the value of cell D in each row. For example if the value of cell A in row 1 is "T" then copy cells B1 to E1 to cell B1 of the first empty row in a new workbook but if the value is "F" then go to the next row and repeat the routine until an empty row is found. A B C D E -------------------------------------------- T N Jim Smith 250-746-8888 F RN Susie Jones 250-749-2288 T RN Vanna White 250-743-1245 Here are some pieces of example code that might help to get you started with the basics, I'm sure other people know a lot more about Excel here but this will probably get you close to what you need. For starters, do you know how to select a file or open a file from a file browse dialog? Do either of these help to select the target workbook where the data will be copied? http://www.exceltip.com/st/Select_fi...n_Microsoft_Ex... Public Function FindFile() As String 'open browse window to select target workbook where data will be copied Dim fn As String fn = Application.GetOpenFilename("Excel-files,*.xls", 1, "Select File To Copy Data To", , False) If fn = "" Then MsgBox "Nothing Chosen" Else 'now that you have the name, you can open it or do something else 'return filename FindFile = fn 'show user filename they selected (just for testing MsgBox "You selected the file """& fn& """", vbOKOnly, "Target Workbook" End If End Function Sub OpenOneSelectedExcelFile() Dim fn As Variant fn = Application.GetOpenFilename("Excel-files,*.xls", 1, "Select One File To Open", , False) If TypeName(fn) = "Boolean" Then ' the user didn't select a file Exit Sub End If Debug.Print "Selected file: "& fn Workbooks.Open fn End Sub '-------------------------------------------------------------------------- http://en.allexperts.com/q/Excel-105...-data-workbook... This code example allow you to select a second worksheet from an opened worksheet that contains this code, and copy data from the first to the selected sheets. Public Function FindFile() As String 'browse to select target workbook where data will be copied 'right now it only shows files with the xls file extension Dim fn As String fn = Application.GetOpenFilename("Excel-files,*.xls", 1, "Select File To Copy Data To", , False) If fn = "" Then exit function Else 'return filename FindFile = fn End If End Function Public Sub CopyDataToTargetBook() 'declare variables Dim sh1 As Worksheet, sh2 As Worksheet, bk2 As Workbook Dim row As Long, col As Long '-------------------------------------------------------- 'refer to sheet in open book data will be copied from Set sh1 = ActiveSheet Application.ScreenUpdating = False '-------------------------------------------------------- 'open sheet data will be copied to Set bk2 = Workbooks.Open(FindFile) 'target sheet where data will be copied Set sh2 = bk2.Sheets(1) 'arbitrary copy code example, not using your conditions For row = 1 To 8 For col = 1 To 3 sh2.Cells(row, col).Value = sh1.Cells(row, col) Next Next 'save the book with the copied data bk2.Close Savechanges:=True Application.ScreenUpdating = True '--------------------------------------------------------- set sh1=nothing set sh2=nothing set bk2=nothing End Sub Does that get you started? Mike Here is how I would do it. I am sort of old school and I like loops. There are many better ways to do this, but it seems easier to understand using fundamental programming constructs. Once you have the concept down, you can make it as fancy as you want. This code copies from one sheet called "data" to another sheet called "file". You can modify this to copy anywhere. ' First find out how many rows are filled with data in your sheet ' Once you run this you will know your last row of data For k = 1 To 10000 If IsEmpty(Cells(k, 1)) = True Then endrow = k - 1 Exit For End If Next Cells(10, 10) = endrow ' this is put a value on the sheet so you can verify that the code has actually found the last row of data ' Now that you have endrow, you can check the values of each cell is column 1 to see if it equals "T" row_data = 0 ' row_data will be used for copying to the proper row on the next sheet For k = 1 To endrow ' this covers all data in the table If Cells(k, 1) = "T" Then ' check to see if cell value = "T" row_data = row_data + 1 'if it does, increment rwo_data Sheets("file").Cells(row_data, 1) = Sheets("data").Cells(k, 2) ' copy over cells. The copied cells will be in row k, the destination row will be row_data Sheets("file").Cells(row_data, 2) = Sheets("data").Cells(k, 3) Sheets("file").Cells(row_data, 3) = Sheets("data").Cells(k, 4) End If Next That looks great! I think you're right, I was approaching it wrong and making it seem harder. Good job. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop
Thanks you Andrew and Mike. This is what I wanted.
I'm working my way thro' the code. Because I'm just learning VBA I need to look up all the codes to understand what each one does and how it is used. I will probably have more questions. Cheers Daphne On 2010-10-23 1:18 PM, Mike S wrote: On 10/23/2010 8:54 AM, Andrew wrote: On Oct 22, 5:24 pm, Daphne wrote: I'm sorry. I don't think I was clear enough on what I needed. I know how to open or create another workbook or worksheet. What I don't know how to do is find out what the value of cell "A1" is and then if the value is "T" copy the row to a new workbook or worksheet But if the value of cell "A1" is "F" skip that row and move down a row and continue the process until all the rows with data in them have been processed. On 2010-10-22 2:57 PM, Mike S wrote: On 10/22/2010 12:33 PM, Daphne Eze wrote: Excel 2010 Win 7 I have been trying to teach myself VBA. I am somewhat familiar with programming but I can't figure this out. Can someone point me in the right direction? Thanks Daphne I have a spreadsheet with information about members of a club. I need to copy some of the information to a new workbook depending on the value of cell D in each row. For example if the value of cell A in row 1 is "T" then copy cells B1 to E1 to cell B1 of the first empty row in a new workbook but if the value is "F" then go to the next row and repeat the routine until an empty row is found. A B C D E -------------------------------------------- T N Jim Smith 250-746-8888 F RN Susie Jones 250-749-2288 T RN Vanna White 250-743-1245 Here are some pieces of example code that might help to get you started with the basics, I'm sure other people know a lot more about Excel here but this will probably get you close to what you need. For starters, do you know how to select a file or open a file from a file browse dialog? Do either of these help to select the target workbook where the data will be copied? http://www.exceltip.com/st/Select_fi...n_Microsoft_Ex... Public Function FindFile() As String 'open browse window to select target workbook where data will be copied Dim fn As String fn = Application.GetOpenFilename("Excel-files,*.xls", 1, "Select File To Copy Data To", , False) If fn = "" Then MsgBox "Nothing Chosen" Else 'now that you have the name, you can open it or do something else 'return filename FindFile = fn 'show user filename they selected (just for testing MsgBox "You selected the file """& fn& """", vbOKOnly, "Target Workbook" End If End Function Sub OpenOneSelectedExcelFile() Dim fn As Variant fn = Application.GetOpenFilename("Excel-files,*.xls", 1, "Select One File To Open", , False) If TypeName(fn) = "Boolean" Then ' the user didn't select a file Exit Sub End If Debug.Print "Selected file: "& fn Workbooks.Open fn End Sub '-------------------------------------------------------------------------- http://en.allexperts.com/q/Excel-105...-data-workbook... This code example allow you to select a second worksheet from an opened worksheet that contains this code, and copy data from the first to the selected sheets. Public Function FindFile() As String 'browse to select target workbook where data will be copied 'right now it only shows files with the xls file extension Dim fn As String fn = Application.GetOpenFilename("Excel-files,*.xls", 1, "Select File To Copy Data To", , False) If fn = "" Then exit function Else 'return filename FindFile = fn End If End Function Public Sub CopyDataToTargetBook() 'declare variables Dim sh1 As Worksheet, sh2 As Worksheet, bk2 As Workbook Dim row As Long, col As Long '-------------------------------------------------------- 'refer to sheet in open book data will be copied from Set sh1 = ActiveSheet Application.ScreenUpdating = False '-------------------------------------------------------- 'open sheet data will be copied to Set bk2 = Workbooks.Open(FindFile) 'target sheet where data will be copied Set sh2 = bk2.Sheets(1) 'arbitrary copy code example, not using your conditions For row = 1 To 8 For col = 1 To 3 sh2.Cells(row, col).Value = sh1.Cells(row, col) Next Next 'save the book with the copied data bk2.Close Savechanges:=True Application.ScreenUpdating = True '--------------------------------------------------------- set sh1=nothing set sh2=nothing set bk2=nothing End Sub Does that get you started? Mike Here is how I would do it. I am sort of old school and I like loops. There are many better ways to do this, but it seems easier to understand using fundamental programming constructs. Once you have the concept down, you can make it as fancy as you want. This code copies from one sheet called "data" to another sheet called "file". You can modify this to copy anywhere. ' First find out how many rows are filled with data in your sheet ' Once you run this you will know your last row of data For k = 1 To 10000 If IsEmpty(Cells(k, 1)) = True Then endrow = k - 1 Exit For End If Next Cells(10, 10) = endrow ' this is put a value on the sheet so you can verify that the code has actually found the last row of data ' Now that you have endrow, you can check the values of each cell is column 1 to see if it equals "T" row_data = 0 ' row_data will be used for copying to the proper row on the next sheet For k = 1 To endrow ' this covers all data in the table If Cells(k, 1) = "T" Then ' check to see if cell value = "T" row_data = row_data + 1 'if it does, increment rwo_data Sheets("file").Cells(row_data, 1) = Sheets("data").Cells(k, 2) ' copy over cells. The copied cells will be in row k, the destination row will be row_data Sheets("file").Cells(row_data, 2) = Sheets("data").Cells(k, 3) Sheets("file").Cells(row_data, 3) = Sheets("data").Cells(k, 4) End If Next That looks great! I think you're right, I was approaching it wrong and making it seem harder. Good job. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop
On Oct 23, 6:57*pm, Daphne Eze wrote:
Thanks you Andrew and Mike. This is what I wanted. I'm working my way thro' the code. Because I'm just learning VBA I need to look up all the codes to understand what each one does and how it is used. I will probably have more questions. Cheers Daphne On 2010-10-23 1:18 PM, Mike S wrote: On 10/23/2010 8:54 AM, Andrew wrote: On Oct 22, 5:24 pm, Daphne wrote: I'm sorry. I don't think I was clear enough on what I needed. I know how to open or create another workbook or worksheet. What I don't know how to do is find out what the value of cell "A1" is and then if the value is "T" copy the row to a new workbook or worksheet But if the value of cell "A1" is "F" skip that row and move down a row and continue the process until all the rows with data in them have been processed. On 2010-10-22 2:57 PM, Mike S wrote: On 10/22/2010 12:33 PM, Daphne Eze wrote: Excel 2010 Win 7 I have been trying to teach myself VBA. I am somewhat familiar with programming but I can't figure this out. Can someone point me in the right direction? Thanks Daphne I have a spreadsheet with information about members of a club. I need to copy some of the information to a new workbook depending on the value of cell D in each row. For example if the value of cell A in row 1 is "T" then copy cells B1 to E1 to cell B1 of the first empty row in a new workbook but if the value is "F" then go to the next row and repeat the routine until an empty row is found. A B C D E -------------------------------------------- T N Jim Smith 250-746-8888 F RN Susie Jones 250-749-2288 T RN Vanna White 250-743-1245 Here are some pieces of example code that might help to get you started with the basics, I'm sure other people know a lot more about Excel here but this will probably get you close to what you need. For starters, do you know how to select a file or open a file from a file browse dialog? Do either of these help to select the target workbook where the data will be copied? http://www.exceltip.com/st/Select_fi...n_Microsoft_Ex.... Public Function FindFile() As String 'open browse window to select target workbook where data will be copied Dim fn As String fn = Application.GetOpenFilename("Excel-files,*.xls", 1, "Select File To Copy Data To", , False) If fn = "" Then MsgBox "Nothing Chosen" Else 'now that you have the name, you can open it or do something else 'return filename FindFile = fn 'show user filename they selected (just for testing MsgBox "You selected the file """& fn& """", vbOKOnly, "Target Workbook" End If End Function Sub OpenOneSelectedExcelFile() Dim fn As Variant fn = Application.GetOpenFilename("Excel-files,*.xls", 1, "Select One File To Open", , False) If TypeName(fn) = "Boolean" Then ' the user didn't select a file Exit Sub End If Debug.Print "Selected file: "& fn Workbooks.Open fn End Sub '-------------------------------------------------------------------------- http://en.allexperts.com/q/Excel-105...-data-workbook.... This code example allow you to select a second worksheet from an opened worksheet that contains this code, and copy data from the first to the selected sheets. Public Function FindFile() As String 'browse to select target workbook where data will be copied 'right now it only shows files with the xls file extension Dim fn As String fn = Application.GetOpenFilename("Excel-files,*.xls", 1, "Select File To Copy Data To", , False) If fn = "" Then exit function Else 'return filename FindFile = fn End If End Function Public Sub CopyDataToTargetBook() 'declare variables Dim sh1 As Worksheet, sh2 As Worksheet, bk2 As Workbook Dim row As Long, col As Long '-------------------------------------------------------- 'refer to sheet in open book data will be copied from Set sh1 = ActiveSheet Application.ScreenUpdating = False '-------------------------------------------------------- 'open sheet data will be copied to Set bk2 = Workbooks.Open(FindFile) 'target sheet where data will be copied Set sh2 = bk2.Sheets(1) 'arbitrary copy code example, not using your conditions For row = 1 To 8 For col = 1 To 3 sh2.Cells(row, col).Value = sh1.Cells(row, col) Next Next 'save the book with the copied data bk2.Close Savechanges:=True Application.ScreenUpdating = True '--------------------------------------------------------- set sh1=nothing set sh2=nothing set bk2=nothing End Sub Does that get you started? Mike Here is how I would do it. I am sort of old school and I like loops. There are many better ways to do this, but it seems easier to understand using fundamental programming constructs. Once you have the concept down, you can make it as fancy as you want. This code copies from one sheet called "data" to another sheet called "file". You can modify this to copy anywhere. ' First find out how many rows are filled with data in your sheet ' Once you run this you will know your last row of data For k = 1 To 10000 If IsEmpty(Cells(k, 1)) = True Then endrow = k - 1 Exit For End If Next Cells(10, 10) = endrow ' this is put a value on the sheet so you can verify that the code has actually found the last row of data ' Now that you have endrow, you can check the values of each cell is column 1 to see if it equals "T" row_data = 0 ' row_data will be used for copying to the proper row on the next sheet For k = 1 To endrow ' this covers all data in the table If Cells(k, 1) = "T" Then ' check to see if cell value = "T" row_data = row_data + 1 'if it does, increment rwo_data Sheets("file").Cells(row_data, 1) = Sheets("data").Cells(k, 2) ' copy over cells. The copied cells will be in row k, the destination row will be row_data Sheets("file").Cells(row_data, 2) = Sheets("data").Cells(k, 3) Sheets("file").Cells(row_data, 3) = Sheets("data").Cells(k, 4) End If Next That looks great! I think you're right, I was approaching it wrong and making it seem harder. Good job. Daphne, one good way to understand this (or any) code is to run it in monitor mode. You can do this by going to the VBA code and then pressing F8. Each press of F8 and the code will increment itself by one line of instruction. After each line is executed, you can hover your mouse over the program variables. Their values will change as the program runs. This way you can watch how the code executes step by step. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop
On 2010-10-25 5:48 AM, Andrew wrote:
On Oct 23, 6:57 pm, Daphne wrote: Thanks you Andrew and Mike. This is what I wanted. I'm working my way thro' the code. Because I'm just learning VBA I need to look up all the codes to understand what each one does and how it is used. I will probably have more questions. Cheers Daphne On 2010-10-23 1:18 PM, Mike S wrote: On 10/23/2010 8:54 AM, Andrew wrote: On Oct 22, 5:24 pm, Daphne wrote: I'm sorry. I don't think I was clear enough on what I needed. I know how to open or create another workbook or worksheet. What I don't know how to do is find out what the value of cell "A1" is and then if the value is "T" copy the row to a new workbook or worksheet But if the value of cell "A1" is "F" skip that row and move down a row and continue the process until all the rows with data in them have been processed. On 2010-10-22 2:57 PM, Mike S wrote: On 10/22/2010 12:33 PM, Daphne Eze wrote: Excel 2010 Win 7 I have been trying to teach myself VBA. I am somewhat familiar with programming but I can't figure this out. Can someone point me in the right direction? Thanks Daphne I have a spreadsheet with information about members of a club. I need to copy some of the information to a new workbook depending on the value of cell D in each row. For example if the value of cell A in row 1 is "T" then copy cells B1 to E1 to cell B1 of the first empty row in a new workbook but if the value is "F" then go to the next row and repeat the routine until an empty row is found. A B C D E -------------------------------------------- T N Jim Smith 250-746-8888 F RN Susie Jones 250-749-2288 T RN Vanna White 250-743-1245 Here are some pieces of example code that might help to get you started with the basics, I'm sure other people know a lot more about Excel here but this will probably get you close to what you need. For starters, do you know how to select a file or open a file from a file browse dialog? Do either of these help to select the target workbook where the data will be copied? http://www.exceltip.com/st/Select_fi...n_Microsoft_Ex... Public Function FindFile() As String 'open browse window to select target workbook where data will be copied Dim fn As String fn = Application.GetOpenFilename("Excel-files,*.xls", 1, "Select File To Copy Data To", , False) If fn = "" Then MsgBox "Nothing Chosen" Else 'now that you have the name, you can open it or do something else 'return filename FindFile = fn 'show user filename they selected (just for testing MsgBox "You selected the file """& fn& """", vbOKOnly, "Target Workbook" End If End Function Sub OpenOneSelectedExcelFile() Dim fn As Variant fn = Application.GetOpenFilename("Excel-files,*.xls", 1, "Select One File To Open", , False) If TypeName(fn) = "Boolean" Then ' the user didn't select a file Exit Sub End If Debug.Print "Selected file: "& fn Workbooks.Open fn End Sub '-------------------------------------------------------------------------- http://en.allexperts.com/q/Excel-105...-data-workbook... This code example allow you to select a second worksheet from an opened worksheet that contains this code, and copy data from the first to the selected sheets. Public Function FindFile() As String 'browse to select target workbook where data will be copied 'right now it only shows files with the xls file extension Dim fn As String fn = Application.GetOpenFilename("Excel-files,*.xls", 1, "Select File To Copy Data To", , False) If fn = "" Then exit function Else 'return filename FindFile = fn End If End Function Public Sub CopyDataToTargetBook() 'declare variables Dim sh1 As Worksheet, sh2 As Worksheet, bk2 As Workbook Dim row As Long, col As Long '-------------------------------------------------------- 'refer to sheet in open book data will be copied from Set sh1 = ActiveSheet Application.ScreenUpdating = False '-------------------------------------------------------- 'open sheet data will be copied to Set bk2 = Workbooks.Open(FindFile) 'target sheet where data will be copied Set sh2 = bk2.Sheets(1) 'arbitrary copy code example, not using your conditions For row = 1 To 8 For col = 1 To 3 sh2.Cells(row, col).Value = sh1.Cells(row, col) Next Next 'save the book with the copied data bk2.Close Savechanges:=True Application.ScreenUpdating = True '--------------------------------------------------------- set sh1=nothing set sh2=nothing set bk2=nothing End Sub Does that get you started? Mike Here is how I would do it. I am sort of old school and I like loops. There are many better ways to do this, but it seems easier to understand using fundamental programming constructs. Once you have the concept down, you can make it as fancy as you want. This code copies from one sheet called "data" to another sheet called "file". You can modify this to copy anywhere. ' First find out how many rows are filled with data in your sheet ' Once you run this you will know your last row of data For k = 1 To 10000 If IsEmpty(Cells(k, 1)) = True Then endrow = k - 1 Exit For End If Next Cells(10, 10) = endrow ' this is put a value on the sheet so you can verify that the code has actually found the last row of data ' Now that you have endrow, you can check the values of each cell is column 1 to see if it equals "T" row_data = 0 ' row_data will be used for copying to the proper row on the next sheet For k = 1 To endrow ' this covers all data in the table If Cells(k, 1) = "T" Then ' check to see if cell value = "T" row_data = row_data + 1 'if it does, increment rwo_data Sheets("file").Cells(row_data, 1) = Sheets("data").Cells(k, 2) ' copy over cells. The copied cells will be in row k, the destination row will be row_data Sheets("file").Cells(row_data, 2) = Sheets("data").Cells(k, 3) Sheets("file").Cells(row_data, 3) = Sheets("data").Cells(k, 4) End If Next That looks great! I think you're right, I was approaching it wrong and making it seem harder. Good job. Daphne, one good way to understand this (or any) code is to run it in monitor mode. You can do this by going to the VBA code and then pressing F8. Each press of F8 and the code will increment itself by one line of instruction. After each line is executed, you can hover your mouse over the program variables. Their values will change as the program runs. This way you can watch how the code executes step by step. Great Thank you Andrew. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
returning back to loop check condition without completing the loop | Excel Programming | |||
Loop to Filter, Name Sheets. If Blank, Exit Loop | Excel Programming | |||
Naming Worksheets - Loop within a loop issue | Excel Programming | |||
Naming Worksheets - Loop within a loop issue | Excel Programming | |||
Advancing outer Loop Based on criteria of inner loop | Excel Programming |