Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Macro-Copy various cells in sheet1 into next available row in sheet 2
I have a sheet where I enter text and values into various cells. After all
cells are entered, I want to copy certain cells into 1 row in another sheet in the same book. Every time I enter information in the cells in the first sheeet, I want to put those values in the next available row in the second sheet. ie: Sheet1! A1,B15,B16,B17,G7,B22 (always the same cell numbers) Copy into Sheet2! A1,B1,C1,D1,E1,F1 the first time, then the new values into cells A2,B2,C2,D2,E2,F2 And so on. Does anyone have a macro or know where to look to read up on how to do it. Any info would be great. Thanks, Joe |
#2
|
|||
|
|||
Saved from a previous post--you'll have to change your addresses:
Option Explicit Sub testme01() Dim historyWks As Worksheet Dim inputWks As Worksheet Dim nextRow As Long Dim oCol As Long Dim myRng As Range Dim myAddresses As String Dim myCell As Range myAddresses = "A1,F9,A2,B1" Set inputWks = Worksheets("Input") Set historyWks = Worksheets("Summary") With historyWks nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row End With With inputWks Set myRng = .Range(myAddresses) If Application.CountA(myRng) < myRng.Cells.Count Then MsgBox "Please fill in all the cells!" Exit Sub End If End With With historyWks With .Cells(nextRow, "A") .Value = Now .NumberFormat = "mm/dd/yyyy hh:mm:ss" End With .Cells(nextRow, "B").Value = Application.UserName oCol = 3 For Each myCell In myRng.Cells historyWks.Cells(nextRow, oCol).Value = myCell.Value myCell.ClearContents 'clean it up??? oCol = oCol + 1 Next myCell End With End Sub I check to see if all the cells have something in them (maybe not required???). I also add the date/time to column A of the summary sheet and the username (taken from Tools|options|General tab) to column B. Then Column C to xxxx go in the same order as the addresses you've specified in this line: myAddresses = "A1,F9,A2,B1" (Change that to match your input worksheet. (mycell.clearcontents may not be necessary, too.) lunker55 wrote: I have a sheet where I enter text and values into various cells. After all cells are entered, I want to copy certain cells into 1 row in another sheet in the same book. Every time I enter information in the cells in the first sheeet, I want to put those values in the next available row in the second sheet. ie: Sheet1! A1,B15,B16,B17,G7,B22 (always the same cell numbers) Copy into Sheet2! A1,B1,C1,D1,E1,F1 the first time, then the new values into cells A2,B2,C2,D2,E2,F2 And so on. Does anyone have a macro or know where to look to read up on how to do it. Any info would be great. Thanks, Joe -- Dave Peterson |
#3
|
|||
|
|||
Thank you very much Dave.
It is exactly what I was loooking for. Just a few minor changes. Thanks again, Joe "Dave Peterson" wrote in message ... Saved from a previous post--you'll have to change your addresses: Option Explicit Sub testme01() Dim historyWks As Worksheet Dim inputWks As Worksheet Dim nextRow As Long Dim oCol As Long Dim myRng As Range Dim myAddresses As String Dim myCell As Range myAddresses = "A1,F9,A2,B1" Set inputWks = Worksheets("Input") Set historyWks = Worksheets("Summary") With historyWks nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row End With With inputWks Set myRng = .Range(myAddresses) If Application.CountA(myRng) < myRng.Cells.Count Then MsgBox "Please fill in all the cells!" Exit Sub End If End With With historyWks With .Cells(nextRow, "A") .Value = Now .NumberFormat = "mm/dd/yyyy hh:mm:ss" End With .Cells(nextRow, "B").Value = Application.UserName oCol = 3 For Each myCell In myRng.Cells historyWks.Cells(nextRow, oCol).Value = myCell.Value myCell.ClearContents 'clean it up??? oCol = oCol + 1 Next myCell End With End Sub I check to see if all the cells have something in them (maybe not required???). I also add the date/time to column A of the summary sheet and the username (taken from Tools|options|General tab) to column B. Then Column C to xxxx go in the same order as the addresses you've specified in this line: myAddresses = "A1,F9,A2,B1" (Change that to match your input worksheet. (mycell.clearcontents may not be necessary, too.) lunker55 wrote: I have a sheet where I enter text and values into various cells. After all cells are entered, I want to copy certain cells into 1 row in another sheet in the same book. Every time I enter information in the cells in the first sheeet, I want to put those values in the next available row in the second sheet. ie: Sheet1! A1,B15,B16,B17,G7,B22 (always the same cell numbers) Copy into Sheet2! A1,B1,C1,D1,E1,F1 the first time, then the new values into cells A2,B2,C2,D2,E2,F2 And so on. Does anyone have a macro or know where to look to read up on how to do it. Any info would be great. Thanks, Joe -- Dave Peterson |
#4
|
|||
|
|||
Glad it worked.
lunker55 wrote: Thank you very much Dave. It is exactly what I was loooking for. Just a few minor changes. Thanks again, Joe "Dave Peterson" wrote in message ... Saved from a previous post--you'll have to change your addresses: Option Explicit Sub testme01() Dim historyWks As Worksheet Dim inputWks As Worksheet Dim nextRow As Long Dim oCol As Long Dim myRng As Range Dim myAddresses As String Dim myCell As Range myAddresses = "A1,F9,A2,B1" Set inputWks = Worksheets("Input") Set historyWks = Worksheets("Summary") With historyWks nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row End With With inputWks Set myRng = .Range(myAddresses) If Application.CountA(myRng) < myRng.Cells.Count Then MsgBox "Please fill in all the cells!" Exit Sub End If End With With historyWks With .Cells(nextRow, "A") .Value = Now .NumberFormat = "mm/dd/yyyy hh:mm:ss" End With .Cells(nextRow, "B").Value = Application.UserName oCol = 3 For Each myCell In myRng.Cells historyWks.Cells(nextRow, oCol).Value = myCell.Value myCell.ClearContents 'clean it up??? oCol = oCol + 1 Next myCell End With End Sub I check to see if all the cells have something in them (maybe not required???). I also add the date/time to column A of the summary sheet and the username (taken from Tools|options|General tab) to column B. Then Column C to xxxx go in the same order as the addresses you've specified in this line: myAddresses = "A1,F9,A2,B1" (Change that to match your input worksheet. (mycell.clearcontents may not be necessary, too.) lunker55 wrote: I have a sheet where I enter text and values into various cells. After all cells are entered, I want to copy certain cells into 1 row in another sheet in the same book. Every time I enter information in the cells in the first sheeet, I want to put those values in the next available row in the second sheet. ie: Sheet1! A1,B15,B16,B17,G7,B22 (always the same cell numbers) Copy into Sheet2! A1,B1,C1,D1,E1,F1 the first time, then the new values into cells A2,B2,C2,D2,E2,F2 And so on. Does anyone have a macro or know where to look to read up on how to do it. Any info would be great. Thanks, Joe -- Dave Peterson -- Dave Peterson |
#5
|
|||
|
|||
Macro-Copy various cells in sheet1 into next available row in sheet 2
Dave,
I have another question. When I am copying the data into the destination sheet, I have a formula in the "F" column, so I don't need data put into it. So I need to skip a column because it erases the formula when I copy a blank cell into it. In the " myAddresses " string, the blank cell is after A19. Do I need 2 macros with the second one starting in columg "G"? Your revised macro is below. Thanks, Joe Sub testme01() Dim historyWks As Worksheet Dim inputWks As Worksheet Dim nextRow As Long Dim oCol As Long Dim myRng As Range Dim myAddresses As String Dim myCell As Range myAddresses = "c7,a11,c5,b36,a19,d1,d2,b19,b20,b21,a24,b24,b25,b 26,a29,b29,b30,b31" Set inputWks = Worksheets("Purchase Order with Sales Tax") Set historyWks = Worksheets("Inventory List") With historyWks nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row End With With inputWks Set myRng = .Range(myAddresses) End With With historyWks With .Cells(nextRow, "A") End With oCol = 1 For Each myCell In myRng.Cells historyWks.Cells(nextRow, oCol).Value = myCell.Value oCol = oCol + 1 Next myCell End With End Sub "lunker55" wrote in message ... Thank you very much Dave. It is exactly what I was loooking for. Just a few minor changes. Thanks again, Joe "Dave Peterson" wrote in message ... Saved from a previous post--you'll have to change your addresses: Option Explicit Sub testme01() Dim historyWks As Worksheet Dim inputWks As Worksheet Dim nextRow As Long Dim oCol As Long Dim myRng As Range Dim myAddresses As String Dim myCell As Range myAddresses = "A1,F9,A2,B1" Set inputWks = Worksheets("Input") Set historyWks = Worksheets("Summary") With historyWks nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row End With With inputWks Set myRng = .Range(myAddresses) If Application.CountA(myRng) < myRng.Cells.Count Then MsgBox "Please fill in all the cells!" Exit Sub End If End With With historyWks With .Cells(nextRow, "A") .Value = Now .NumberFormat = "mm/dd/yyyy hh:mm:ss" End With .Cells(nextRow, "B").Value = Application.UserName oCol = 3 For Each myCell In myRng.Cells historyWks.Cells(nextRow, oCol).Value = myCell.Value myCell.ClearContents 'clean it up??? oCol = oCol + 1 Next myCell End With End Sub I check to see if all the cells have something in them (maybe not required???). I also add the date/time to column A of the summary sheet and the username (taken from Tools|options|General tab) to column B. Then Column C to xxxx go in the same order as the addresses you've specified in this line: myAddresses = "A1,F9,A2,B1" (Change that to match your input worksheet. (mycell.clearcontents may not be necessary, too.) lunker55 wrote: I have a sheet where I enter text and values into various cells. After all cells are entered, I want to copy certain cells into 1 row in another sheet in the same book. Every time I enter information in the cells in the first sheeet, I want to put those values in the next available row in the second sheet. ie: Sheet1! A1,B15,B16,B17,G7,B22 (always the same cell numbers) Copy into Sheet2! A1,B1,C1,D1,E1,F1 the first time, then the new values into cells A2,B2,C2,D2,E2,F2 And so on. Does anyone have a macro or know where to look to read up on how to do it. Any info would be great. Thanks, Joe -- Dave Peterson |
#6
|
|||
|
|||
Macro-Copy various cells in sheet1 into next available row in sheet2
Instead of fixing the cells that are being copied, you could just fix where it
gets pasted (well, if I understand correctly). The portion that writes to that history sheet. With historyWks oCol = 1 For Each myCell In myRng.Cells historyWks.Cells(nextRow, oCol).Value = myCell.Value 'get ready for next time oCol = oCol + 1 'if the next column is 6 (column F), then just bounce to column G if oCol = 6 then ocol = ocol + 1 end if Next myCell End With lunker55 wrote: Dave, I have another question. When I am copying the data into the destination sheet, I have a formula in the "F" column, so I don't need data put into it. So I need to skip a column because it erases the formula when I copy a blank cell into it. In the " myAddresses " string, the blank cell is after A19. Do I need 2 macros with the second one starting in columg "G"? Your revised macro is below. Thanks, Joe Sub testme01() Dim historyWks As Worksheet Dim inputWks As Worksheet Dim nextRow As Long Dim oCol As Long Dim myRng As Range Dim myAddresses As String Dim myCell As Range myAddresses = "c7,a11,c5,b36,a19,d1,d2,b19,b20,b21,a24,b24,b25,b 26,a29,b29,b30,b31" Set inputWks = Worksheets("Purchase Order with Sales Tax") Set historyWks = Worksheets("Inventory List") With historyWks nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row End With With inputWks Set myRng = .Range(myAddresses) End With With historyWks With .Cells(nextRow, "A") End With oCol = 1 For Each myCell In myRng.Cells historyWks.Cells(nextRow, oCol).Value = myCell.Value oCol = oCol + 1 Next myCell End With End Sub "lunker55" wrote in message ... Thank you very much Dave. It is exactly what I was loooking for. Just a few minor changes. Thanks again, Joe "Dave Peterson" wrote in message ... Saved from a previous post--you'll have to change your addresses: Option Explicit Sub testme01() Dim historyWks As Worksheet Dim inputWks As Worksheet Dim nextRow As Long Dim oCol As Long Dim myRng As Range Dim myAddresses As String Dim myCell As Range myAddresses = "A1,F9,A2,B1" Set inputWks = Worksheets("Input") Set historyWks = Worksheets("Summary") With historyWks nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row End With With inputWks Set myRng = .Range(myAddresses) If Application.CountA(myRng) < myRng.Cells.Count Then MsgBox "Please fill in all the cells!" Exit Sub End If End With With historyWks With .Cells(nextRow, "A") .Value = Now .NumberFormat = "mm/dd/yyyy hh:mm:ss" End With .Cells(nextRow, "B").Value = Application.UserName oCol = 3 For Each myCell In myRng.Cells historyWks.Cells(nextRow, oCol).Value = myCell.Value myCell.ClearContents 'clean it up??? oCol = oCol + 1 Next myCell End With End Sub I check to see if all the cells have something in them (maybe not required???). I also add the date/time to column A of the summary sheet and the username (taken from Tools|options|General tab) to column B. Then Column C to xxxx go in the same order as the addresses you've specified in this line: myAddresses = "A1,F9,A2,B1" (Change that to match your input worksheet. (mycell.clearcontents may not be necessary, too.) lunker55 wrote: I have a sheet where I enter text and values into various cells. After all cells are entered, I want to copy certain cells into 1 row in another sheet in the same book. Every time I enter information in the cells in the first sheeet, I want to put those values in the next available row in the second sheet. ie: Sheet1! A1,B15,B16,B17,G7,B22 (always the same cell numbers) Copy into Sheet2! A1,B1,C1,D1,E1,F1 the first time, then the new values into cells A2,B2,C2,D2,E2,F2 And so on. Does anyone have a macro or know where to look to read up on how to do it. Any info would be great. Thanks, Joe -- Dave Peterson -- Dave Peterson |
#7
|
|||
|
|||
Macro-Copy various cells in sheet1 into next available row in sheet 2
Thanks Dave- Works excellent!
I'll have to get a book on macros. Joe "Dave Peterson" wrote in message ... Instead of fixing the cells that are being copied, you could just fix where it gets pasted (well, if I understand correctly). The portion that writes to that history sheet. With historyWks oCol = 1 For Each myCell In myRng.Cells historyWks.Cells(nextRow, oCol).Value = myCell.Value 'get ready for next time oCol = oCol + 1 'if the next column is 6 (column F), then just bounce to column G if oCol = 6 then ocol = ocol + 1 end if Next myCell End With lunker55 wrote: Dave, I have another question. When I am copying the data into the destination sheet, I have a formula in the "F" column, so I don't need data put into it. So I need to skip a column because it erases the formula when I copy a blank cell into it. In the " myAddresses " string, the blank cell is after A19. Do I need 2 macros with the second one starting in columg "G"? Your revised macro is below. Thanks, Joe Sub testme01() Dim historyWks As Worksheet Dim inputWks As Worksheet Dim nextRow As Long Dim oCol As Long Dim myRng As Range Dim myAddresses As String Dim myCell As Range myAddresses = "c7,a11,c5,b36,a19,d1,d2,b19,b20,b21,a24,b24,b25,b 26,a29,b29,b30,b31" Set inputWks = Worksheets("Purchase Order with Sales Tax") Set historyWks = Worksheets("Inventory List") With historyWks nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row End With With inputWks Set myRng = .Range(myAddresses) End With With historyWks With .Cells(nextRow, "A") End With oCol = 1 For Each myCell In myRng.Cells historyWks.Cells(nextRow, oCol).Value = myCell.Value oCol = oCol + 1 Next myCell End With End Sub "lunker55" wrote in message ... Thank you very much Dave. It is exactly what I was loooking for. Just a few minor changes. Thanks again, Joe "Dave Peterson" wrote in message ... Saved from a previous post--you'll have to change your addresses: Option Explicit Sub testme01() Dim historyWks As Worksheet Dim inputWks As Worksheet Dim nextRow As Long Dim oCol As Long Dim myRng As Range Dim myAddresses As String Dim myCell As Range myAddresses = "A1,F9,A2,B1" Set inputWks = Worksheets("Input") Set historyWks = Worksheets("Summary") With historyWks nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row End With With inputWks Set myRng = .Range(myAddresses) If Application.CountA(myRng) < myRng.Cells.Count Then MsgBox "Please fill in all the cells!" Exit Sub End If End With With historyWks With .Cells(nextRow, "A") .Value = Now .NumberFormat = "mm/dd/yyyy hh:mm:ss" End With .Cells(nextRow, "B").Value = Application.UserName oCol = 3 For Each myCell In myRng.Cells historyWks.Cells(nextRow, oCol).Value = myCell.Value myCell.ClearContents 'clean it up??? oCol = oCol + 1 Next myCell End With End Sub I check to see if all the cells have something in them (maybe not required???). I also add the date/time to column A of the summary sheet and the username (taken from Tools|options|General tab) to column B. Then Column C to xxxx go in the same order as the addresses you've specified in this line: myAddresses = "A1,F9,A2,B1" (Change that to match your input worksheet. (mycell.clearcontents may not be necessary, too.) lunker55 wrote: I have a sheet where I enter text and values into various cells. After all cells are entered, I want to copy certain cells into 1 row in another sheet in the same book. Every time I enter information in the cells in the first sheeet, I want to put those values in the next available row in the second sheet. ie: Sheet1! A1,B15,B16,B17,G7,B22 (always the same cell numbers) Copy into Sheet2! A1,B1,C1,D1,E1,F1 the first time, then the new values into cells A2,B2,C2,D2,E2,F2 And so on. Does anyone have a macro or know where to look to read up on how to do it. Any info would be great. Thanks, Joe -- Dave Peterson -- Dave Peterson |
#8
|
|||
|
|||
Macro-Copy various cells in sheet1 into next available row insheet 2
Debra Dalgleish has a list of books at her site:
http://www.contextures.com/xlbooks.html John Walkenbach's is a nice one to start with. See if you can find them in your local bookstore and you can choose what one you like best. lunker55 wrote: Thanks Dave- Works excellent! I'll have to get a book on macros. Joe "Dave Peterson" wrote in message ... Instead of fixing the cells that are being copied, you could just fix where it gets pasted (well, if I understand correctly). The portion that writes to that history sheet. With historyWks oCol = 1 For Each myCell In myRng.Cells historyWks.Cells(nextRow, oCol).Value = myCell.Value 'get ready for next time oCol = oCol + 1 'if the next column is 6 (column F), then just bounce to column G if oCol = 6 then ocol = ocol + 1 end if Next myCell End With lunker55 wrote: Dave, I have another question. When I am copying the data into the destination sheet, I have a formula in the "F" column, so I don't need data put into it. So I need to skip a column because it erases the formula when I copy a blank cell into it. In the " myAddresses " string, the blank cell is after A19. Do I need 2 macros with the second one starting in columg "G"? Your revised macro is below. Thanks, Joe Sub testme01() Dim historyWks As Worksheet Dim inputWks As Worksheet Dim nextRow As Long Dim oCol As Long Dim myRng As Range Dim myAddresses As String Dim myCell As Range myAddresses = "c7,a11,c5,b36,a19,d1,d2,b19,b20,b21,a24,b24,b25,b 26,a29,b29,b30,b31" Set inputWks = Worksheets("Purchase Order with Sales Tax") Set historyWks = Worksheets("Inventory List") With historyWks nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row End With With inputWks Set myRng = .Range(myAddresses) End With With historyWks With .Cells(nextRow, "A") End With oCol = 1 For Each myCell In myRng.Cells historyWks.Cells(nextRow, oCol).Value = myCell.Value oCol = oCol + 1 Next myCell End With End Sub "lunker55" wrote in message ... Thank you very much Dave. It is exactly what I was loooking for. Just a few minor changes. Thanks again, Joe "Dave Peterson" wrote in message ... Saved from a previous post--you'll have to change your addresses: Option Explicit Sub testme01() Dim historyWks As Worksheet Dim inputWks As Worksheet Dim nextRow As Long Dim oCol As Long Dim myRng As Range Dim myAddresses As String Dim myCell As Range myAddresses = "A1,F9,A2,B1" Set inputWks = Worksheets("Input") Set historyWks = Worksheets("Summary") With historyWks nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row End With With inputWks Set myRng = .Range(myAddresses) If Application.CountA(myRng) < myRng.Cells.Count Then MsgBox "Please fill in all the cells!" Exit Sub End If End With With historyWks With .Cells(nextRow, "A") .Value = Now .NumberFormat = "mm/dd/yyyy hh:mm:ss" End With .Cells(nextRow, "B").Value = Application.UserName oCol = 3 For Each myCell In myRng.Cells historyWks.Cells(nextRow, oCol).Value = myCell.Value myCell.ClearContents 'clean it up??? oCol = oCol + 1 Next myCell End With End Sub I check to see if all the cells have something in them (maybe not required???). I also add the date/time to column A of the summary sheet and the username (taken from Tools|options|General tab) to column B. Then Column C to xxxx go in the same order as the addresses you've specified in this line: myAddresses = "A1,F9,A2,B1" (Change that to match your input worksheet. (mycell.clearcontents may not be necessary, too.) lunker55 wrote: I have a sheet where I enter text and values into various cells. After all cells are entered, I want to copy certain cells into 1 row in another sheet in the same book. Every time I enter information in the cells in the first sheeet, I want to put those values in the next available row in the second sheet. ie: Sheet1! A1,B15,B16,B17,G7,B22 (always the same cell numbers) Copy into Sheet2! A1,B1,C1,D1,E1,F1 the first time, then the new values into cells A2,B2,C2,D2,E2,F2 And so on. Does anyone have a macro or know where to look to read up on how to do it. Any info would be great. Thanks, Joe -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a macros to copy and paste cells | Excel Discussion (Misc queries) | |||
macro to copy and edit then delete a worksheet | Excel Discussion (Misc queries) | |||
Macro to copy value in empty cells | Excel Discussion (Misc queries) | |||
Copy down - special to fill only the blank cells | Excel Discussion (Misc queries) | |||
macro to copy columns to sheet | Excel Discussion (Misc queries) |