Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
need a macro to copy the data to the following cells
Please help with a macros to copy the data to acitve cell from the cell just
before the activce cell ( Only if the active cell is not null ). for example type name 1 XX 2 xx1 3 xx2 4 YY 5 yy1 6 yy2 In the above example I want to copy XX from A1 cell to range a2..a3, YY from A4 to copy to a4...a6. So I need to write the macro to copy conditionally if the active cell is not null . The out put should come type name 1 XX 2 XX xx1 3 XX xx2 4 YY 5 YY yy1 6 YY yy2 Please help With thanks Pol |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
need a macro to copy the data to the following cells
To do this you dont need a macro...
--Select a unused cell say E2 --Enter the formula in cell E2 which references the top cell (=E1) --Copy the cell --Keeping the copy select the data range in ColA (say A1:A100). --Press F5. From Goto window select blanks --This will select all blanks.. Now Right clickPasteSpecialFormulas. will fill in with the data you need.. --Once done you can select col A Copy PasteSpecialValues to turn formulas to actual values PS: Post back if you still would prefer a macro If this post helps click Yes --------------- Jacob Skaria "pol" wrote: Please help with a macros to copy the data to acitve cell from the cell just before the activce cell ( Only if the active cell is not null ). for example type name 1 XX 2 xx1 3 xx2 4 YY 5 yy1 6 yy2 In the above example I want to copy XX from A1 cell to range a2..a3, YY from A4 to copy to a4...a6. So I need to write the macro to copy conditionally if the active cell is not null . The out put should come type name 1 XX 2 XX xx1 3 XX xx2 4 YY 5 YY yy1 6 YY yy2 Please help With thanks Pol |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
need a macro to copy the data to the following cells
If the excel have 4000 or 5000 record it is not easy to do this by a user .
In the following example given , the unused cell is is A2..A3 and A5 .. A6 In a2.. a3 I have to copy XX and A5.. A6 I have to copy YY. So I think that is not a solution for giving the equation ( =A1) from unused cell (a2) . The copy and paste of this formula will not be wroked out. With thanks for the reply. Pol "Jacob Skaria" wrote: To do this you dont need a macro... --Select a unused cell say E2 --Enter the formula in cell E2 which references the top cell (=E1) --Copy the cell --Keeping the copy select the data range in ColA (say A1:A100). --Press F5. From Goto window select blanks --This will select all blanks.. Now Right clickPasteSpecialFormulas. will fill in with the data you need.. --Once done you can select col A Copy PasteSpecialValues to turn formulas to actual values PS: Post back if you still would prefer a macro If this post helps click Yes --------------- Jacob Skaria "pol" wrote: Please help with a macros to copy the data to acitve cell from the cell just before the activce cell ( Only if the active cell is not null ). for example type name 1 XX 2 xx1 3 xx2 4 YY 5 yy1 6 yy2 In the above example I want to copy XX from A1 cell to range a2..a3, YY from A4 to copy to a4...a6. So I need to write the macro to copy conditionally if the active cell is not null . The out put should come type name 1 XX 2 XX xx1 3 XX xx2 4 YY 5 YY yy1 6 YY yy2 Please help With thanks Pol |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
need a macro to copy the data to the following cells
By unused cell I mean a cell out side this range...Try out entering the
formula in E2 itself copy the cell (not the formula..). Select your data range ie Columna A. Use Go To window to select blanks and then PasteSpecial formulas... Even if you have an entire column of data this way works... If this post helps click Yes --------------- Jacob Skaria "pol" wrote: If the excel have 4000 or 5000 record it is not easy to do this by a user . In the following example given , the unused cell is is A2..A3 and A5 .. A6 In a2.. a3 I have to copy XX and A5.. A6 I have to copy YY. So I think that is not a solution for giving the equation ( =A1) from unused cell (a2) . The copy and paste of this formula will not be wroked out. With thanks for the reply. Pol "Jacob Skaria" wrote: To do this you dont need a macro... --Select a unused cell say E2 --Enter the formula in cell E2 which references the top cell (=E1) --Copy the cell --Keeping the copy select the data range in ColA (say A1:A100). --Press F5. From Goto window select blanks --This will select all blanks.. Now Right clickPasteSpecialFormulas. will fill in with the data you need.. --Once done you can select col A Copy PasteSpecialValues to turn formulas to actual values PS: Post back if you still would prefer a macro If this post helps click Yes --------------- Jacob Skaria "pol" wrote: Please help with a macros to copy the data to acitve cell from the cell just before the activce cell ( Only if the active cell is not null ). for example type name 1 XX 2 xx1 3 xx2 4 YY 5 yy1 6 yy2 In the above example I want to copy XX from A1 cell to range a2..a3, YY from A4 to copy to a4...a6. So I need to write the macro to copy conditionally if the active cell is not null . The out put should come type name 1 XX 2 XX xx1 3 XX xx2 4 YY 5 YY yy1 6 YY yy2 Please help With thanks Pol |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
need a macro to copy the data to the following cells
Blanks cells are selected from the Goto Window Special select 'Blanks'
OR ..try the below macro which works on your data in ColA and ColB Sub MyMacro() Dim lngRow As Long For lngRow = 2 To Cells(Rows.Count, "B").End(xlUp).Row If Trim(Range("A" & lngRow)) = "" Then _ Range("A" & lngRow) = Range("A" & lngRow - 1) Next End Sub If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: By unused cell I mean a cell out side this range...Try out entering the formula in E2 itself copy the cell (not the formula..). Select your data range ie Columna A. Use Go To window to select blanks and then PasteSpecial formulas... Even if you have an entire column of data this way works... If this post helps click Yes --------------- Jacob Skaria "pol" wrote: If the excel have 4000 or 5000 record it is not easy to do this by a user . In the following example given , the unused cell is is A2..A3 and A5 .. A6 In a2.. a3 I have to copy XX and A5.. A6 I have to copy YY. So I think that is not a solution for giving the equation ( =A1) from unused cell (a2) . The copy and paste of this formula will not be wroked out. With thanks for the reply. Pol "Jacob Skaria" wrote: To do this you dont need a macro... --Select a unused cell say E2 --Enter the formula in cell E2 which references the top cell (=E1) --Copy the cell --Keeping the copy select the data range in ColA (say A1:A100). --Press F5. From Goto window select blanks --This will select all blanks.. Now Right clickPasteSpecialFormulas. will fill in with the data you need.. --Once done you can select col A Copy PasteSpecialValues to turn formulas to actual values PS: Post back if you still would prefer a macro If this post helps click Yes --------------- Jacob Skaria "pol" wrote: Please help with a macros to copy the data to acitve cell from the cell just before the activce cell ( Only if the active cell is not null ). for example type name 1 XX 2 xx1 3 xx2 4 YY 5 yy1 6 yy2 In the above example I want to copy XX from A1 cell to range a2..a3, YY from A4 to copy to a4...a6. So I need to write the macro to copy conditionally if the active cell is not null . The out put should come type name 1 XX 2 XX xx1 3 XX xx2 4 YY 5 YY yy1 6 YY yy2 Please help With thanks Pol |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
need a macro to copy the data to the following cells
I think may be you haven't got clear idea from my example. One column
contain Item size , another column in next row contain item name. The same item size may be repeated for 100s of item. So if the same item size is coming for following item, the item size will not be repeated for the item under the same size. 1000's of item size may come. But the orginal excel will be sorted acording to the size of the item. I need to copy each size against each item ( each row) . many itemsize and its item can be seen Size item A 1 2 3 B 4 5 6 Output A A 1 A 2 as follows With thanks Pol "Jacob Skaria" wrote: By unused cell I mean a cell out side this range...Try out entering the formula in E2 itself copy the cell (not the formula..). Select your data range ie Columna A. Use Go To window to select blanks and then PasteSpecial formulas... Even if you have an entire column of data this way works... If this post helps click Yes --------------- Jacob Skaria "pol" wrote: If the excel have 4000 or 5000 record it is not easy to do this by a user . In the following example given , the unused cell is is A2..A3 and A5 .. A6 In a2.. a3 I have to copy XX and A5.. A6 I have to copy YY. So I think that is not a solution for giving the equation ( =A1) from unused cell (a2) . The copy and paste of this formula will not be wroked out. With thanks for the reply. Pol "Jacob Skaria" wrote: To do this you dont need a macro... --Select a unused cell say E2 --Enter the formula in cell E2 which references the top cell (=E1) --Copy the cell --Keeping the copy select the data range in ColA (say A1:A100). --Press F5. From Goto window select blanks --This will select all blanks.. Now Right clickPasteSpecialFormulas. will fill in with the data you need.. --Once done you can select col A Copy PasteSpecialValues to turn formulas to actual values PS: Post back if you still would prefer a macro If this post helps click Yes --------------- Jacob Skaria "pol" wrote: Please help with a macros to copy the data to acitve cell from the cell just before the activce cell ( Only if the active cell is not null ). for example type name 1 XX 2 xx1 3 xx2 4 YY 5 yy1 6 yy2 In the above example I want to copy XX from A1 cell to range a2..a3, YY from A4 to copy to a4...a6. So I need to write the macro to copy conditionally if the active cell is not null . The out put should come type name 1 XX 2 XX xx1 3 XX xx2 4 YY 5 YY yy1 6 YY yy2 Please help With thanks Pol |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
need a macro to copy the data to the following cells
You need to have a macro to perform this task. I writine dprograms like this
before but I need more information from you to get it correct. 1) Are you groups always 3 rows or is there a different criteria that you use to seperate the groups of rows? 2) Are you combining the data into 1 cell or are you putting data in column B. 3) You said there was empty cells but your data didn't show any empty cells. Can you post an example showing the empty cells as well as data with different number of rows per group. "pol" wrote: Please help with a macros to copy the data to acitve cell from the cell just before the activce cell ( Only if the active cell is not null ). for example type name 1 XX 2 xx1 3 xx2 4 YY 5 yy1 6 yy2 In the above example I want to copy XX from A1 cell to range a2..a3, YY from A4 to copy to a4...a6. So I need to write the macro to copy conditionally if the active cell is not null . The out put should come type name 1 XX 2 XX xx1 3 XX xx2 4 YY 5 YY yy1 6 YY yy2 Please help With thanks Pol |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
need a macro to copy the data to the following cells
OK. Did you try the macro?
If this post helps click Yes --------------- Jacob Skaria "pol" wrote: I think may be you haven't got clear idea from my example. One column contain Item size , another column in next row contain item name. The same item size may be repeated for 100s of item. So if the same item size is coming for following item, the item size will not be repeated for the item under the same size. 1000's of item size may come. But the orginal excel will be sorted acording to the size of the item. I need to copy each size against each item ( each row) . many itemsize and its item can be seen Size item A 1 2 3 B 4 5 6 Output A A 1 A 2 as follows With thanks Pol "Jacob Skaria" wrote: By unused cell I mean a cell out side this range...Try out entering the formula in E2 itself copy the cell (not the formula..). Select your data range ie Columna A. Use Go To window to select blanks and then PasteSpecial formulas... Even if you have an entire column of data this way works... If this post helps click Yes --------------- Jacob Skaria "pol" wrote: If the excel have 4000 or 5000 record it is not easy to do this by a user . In the following example given , the unused cell is is A2..A3 and A5 .. A6 In a2.. a3 I have to copy XX and A5.. A6 I have to copy YY. So I think that is not a solution for giving the equation ( =A1) from unused cell (a2) . The copy and paste of this formula will not be wroked out. With thanks for the reply. Pol "Jacob Skaria" wrote: To do this you dont need a macro... --Select a unused cell say E2 --Enter the formula in cell E2 which references the top cell (=E1) --Copy the cell --Keeping the copy select the data range in ColA (say A1:A100). --Press F5. From Goto window select blanks --This will select all blanks.. Now Right clickPasteSpecialFormulas. will fill in with the data you need.. --Once done you can select col A Copy PasteSpecialValues to turn formulas to actual values PS: Post back if you still would prefer a macro If this post helps click Yes --------------- Jacob Skaria "pol" wrote: Please help with a macros to copy the data to acitve cell from the cell just before the activce cell ( Only if the active cell is not null ). for example type name 1 XX 2 xx1 3 xx2 4 YY 5 yy1 6 yy2 In the above example I want to copy XX from A1 cell to range a2..a3, YY from A4 to copy to a4...a6. So I need to write the macro to copy conditionally if the active cell is not null . The out put should come type name 1 XX 2 XX xx1 3 XX xx2 4 YY 5 YY yy1 6 YY yy2 Please help With thanks Pol |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
need a macro to copy the data to the following cells
example
A B C D 1 SIZE1 2 ITEM1 3 ITEM2 4 SIZE2 5 ITEM3 6 SIZE3 7 ITEM4 8 ITEM5 9 ITEM6 In the above example a2, a3,a5 etc are empty becuase A2, A3 will be the same size mentioned in A1. A5 will be the same in A4. So I need copy the value from A1 to a2,a3 , simillary the value from A4 to A5, value from A6 to A7,A8,A9. In short I have to copy from top cell value to the next cell if it is blank. With thanks Pol "Joel" wrote: You need to have a macro to perform this task. I writine dprograms like this before but I need more information from you to get it correct. 1) Are you groups always 3 rows or is there a different criteria that you use to seperate the groups of rows? 2) Are you combining the data into 1 cell or are you putting data in column B. 3) You said there was empty cells but your data didn't show any empty cells. Can you post an example showing the empty cells as well as data with different number of rows per group. "pol" wrote: Please help with a macros to copy the data to acitve cell from the cell just before the activce cell ( Only if the active cell is not null ). for example type name 1 XX 2 xx1 3 xx2 4 YY 5 yy1 6 yy2 In the above example I want to copy XX from A1 cell to range a2..a3, YY from A4 to copy to a4...a6. So I need to write the macro to copy conditionally if the active cell is not null . The out put should come type name 1 XX 2 XX xx1 3 XX xx2 4 YY 5 YY yy1 6 YY yy2 Please help With thanks Pol |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
need a macro to copy the data to the following cells
This should work. It makes a big difference when a good example is posted.
Sub test() LastRow = Range("B" & Rows.Count).End(xlUp).Row Col_A = "" For RowCount = 1 To LastRow If Range("A" & RowCount) = "" Then Range("A" & RowCount) = Col_A Else Col_A = Range("A" & RowCount) End If Next RowCount End Sub "pol" wrote: example A B C D 1 SIZE1 2 ITEM1 3 ITEM2 4 SIZE2 5 ITEM3 6 SIZE3 7 ITEM4 8 ITEM5 9 ITEM6 In the above example a2, a3,a5 etc are empty becuase A2, A3 will be the same size mentioned in A1. A5 will be the same in A4. So I need copy the value from A1 to a2,a3 , simillary the value from A4 to A5, value from A6 to A7,A8,A9. In short I have to copy from top cell value to the next cell if it is blank. With thanks Pol "Joel" wrote: You need to have a macro to perform this task. I writine dprograms like this before but I need more information from you to get it correct. 1) Are you groups always 3 rows or is there a different criteria that you use to seperate the groups of rows? 2) Are you combining the data into 1 cell or are you putting data in column B. 3) You said there was empty cells but your data didn't show any empty cells. Can you post an example showing the empty cells as well as data with different number of rows per group. "pol" wrote: Please help with a macros to copy the data to acitve cell from the cell just before the activce cell ( Only if the active cell is not null ). for example type name 1 XX 2 xx1 3 xx2 4 YY 5 yy1 6 yy2 In the above example I want to copy XX from A1 cell to range a2..a3, YY from A4 to copy to a4...a6. So I need to write the macro to copy conditionally if the active cell is not null . The out put should come type name 1 XX 2 XX xx1 3 XX xx2 4 YY 5 YY yy1 6 YY yy2 Please help With thanks Pol |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
need a macro to copy the data to the following cells
works fine. thanks lot
"Joel" wrote: This should work. It makes a big difference when a good example is posted. Sub test() LastRow = Range("B" & Rows.Count).End(xlUp).Row Col_A = "" For RowCount = 1 To LastRow If Range("A" & RowCount) = "" Then Range("A" & RowCount) = Col_A Else Col_A = Range("A" & RowCount) End If Next RowCount End Sub "pol" wrote: example A B C D 1 SIZE1 2 ITEM1 3 ITEM2 4 SIZE2 5 ITEM3 6 SIZE3 7 ITEM4 8 ITEM5 9 ITEM6 In the above example a2, a3,a5 etc are empty becuase A2, A3 will be the same size mentioned in A1. A5 will be the same in A4. So I need copy the value from A1 to a2,a3 , simillary the value from A4 to A5, value from A6 to A7,A8,A9. In short I have to copy from top cell value to the next cell if it is blank. With thanks Pol "Joel" wrote: You need to have a macro to perform this task. I writine dprograms like this before but I need more information from you to get it correct. 1) Are you groups always 3 rows or is there a different criteria that you use to seperate the groups of rows? 2) Are you combining the data into 1 cell or are you putting data in column B. 3) You said there was empty cells but your data didn't show any empty cells. Can you post an example showing the empty cells as well as data with different number of rows per group. "pol" wrote: Please help with a macros to copy the data to acitve cell from the cell just before the activce cell ( Only if the active cell is not null ). for example type name 1 XX 2 xx1 3 xx2 4 YY 5 yy1 6 yy2 In the above example I want to copy XX from A1 cell to range a2..a3, YY from A4 to copy to a4...a6. So I need to write the macro to copy conditionally if the active cell is not null . The out put should come type name 1 XX 2 XX xx1 3 XX xx2 4 YY 5 YY yy1 6 YY yy2 Please help With thanks Pol |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need a macro to copy cells | New Users to Excel | |||
Macro Search and Copy between Cells with unique data | Excel Discussion (Misc queries) | |||
macro copy/paste data from multiple cells to multiple cells | Excel Discussion (Misc queries) | |||
Macro to copy cells | Excel Discussion (Misc queries) | |||
Correctly copy cells with a macro | Excel Discussion (Misc queries) |