| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA Code- Copy & Paste in Blank Range
Hi,
I'm using Excel 2002 I'm trying to write a code to copy and paste special a range of data in the next empty cell. Therefore if cell j5 has data it would start pasting it in only k5 and so on. This is what I have so far but its pasting in all the columns where row 5 is blank: If Range("j5").Value = " " Then End If ActiveWindow.SmallScroll ToRight:=-1 Range("D5:D93").Select Selection.copy ActiveWindow.SmallScroll Down:=-123 ActiveWindow.SmallScroll ToRight:=15 ActiveWindow.SmallScroll Down:=-15 Range("j5").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If Else I would greatly appreciate any help with this. Thanks in advance. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA Code- Copy & Paste in Blank Range
"Don Guillett" wrote: sub copytonextcol() mr=5 'row lastcol=cells(mr,columns.count).end(xltoleft).colu mn+1 Range("D5:D93").copy cells(mr,lastcol).PasteSpecial Paste:=xlPasteValues end sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Youlan" wrote in message ... Hi, I'm using Excel 2002 I'm trying to write a code to copy and paste special a range of data in the next empty cell. Therefore if cell j5 has data it would start pasting it in only k5 and so on. This is what I have so far but its pasting in all the columns where row 5 is blank: If Range("j5").Value = " " Then End If ActiveWindow.SmallScroll ToRight:=-1 Range("D5:D93").Select Selection.copy ActiveWindow.SmallScroll Down:=-123 ActiveWindow.SmallScroll ToRight:=15 ActiveWindow.SmallScroll Down:=-15 Range("j5").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If Else I would greatly appreciate any help with this. Thanks in advance. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA Code- Copy & Paste in Blank Range
Youlan wrote
_______________? -- Don Guillett Microsoft MVP Excel SalesAid Software "Youlan" wrote in message ... "Don Guillett" wrote: sub copytonextcol() mr=5 'row lastcol=cells(mr,columns.count).end(xltoleft).colu mn+1 Range("D5:D93").copy cells(mr,lastcol).PasteSpecial Paste:=xlPasteValues end sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Youlan" wrote in message ... Hi, I'm using Excel 2002 I'm trying to write a code to copy and paste special a range of data in the next empty cell. Therefore if cell j5 has data it would start pasting it in only k5 and so on. This is what I have so far but its pasting in all the columns where row 5 is blank: If Range("j5").Value = " " Then End If ActiveWindow.SmallScroll ToRight:=-1 Range("D5:D93").Select Selection.copy ActiveWindow.SmallScroll Down:=-123 ActiveWindow.SmallScroll ToRight:=15 ActiveWindow.SmallScroll Down:=-15 Range("j5").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If Else I would greatly appreciate any help with this. Thanks in advance. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA Code- Copy & Paste in Blank Range
Hi Don,
I don't know why you wern't able to see what I wrote before. Thanks for your help but I am still having a little problem because when I run the macro I get the following compile error: "Expected End Sub" Can you help please? "Don Guillett" wrote: Youlan wrote _______________? -- Don Guillett Microsoft MVP Excel SalesAid Software "Youlan" wrote in message ... "Don Guillett" wrote: sub copytonextcol() mr=5 'row lastcol=cells(mr,columns.count).end(xltoleft).colu mn+1 Range("D5:D93").copy cells(mr,lastcol).PasteSpecial Paste:=xlPasteValues end sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Youlan" wrote in message ... Hi, I'm using Excel 2002 I'm trying to write a code to copy and paste special a range of data in the next empty cell. Therefore if cell j5 has data it would start pasting it in only k5 and so on. This is what I have so far but its pasting in all the columns where row 5 is blank: If Range("j5").Value = " " Then End If ActiveWindow.SmallScroll ToRight:=-1 Range("D5:D93").Select Selection.copy ActiveWindow.SmallScroll Down:=-123 ActiveWindow.SmallScroll ToRight:=15 ActiveWindow.SmallScroll Down:=-15 Range("j5").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If Else I would greatly appreciate any help with this. Thanks in advance. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA Code- Copy & Paste in Blank Range
Did you copy all lines?
sub copytonextcol() mr=5 'row lastcol=cells(mr,columns.count).end(xltoleft).colu mn+1 Range("D5:D93").copy cells(mr,lastcol).PasteSpecial Paste:=xlPasteValues 'line below added Application.CutCopyMode = False end sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Youlan" wrote in message ... Hi Don, I don't know why you wern't able to see what I wrote before. Thanks for your help but I am still having a little problem because when I run the macro I get the following compile error: "Expected End Sub" Can you help please? "Don Guillett" wrote: Youlan wrote _______________? -- Don Guillett Microsoft MVP Excel SalesAid Software "Youlan" wrote in message ... "Don Guillett" wrote: sub copytonextcol() mr=5 'row lastcol=cells(mr,columns.count).end(xltoleft).colu mn+1 Range("D5:D93").copy cells(mr,lastcol).PasteSpecial Paste:=xlPasteValues end sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Youlan" wrote in message ... Hi, I'm using Excel 2002 I'm trying to write a code to copy and paste special a range of data in the next empty cell. Therefore if cell j5 has data it would start pasting it in only k5 and so on. This is what I have so far but its pasting in all the columns where row 5 is blank: If Range("j5").Value = " " Then End If ActiveWindow.SmallScroll ToRight:=-1 Range("D5:D93").Select Selection.copy ActiveWindow.SmallScroll Down:=-123 ActiveWindow.SmallScroll ToRight:=15 ActiveWindow.SmallScroll Down:=-15 Range("j5").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If Else I would greatly appreciate any help with this. Thanks in advance. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA Code- Copy & Paste in Blank Range
Thanks Don,
It works but not with a commandbutton ( I was doing it with a command button before maybe thats why it wasn't working before) Also I wanted it to start pasting in the next blank column (starting at row 5) not the last column and not the entire column. Also this process is to be repeated everytime the macro is evoked. Can this be done? I hope I'm not giving you too much trouble. Thanks again. "Don Guillett" wrote: Did you copy all lines? sub copytonextcol() mr=5 'row lastcol=cells(mr,columns.count).end(xltoleft).colu mn+1 Range("D5:D93").copy cells(mr,lastcol).PasteSpecial Paste:=xlPasteValues 'line below added Application.CutCopyMode = False end sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Youlan" wrote in message ... Hi Don, I don't know why you wern't able to see what I wrote before. Thanks for your help but I am still having a little problem because when I run the macro I get the following compile error: "Expected End Sub" Can you help please? "Don Guillett" wrote: Youlan wrote _______________? -- Don Guillett Microsoft MVP Excel SalesAid Software "Youlan" wrote in message ... "Don Guillett" wrote: sub copytonextcol() mr=5 'row lastcol=cells(mr,columns.count).end(xltoleft).colu mn+1 Range("D5:D93").copy cells(mr,lastcol).PasteSpecial Paste:=xlPasteValues end sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Youlan" wrote in message ... Hi, I'm using Excel 2002 I'm trying to write a code to copy and paste special a range of data in the next empty cell. Therefore if cell j5 has data it would start pasting it in only k5 and so on. This is what I have so far but its pasting in all the columns where row 5 is blank: If Range("j5").Value = " " Then End If ActiveWindow.SmallScroll ToRight:=-1 Range("D5:D93").Select Selection.copy ActiveWindow.SmallScroll Down:=-123 ActiveWindow.SmallScroll ToRight:=15 ActiveWindow.SmallScroll Down:=-15 Range("j5").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If Else I would greatly appreciate any help with this. Thanks in advance. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA Code- Copy & Paste in Blank Range
So I don't have to re-create, send a workbook along with what you want. Most
never use command buttons. I usually asign to a shape. Did one for a client the other day assigned to his logo. -- Don Guillett Microsoft MVP Excel SalesAid Software "Youlan" wrote in message ... Thanks Don, It works but not with a commandbutton ( I was doing it with a command button before maybe thats why it wasn't working before) Also I wanted it to start pasting in the next blank column (starting at row 5) not the last column and not the entire column. Also this process is to be repeated everytime the macro is evoked. Can this be done? I hope I'm not giving you too much trouble. Thanks again. "Don Guillett" wrote: Did you copy all lines? sub copytonextcol() mr=5 'row lastcol=cells(mr,columns.count).end(xltoleft).colu mn+1 Range("D5:D93").copy cells(mr,lastcol).PasteSpecial Paste:=xlPasteValues 'line below added Application.CutCopyMode = False end sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Youlan" wrote in message ... Hi Don, I don't know why you wern't able to see what I wrote before. Thanks for your help but I am still having a little problem because when I run the macro I get the following compile error: "Expected End Sub" Can you help please? "Don Guillett" wrote: Youlan wrote _______________? -- Don Guillett Microsoft MVP Excel SalesAid Software "Youlan" wrote in message ... "Don Guillett" wrote: sub copytonextcol() mr=5 'row lastcol=cells(mr,columns.count).end(xltoleft).colu mn+1 Range("D5:D93").copy cells(mr,lastcol).PasteSpecial Paste:=xlPasteValues end sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Youlan" wrote in message ... Hi, I'm using Excel 2002 I'm trying to write a code to copy and paste special a range of data in the next empty cell. Therefore if cell j5 has data it would start pasting it in only k5 and so on. This is what I have so far but its pasting in all the columns where row 5 is blank: If Range("j5").Value = " " Then End If ActiveWindow.SmallScroll ToRight:=-1 Range("D5:D93").Select Selection.copy ActiveWindow.SmallScroll Down:=-123 ActiveWindow.SmallScroll ToRight:=15 ActiveWindow.SmallScroll Down:=-15 Range("j5").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If Else I would greatly appreciate any help with this. Thanks in advance. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA Code- Copy & Paste in Blank Range
Hi Don,
Yeah, I realize command buttons can be a little finicky. I'll just use a graphic. I'm going to send the workbook to the e-mail address. I've typed what I want to do in the comments in D4. Thanks again "Don Guillett" wrote: So I don't have to re-create, send a workbook along with what you want. Most never use command buttons. I usually asign to a shape. Did one for a client the other day assigned to his logo. -- Don Guillett Microsoft MVP Excel SalesAid Software "Youlan" wrote in message ... Thanks Don, It works but not with a commandbutton ( I was doing it with a command button before maybe thats why it wasn't working before) Also I wanted it to start pasting in the next blank column (starting at row 5) not the last column and not the entire column. Also this process is to be repeated everytime the macro is evoked. Can this be done? I hope I'm not giving you too much trouble. Thanks again. "Don Guillett" wrote: Did you copy all lines? sub copytonextcol() mr=5 'row lastcol=cells(mr,columns.count).end(xltoleft).colu mn+1 Range("D5:D93").copy cells(mr,lastcol).PasteSpecial Paste:=xlPasteValues 'line below added Application.CutCopyMode = False end sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Youlan" wrote in message ... Hi Don, I don't know why you wern't able to see what I wrote before. Thanks for your help but I am still having a little problem because when I run the macro I get the following compile error: "Expected End Sub" Can you help please? "Don Guillett" wrote: Youlan wrote _______________? -- Don Guillett Microsoft MVP Excel SalesAid Software "Youlan" wrote in message ... "Don Guillett" wrote: sub copytonextcol() mr=5 'row lastcol=cells(mr,columns.count).end(xltoleft).colu mn+1 Range("D5:D93").copy cells(mr,lastcol).PasteSpecial Paste:=xlPasteValues end sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Youlan" wrote in message ... Hi, I'm using Excel 2002 I'm trying to write a code to copy and paste special a range of data in the next empty cell. Therefore if cell j5 has data it would start pasting it in only k5 and so on. This is what I have so far but its pasting in all the columns where row 5 is blank: If Range("j5").Value = " " Then End If ActiveWindow.SmallScroll ToRight:=-1 Range("D5:D93").Select Selection.copy ActiveWindow.SmallScroll Down:=-123 ActiveWindow.SmallScroll ToRight:=15 ActiveWindow.SmallScroll Down:=-15 Range("j5").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If Else I would greatly appreciate any help with this. Thanks in advance. |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA Code- Copy & Paste in Blank Range
Sent him this.
Sub CopyToNextAvailCol() mr = 5 mc = "d" lc = Cells(mr, mc).End(xlToRight).Column + 1 Range(Cells(mr, "f"), Cells(93, "f")).Value = _ Range(Cells(mr, lc - 1), Cells(93, lc - 1)).Value Range(Cells(mr, lc), Cells(93, lc)).Value = _ Range(Cells(mr, mc), Cells(93, mc)).Value End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Youlan" wrote in message ... Hi Don, Yeah, I realize command buttons can be a little finicky. I'll just use a graphic. I'm going to send the workbook to the e-mail address. I've typed what I want to do in the comments in D4. Thanks again "Don Guillett" wrote: So I don't have to re-create, send a workbook along with what you want. Most never use command buttons. I usually asign to a shape. Did one for a client the other day assigned to his logo. -- Don Guillett Microsoft MVP Excel SalesAid Software "Youlan" wrote in message ... Thanks Don, It works but not with a commandbutton ( I was doing it with a command button before maybe thats why it wasn't working before) Also I wanted it to start pasting in the next blank column (starting at row 5) not the last column and not the entire column. Also this process is to be repeated everytime the macro is evoked. Can this be done? I hope I'm not giving you too much trouble. Thanks again. "Don Guillett" wrote: Did you copy all lines? sub copytonextcol() mr=5 'row lastcol=cells(mr,columns.count).end(xltoleft).colu mn+1 Range("D5:D93").copy cells(mr,lastcol).PasteSpecial Paste:=xlPasteValues 'line below added Application.CutCopyMode = False end sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Youlan" wrote in message ... Hi Don, I don't know why you wern't able to see what I wrote before. Thanks for your help but I am still having a little problem because when I run the macro I get the following compile error: "Expected End Sub" Can you help please? "Don Guillett" wrote: Youlan wrote _______________? -- Don Guillett Microsoft MVP Excel SalesAid Software "Youlan" wrote in message ... "Don Guillett" wrote: sub copytonextcol() mr=5 'row lastcol=cells(mr,columns.count).end(xltoleft).colu mn+1 Range("D5:D93").copy cells(mr,lastcol).PasteSpecial Paste:=xlPasteValues end sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Youlan" wrote in message ... Hi, I'm using Excel 2002 I'm trying to write a code to copy and paste special a range of data in the next empty cell. Therefore if cell j5 has data it would start pasting it in only k5 and so on. This is what I have so far but its pasting in all the columns where row 5 is blank: If Range("j5").Value = " " Then End If ActiveWindow.SmallScroll ToRight:=-1 Range("D5:D93").Select Selection.copy ActiveWindow.SmallScroll Down:=-123 ActiveWindow.SmallScroll ToRight:=15 ActiveWindow.SmallScroll Down:=-15 Range("j5").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If Else I would greatly appreciate any help with this. Thanks in advance. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| copy and paste using vb code | Excel Discussion (Misc queries) | |||
| copy and paste formula using vb code | Excel Discussion (Misc queries) | |||
| VBA-code for search,copy and paste | Excel Discussion (Misc queries) | |||
| CheckBox Code to copy & paste range | Excel Discussion (Misc queries) | |||
| copy and paste code problem | Excel Worksheet Functions | |||
Linear Mode
