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.

VBA Code- Copy & Paste in Blank Range
 

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.

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.

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.

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.

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.

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.

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.

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.

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.