| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
find first empty cell in range and copy information from another workbook
I am looking for a formula function or a vba code whe
- In workbook1 find the first cell that is empty between range A7 - A10, - In workbook2, in Range G10- G13: find the word "Day1". - If the word "Day1" exists in cells G10 or G11 or G12 or G13, copy the particular cell or cells where "Day1" exists to the first found empty cell or cells in range A7-A10 in workbook1. Lets suppose cells A8, A9, A10(workbook1) are empty cells, that means A8 is the first empty cell. And G10,G11,G12, G13 (workbook2) have the word "Day1" Then, Copy cell G10 into cell A8 Copy cell G13 into cell A9 Copy cell G14 into cell A10 Please let me know if this explanation is confusing.... and I can try explaining again. Appreciate all help! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
find first empty cell in range and copy information from another workbook
SORRY ABOUT THE ERROR, PLEASE SEE THIS QUESTION:
I am looking for a formula function or a vba code whe - In workbook1 find the first cell that is empty between range A7 - A10, - In workbook2, in Range G10- G13: find the word "Day1". - If the word "Day1" exists in cells G10 or G11 or G12 or G13, copy the particular cell or cells where "Day1" exists to the first found empty cell or cells in range A7-A10 in workbook1. Lets suppose cells A8, A9, A10(workbook1) are empty cells, that means A8 is the first empty cell. And G10,G11,G12, G13 (workbook2) have the word "Day1" Then, Copy cell G10 into cell A8 Copy cell G11 into cell A9 Copy cell G12 into cell A10 Please let me know if this explanation is confusing.... and I can try explaining again. Appreciate all help! On Jan 27, 6:34 pm, wrote: I am looking for a formula function or a vba code whe - In workbook1 find the first cell that is empty between range A7 - A10, - In workbook2, in Range G10- G13: find the word "Day1". - If the word "Day1" exists in cells G10 or G11 or G12 or G13, copy the particular cell or cells where "Day1" exists to the first found empty cell or cells in range A7-A10 in workbook1. Lets suppose cells A8, A9, A10(workbook1) are empty cells, that means A8 is the first empty cell. And G10,G11,G12, G13 (workbook2) have the word "Day1" Then, Copy cell G10 into cell A8 Copy cell G13 into cell A9 Copy cell G14 into cell A10 Please let me know if this explanation is confusing.... and I can try explaining again. Appreciate all help! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
find first empty cell in range and copy information from anoth
Try this:
Sub RwEmptCpy() Set aRng = Workbooks(1).Worksheets(1).Range("$A$7:$A$10") counter = 10 For Each c In aRng If c = "" Then xRng = c.Address Workbooks(2).Sheets(1).Activate Range("$G" & counter).Activate If ActiveCell = "Day1" Then ActiveCell.Copy Workbooks(1).Sheets(1).Range(xRng) End If counter = counter + 1 Workbooks(1).Activate End If Next End Sub It assumes that you are using Sheet 1 in both workbooks. If not you will need to change the sheet references. " wrote: SORRY ABOUT THE ERROR, PLEASE SEE THIS QUESTION: I am looking for a formula function or a vba code whe - In workbook1 find the first cell that is empty between range A7 - A10, - In workbook2, in Range G10- G13: find the word "Day1". - If the word "Day1" exists in cells G10 or G11 or G12 or G13, copy the particular cell or cells where "Day1" exists to the first found empty cell or cells in range A7-A10 in workbook1. Lets suppose cells A8, A9, A10(workbook1) are empty cells, that means A8 is the first empty cell. And G10,G11,G12, G13 (workbook2) have the word "Day1" Then, Copy cell G10 into cell A8 Copy cell G11 into cell A9 Copy cell G12 into cell A10 Please let me know if this explanation is confusing.... and I can try explaining again. Appreciate all help! On Jan 27, 6:34 pm, wrote: I am looking for a formula function or a vba code whe - In workbook1 find the first cell that is empty between range A7 - A10, - In workbook2, in Range G10- G13: find the word "Day1". - If the word "Day1" exists in cells G10 or G11 or G12 or G13, copy the particular cell or cells where "Day1" exists to the first found empty cell or cells in range A7-A10 in workbook1. Lets suppose cells A8, A9, A10(workbook1) are empty cells, that means A8 is the first empty cell. And G10,G11,G12, G13 (workbook2) have the word "Day1" Then, Copy cell G10 into cell A8 Copy cell G13 into cell A9 Copy cell G14 into cell A10 Please let me know if this explanation is confusing.... and I can try explaining again. Appreciate all help! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
find first empty cell in range and copy information from anoth
Hi JLGWhiz,
for line: Range("$G" & counter).Activate It gives an error saying " Runtime 1004 : Activate method of Range class failed" Thank you for your help again. On Jan 27, 7:42 pm, JLGWhiz wrote: Try this: Sub RwEmptCpy() Set aRng = Workbooks(1).Worksheets(1).Range("$A$7:$A$10") counter = 10 For Each c In aRng If c = "" Then xRng = c.Address Workbooks(2).Sheets(1).Activate Range("$G" & counter).Activate If ActiveCell = "Day1" Then ActiveCell.Copy Workbooks(1).Sheets(1).Range(xRng) End If counter = counter + 1 Workbooks(1).Activate End If Next End Sub It assumes that you are using Sheet 1 in both workbooks. If not you will need to change the sheet references. " wrote: SORRY ABOUT THE ERROR, PLEASE SEE THIS QUESTION: I am looking for a formula function or a vba code whe - In workbook1 find the first cell that is empty between range A7 - A10, - In workbook2, in Range G10- G13: find the word "Day1". - If the word "Day1" exists in cells G10 or G11 or G12 or G13, copy the particular cell or cells where "Day1" exists to the first found empty cell or cells in range A7-A10 in workbook1. Lets suppose cells A8, A9, A10(workbook1) are empty cells, that means A8 is the first empty cell. And G10,G11,G12, G13 (workbook2) have the word "Day1" Then, Copy cell G10 into cell A8 Copy cell G11 into cell A9 Copy cell G12 into cell A10 Please let me know if this explanation is confusing.... and I can try explaining again. Appreciate all help! On Jan 27, 6:34 pm, wrote: I am looking for a formula function or a vba code whe - In workbook1 find the first cell that is empty between range A7 - A10, - In workbook2, in Range G10- G13: find the word "Day1". - If the word "Day1" exists in cells G10 or G11 or G12 or G13, copy the particular cell or cells where "Day1" exists to the first found empty cell or cells in range A7-A10 in workbook1. Lets suppose cells A8, A9, A10(workbook1) are empty cells, that means A8 is the first empty cell. And G10,G11,G12, G13 (workbook2) have the word "Day1" Then, Copy cell G10 into cell A8 Copy cell G13 into cell A9 Copy cell G14 into cell A10 Please let me know if this explanation is confusing.... and I can try explaining again. Appreciate all help!- Hide quoted text -- Show quoted text - |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
find first empty cell in range and copy information from anoth
You need to have both workbooks open for the code to work. Also, you should
use the Workbook names instead of (1) and (2) since Workbooks(1) is the first wb opened. I ran it two or three different ways and don't get the error. Step through to make sure the counter reference is correct. It should start at 10 and go no further than 13 base of the four iterations of the range in wb 1. " wrote: Hi JLGWhiz, for line: Range("$G" & counter).Activate It gives an error saying " Runtime 1004 : Activate method of Range class failed" Thank you for your help again. On Jan 27, 7:42 pm, JLGWhiz wrote: Try this: Sub RwEmptCpy() Set aRng = Workbooks(1).Worksheets(1).Range("$A$7:$A$10") counter = 10 For Each c In aRng If c = "" Then xRng = c.Address Workbooks(2).Sheets(1).Activate Range("$G" & counter).Activate If ActiveCell = "Day1" Then ActiveCell.Copy Workbooks(1).Sheets(1).Range(xRng) End If counter = counter + 1 Workbooks(1).Activate End If Next End Sub It assumes that you are using Sheet 1 in both workbooks. If not you will need to change the sheet references. " wrote: SORRY ABOUT THE ERROR, PLEASE SEE THIS QUESTION: I am looking for a formula function or a vba code whe - In workbook1 find the first cell that is empty between range A7 - A10, - In workbook2, in Range G10- G13: find the word "Day1". - If the word "Day1" exists in cells G10 or G11 or G12 or G13, copy the particular cell or cells where "Day1" exists to the first found empty cell or cells in range A7-A10 in workbook1. Lets suppose cells A8, A9, A10(workbook1) are empty cells, that means A8 is the first empty cell. And G10,G11,G12, G13 (workbook2) have the word "Day1" Then, Copy cell G10 into cell A8 Copy cell G11 into cell A9 Copy cell G12 into cell A10 Please let me know if this explanation is confusing.... and I can try explaining again. Appreciate all help! On Jan 27, 6:34 pm, wrote: I am looking for a formula function or a vba code whe - In workbook1 find the first cell that is empty between range A7 - A10, - In workbook2, in Range G10- G13: find the word "Day1". - If the word "Day1" exists in cells G10 or G11 or G12 or G13, copy the particular cell or cells where "Day1" exists to the first found empty cell or cells in range A7-A10 in workbook1. Lets suppose cells A8, A9, A10(workbook1) are empty cells, that means A8 is the first empty cell. And G10,G11,G12, G13 (workbook2) have the word "Day1" Then, Copy cell G10 into cell A8 Copy cell G13 into cell A9 Copy cell G14 into cell A10 Please let me know if this explanation is confusing.... and I can try explaining again. Appreciate all help!- Hide quoted text -- Show quoted text - |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
find first empty cell in range and copy information from anoth
Not sure what you tested, but if Workbooks(1) is active, then this line
Workbooks(2).Sheets(1).Activate will certainly raise an error. And in your code, you do Workbooks(1).Activate I guess you tested with only one cell in A7:A10 being blank. -- Regards, Tom Ogilvy "JLGWhiz" wrote in message ... You need to have both workbooks open for the code to work. Also, you should use the Workbook names instead of (1) and (2) since Workbooks(1) is the first wb opened. I ran it two or three different ways and don't get the error. Step through to make sure the counter reference is correct. It should start at 10 and go no further than 13 base of the four iterations of the range in wb 1. " wrote: Hi JLGWhiz, for line: Range("$G" & counter).Activate It gives an error saying " Runtime 1004 : Activate method of Range class failed" Thank you for your help again. On Jan 27, 7:42 pm, JLGWhiz wrote: Try this: Sub RwEmptCpy() Set aRng = Workbooks(1).Worksheets(1).Range("$A$7:$A$10") counter = 10 For Each c In aRng If c = "" Then xRng = c.Address Workbooks(2).Sheets(1).Activate Range("$G" & counter).Activate If ActiveCell = "Day1" Then ActiveCell.Copy Workbooks(1).Sheets(1).Range(xRng) End If counter = counter + 1 Workbooks(1).Activate End If Next End Sub It assumes that you are using Sheet 1 in both workbooks. If not you will need to change the sheet references. " wrote: SORRY ABOUT THE ERROR, PLEASE SEE THIS QUESTION: I am looking for a formula function or a vba code whe - In workbook1 find the first cell that is empty between range A7 - A10, - In workbook2, in Range G10- G13: find the word "Day1". - If the word "Day1" exists in cells G10 or G11 or G12 or G13, copy the particular cell or cells where "Day1" exists to the first found empty cell or cells in range A7-A10 in workbook1. Lets suppose cells A8, A9, A10(workbook1) are empty cells, that means A8 is the first empty cell. And G10,G11,G12, G13 (workbook2) have the word "Day1" Then, Copy cell G10 into cell A8 Copy cell G11 into cell A9 Copy cell G12 into cell A10 Please let me know if this explanation is confusing.... and I can try explaining again. Appreciate all help! On Jan 27, 6:34 pm, wrote: I am looking for a formula function or a vba code whe - In workbook1 find the first cell that is empty between range A7 - A10, - In workbook2, in Range G10- G13: find the word "Day1". - If the word "Day1" exists in cells G10 or G11 or G12 or G13, copy the particular cell or cells where "Day1" exists to the first found empty cell or cells in range A7-A10 in workbook1. Lets suppose cells A8, A9, A10(workbook1) are empty cells, that means A8 is the first empty cell. And G10,G11,G12, G13 (workbook2) have the word "Day1" Then, Copy cell G10 into cell A8 Copy cell G13 into cell A9 Copy cell G14 into cell A10 Please let me know if this explanation is confusing.... and I can try explaining again. Appreciate all help!- Hide quoted text -- Show quoted text - |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
find first empty cell in range and copy information from anoth
This eliminates the activation steps
between the workbooks. See if it runs without throwing an error. Once again, you will need to change the Workbooks(1) and (2) and sheet number references to meet your workbook name requirements. Both workbooks must be open and the first one opened will be Workbooks(1) since the sytem assigns the index as the files are opened. Sub EmpRwCpy() Set aRng = Workbooks(1).Worksheets(1) _ ..Range("$A$7:$A$10") counter = 10 For Each c In aRng If c = "" Then xRng = c.Address If Workbooks(2).Sheets(1) _ ..Range("$G" & counter) = "Day1" Then Workbooks(2).Sheets(1) _ ..Range("$G" & counter).Copy Workbooks(1).Sheets(1).Range(xRng) End If counter = counter + 1 End If Next End Sub " wrote: Hi JLGWhiz, for line: Range("$G" & counter).Activate It gives an error saying " Runtime 1004 : Activate method of Range class failed" Thank you for your help again. On Jan 27, 7:42 pm, JLGWhiz wrote: Try this: Sub RwEmptCpy() Set aRng = Workbooks(1).Worksheets(1).Range("$A$7:$A$10") counter = 10 For Each c In aRng If c = "" Then xRng = c.Address Workbooks(2).Sheets(1).Activate Range("$G" & counter).Activate If ActiveCell = "Day1" Then ActiveCell.Copy Workbooks(1).Sheets(1).Range(xRng) End If counter = counter + 1 Workbooks(1).Activate End If Next End Sub It assumes that you are using Sheet 1 in both workbooks. If not you will need to change the sheet references. " wrote: SORRY ABOUT THE ERROR, PLEASE SEE THIS QUESTION: I am looking for a formula function or a vba code whe - In workbook1 find the first cell that is empty between range A7 - A10, - In workbook2, in Range G10- G13: find the word "Day1". - If the word "Day1" exists in cells G10 or G11 or G12 or G13, copy the particular cell or cells where "Day1" exists to the first found empty cell or cells in range A7-A10 in workbook1. Lets suppose cells A8, A9, A10(workbook1) are empty cells, that means A8 is the first empty cell. And G10,G11,G12, G13 (workbook2) have the word "Day1" Then, Copy cell G10 into cell A8 Copy cell G11 into cell A9 Copy cell G12 into cell A10 Please let me know if this explanation is confusing.... and I can try explaining again. Appreciate all help! On Jan 27, 6:34 pm, wrote: I am looking for a formula function or a vba code whe - In workbook1 find the first cell that is empty between range A7 - A10, - In workbook2, in Range G10- G13: find the word "Day1". - If the word "Day1" exists in cells G10 or G11 or G12 or G13, copy the particular cell or cells where "Day1" exists to the first found empty cell or cells in range A7-A10 in workbook1. Lets suppose cells A8, A9, A10(workbook1) are empty cells, that means A8 is the first empty cell. And G10,G11,G12, G13 (workbook2) have the word "Day1" Then, Copy cell G10 into cell A8 Copy cell G13 into cell A9 Copy cell G14 into cell A10 Please let me know if this explanation is confusing.... and I can try explaining again. Appreciate all help!- Hide quoted text -- Show quoted text - |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
find first empty cell in range and copy information from anoth
Hi JLG, The code is running this time without errors. But its not exactly the same thing it is suppossed to. It doesnt copy the information to the topmost empty table. Nevertheless the thing works without any errors... eg. if this is how the data is on wb2: G10 Day1 G11 Day2 G12 Day2 G13 Day1 it gets copied on wb1 as: A7 Day1 A8 (empty) A9 (empty) A10 Day1 On Jan 28, 9:07 am, JLGWhiz wrote: This eliminates the activation steps between the workbooks. See if it runs without throwing an error. Once again, you will need to change the Workbooks(1) and (2) and sheet number references to meet your workbook name requirements. Both workbooks must be open and the first one opened will be Workbooks(1) since the sytem assigns the index as the files are opened. Sub EmpRwCpy() Set aRng = Workbooks(1).Worksheets(1) _ .Range("$A$7:$A$10") counter = 10 For Each c In aRng If c = "" Then xRng = c.Address If Workbooks(2).Sheets(1) _ .Range("$G" & counter) = "Day1" Then Workbooks(2).Sheets(1) _ .Range("$G" & counter).Copy Workbooks(1).Sheets(1).Range(xRng) End If counter = counter + 1 End If Next End Sub " wrote: Hi JLGWhiz, for line: Range("$G" & counter).Activate It gives an error saying " Runtime 1004 : Activate method of Range class failed" Thank you for your help again. On Jan 27, 7:42 pm, JLGWhiz wrote: Try this: Sub RwEmptCpy() Set aRng = Workbooks(1).Worksheets(1).Range("$A$7:$A$10") counter = 10 For Each c In aRng If c = "" Then xRng = c.Address Workbooks(2).Sheets(1).Activate Range("$G" & counter).Activate If ActiveCell = "Day1" Then ActiveCell.Copy Workbooks(1).Sheets(1).Range(xRng) End If counter = counter + 1 Workbooks(1).Activate End If Next End Sub It assumes that you are using Sheet 1 in both workbooks. If not you will need to change the sheet references. " wrote: SORRY ABOUT THE ERROR, PLEASE SEE THIS QUESTION: I am looking for a formula function or a vba code whe - In workbook1 find the first cell that is empty between range A7 - A10, - In workbook2, in Range G10- G13: find the word "Day1". - If the word "Day1" exists in cells G10 or G11 or G12 or G13, copy the particular cell or cells where "Day1" exists to the first found empty cell or cells in range A7-A10 in workbook1. Lets suppose cells A8, A9, A10(workbook1) are empty cells, that means A8 is the first empty cell. And G10,G11,G12, G13 (workbook2) have the word "Day1" Then, Copy cell G10 into cell A8 Copy cell G11 into cell A9 Copy cell G12 into cell A10 Please let me know if this explanation is confusing.... and I can try explaining again. Appreciate all help! On Jan 27, 6:34 pm, wrote: I am looking for a formula function or a vba code whe - In workbook1 find the first cell that is empty between range A7 - A10, - In workbook2, in Range G10- G13: find the word "Day1". - If the word "Day1" exists in cells G10 or G11 or G12 or G13, copy the particular cell or cells where "Day1" exists to the first found empty cell or cells in range A7-A10 in workbook1. Lets suppose cells A8, A9, A10(workbook1) are empty cells, that means A8 is the first empty cell. And G10,G11,G12, G13 (workbook2) have the word "Day1" Then, Copy cell G10 into cell A8 Copy cell G13 into cell A9 Copy cell G14 into cell A10 Please let me know if this explanation is confusing.... and I can try explaining again. Appreciate all help!- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do i find last not empty cell in a range | Excel Worksheet Functions | |||
| Copy Values to next empty cell range | Excel Worksheet Functions | |||
| Copy to next empty cell in another workbook | Excel Programming | |||
| Macro to find empty cell and select range to print selected. | Excel Programming | |||
| find changes in a cell range, copy changes to another workbook | Excel Programming | |||
Hybrid Mode
