Tags: cell, code, code-, copy, empty, excel, microsoft, msdn, paste, range, software, special, therefore, vba, write

VBA Code- Copy & Paste in Blank Range

On Microsoft » Microsoft Excel

36,973 words with 9 Comments; publish: Thu, 22 May 2008 05:58:00 GMT; (306156.25, « »)

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.

All Comments

Leave a comment...

  • 9 Comments
    • sub copytonextcol()

      mr=5 'row

      lastcol=cells(mr,columns.count).end(xltoleft).column+1

      Range("D5:D93").copy

      cells(mr,lastcol).PasteSpecial Paste:=xlPasteValues

      end sub

      --

      Don Guillett

      Microsoft MVP Excel

      SalesAid Software

      dguillett1.excel.todaysummary.com.austin.rr.com

      "Youlan" <Youlan.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      news:6C74DF1B-FAF1-4EBD-BE90-24562544B18B.excel.todaysummary.com.microsoft.com...

      > 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.

      #1; Thu, 22 May 2008 05:59:00 GMT
    • "Don Guillett" wrote:

      > sub copytonextcol()

      > mr=5 'row

      > lastcol=cells(mr,columns.count).end(xltoleft).column+1

      > Range("D5:D93").copy

      > cells(mr,lastcol).PasteSpecial Paste:=xlPasteValues

      > end sub

      > --

      > Don Guillett

      > Microsoft MVP Excel

      > SalesAid Software

      > dguillett1.excel.todaysummary.com.austin.rr.com

      > "Youlan" <Youlan.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      > news:6C74DF1B-FAF1-4EBD-BE90-24562544B18B.excel.todaysummary.com.microsoft.com...

      > > 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.

      >

      #2; Thu, 22 May 2008 06:00:00 GMT
    • Youlan wrote

      _______________?

      --

      Don Guillett

      Microsoft MVP Excel

      SalesAid Software

      dguillett1.excel.todaysummary.com.austin.rr.com

      "Youlan" <Youlan.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      news:45935120-3F98-465B-A8B7-E340F2CBF3F7.excel.todaysummary.com.microsoft.com...

      >

      > "Don Guillett" wrote:

      >> sub copytonextcol()

      >> mr=5 'row

      >> lastcol=cells(mr,columns.count).end(xltoleft).column+1

      >> Range("D5:D93").copy

      >> cells(mr,lastcol).PasteSpecial Paste:=xlPasteValues

      >> end sub

      >> --

      >> Don Guillett

      >> Microsoft MVP Excel

      >> SalesAid Software

      >> dguillett1.excel.todaysummary.com.austin.rr.com

      >> "Youlan" <Youlan.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      >> news:6C74DF1B-FAF1-4EBD-BE90-24562544B18B.excel.todaysummary.com.microsoft.com...

      >> > 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; Thu, 22 May 2008 06:01:00 GMT
    • 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

      > dguillett1.excel.todaysummary.com.austin.rr.com

      > "Youlan" <Youlan.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      > news:45935120-3F98-465B-A8B7-E340F2CBF3F7.excel.todaysummary.com.microsoft.com...

      > >

      > >

      > > "Don Guillett" wrote:

      > >

      > >> sub copytonextcol()

      > >> mr=5 'row

      > >> lastcol=cells(mr,columns.count).end(xltoleft).column+1

      > >> Range("D5:D93").copy

      > >> cells(mr,lastcol).PasteSpecial Paste:=xlPasteValues

      > >> end sub

      > >> --

      > >> Don Guillett

      > >> Microsoft MVP Excel

      > >> SalesAid Software

      > >> dguillett1.excel.todaysummary.com.austin.rr.com

      > >> "Youlan" <Youlan.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      > >> news:6C74DF1B-FAF1-4EBD-BE90-24562544B18B.excel.todaysummary.com.microsoft.com...

      > >> > 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; Thu, 22 May 2008 06:02:00 GMT
    • Did you copy all lines?

      sub copytonextcol()

      mr=5 'row

      lastcol=cells(mr,columns.count).end(xltoleft).column+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

      dguillett1.excel.todaysummary.com.austin.rr.com

      "Youlan" <Youlan.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      news:3C8ED230-B8DA-4F4F-AD35-17C9A15E8753.excel.todaysummary.com.microsoft.com...

      > 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

      >> dguillett1.excel.todaysummary.com.austin.rr.com

      >> "Youlan" <Youlan.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      >> news:45935120-3F98-465B-A8B7-E340F2CBF3F7.excel.todaysummary.com.microsoft.com...

      >> >

      >> >

      >> > "Don Guillett" wrote:

      >> >

      >> >> sub copytonextcol()

      >> >> mr=5 'row

      >> >> lastcol=cells(mr,columns.count).end(xltoleft).column+1

      >> >> Range("D5:D93").copy

      >> >> cells(mr,lastcol).PasteSpecial Paste:=xlPasteValues

      >> >> end sub

      >> >> --

      >> >> Don Guillett

      >> >> Microsoft MVP Excel

      >> >> SalesAid Software

      >> >> dguillett1.excel.todaysummary.com.austin.rr.com

      >> >> "Youlan" <Youlan.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      >> >> news:6C74DF1B-FAF1-4EBD-BE90-24562544B18B.excel.todaysummary.com.microsoft.com...

      >> >> > 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; Thu, 22 May 2008 06:03:00 GMT
    • 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).column+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

      > dguillett1.excel.todaysummary.com.austin.rr.com

      > "Youlan" <Youlan.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      > news:3C8ED230-B8DA-4F4F-AD35-17C9A15E8753.excel.todaysummary.com.microsoft.com...

      > > 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

      > >> dguillett1.excel.todaysummary.com.austin.rr.com

      > >> "Youlan" <Youlan.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      > >> news:45935120-3F98-465B-A8B7-E340F2CBF3F7.excel.todaysummary.com.microsoft.com...

      > >> >

      > >> >

      > >> > "Don Guillett" wrote:

      > >> >

      > >> >> sub copytonextcol()

      > >> >> mr=5 'row

      > >> >> lastcol=cells(mr,columns.count).end(xltoleft).column+1

      > >> >> Range("D5:D93").copy

      > >> >> cells(mr,lastcol).PasteSpecial Paste:=xlPasteValues

      > >> >> end sub

      > >> >> --

      > >> >> Don Guillett

      > >> >> Microsoft MVP Excel

      > >> >> SalesAid Software

      > >> >> dguillett1.excel.todaysummary.com.austin.rr.com

      > >> >> "Youlan" <Youlan.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      > >> >> news:6C74DF1B-FAF1-4EBD-BE90-24562544B18B.excel.todaysummary.com.microsoft.com...

      > >> >> > 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; Thu, 22 May 2008 06:05:00 GMT
    • 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

      dguillett1.excel.todaysummary.com.austin.rr.com

      "Youlan" <Youlan.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      news:F3DD3923-1528-4916-AE6E-F7D20A922A6B.excel.todaysummary.com.microsoft.com...

      > 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).column+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

      >> dguillett1.excel.todaysummary.com.austin.rr.com

      >> "Youlan" <Youlan.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      >> news:3C8ED230-B8DA-4F4F-AD35-17C9A15E8753.excel.todaysummary.com.microsoft.com...

      >> > 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

      >> >> dguillett1.excel.todaysummary.com.austin.rr.com

      >> >> "Youlan" <Youlan.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      >> >> news:45935120-3F98-465B-A8B7-E340F2CBF3F7.excel.todaysummary.com.microsoft.com...

      >> >> >

      >> >> >

      >> >> > "Don Guillett" wrote:

      >> >> >

      >> >> >> sub copytonextcol()

      >> >> >> mr=5 'row

      >> >> >> lastcol=cells(mr,columns.count).end(xltoleft).column+1

      >> >> >> Range("D5:D93").copy

      >> >> >> cells(mr,lastcol).PasteSpecial Paste:=xlPasteValues

      >> >> >> end sub

      >> >> >> --

      >> >> >> Don Guillett

      >> >> >> Microsoft MVP Excel

      >> >> >> SalesAid Software

      >> >> >> dguillett1.excel.todaysummary.com.austin.rr.com

      >> >> >> "Youlan" <Youlan.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      >> >> >> news:6C74DF1B-FAF1-4EBD-BE90-24562544B18B.excel.todaysummary.com.microsoft.com...

      >> >> >> > 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; Thu, 22 May 2008 06:05:00 GMT
    • 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

      > dguillett1.excel.todaysummary.com.austin.rr.com

      > "Youlan" <Youlan.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      > news:F3DD3923-1528-4916-AE6E-F7D20A922A6B.excel.todaysummary.com.microsoft.com...

      > > 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).column+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

      > >> dguillett1.excel.todaysummary.com.austin.rr.com

      > >> "Youlan" <Youlan.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      > >> news:3C8ED230-B8DA-4F4F-AD35-17C9A15E8753.excel.todaysummary.com.microsoft.com...

      > >> > 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

      > >> >> dguillett1.excel.todaysummary.com.austin.rr.com

      > >> >> "Youlan" <Youlan.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      > >> >> news:45935120-3F98-465B-A8B7-E340F2CBF3F7.excel.todaysummary.com.microsoft.com...

      > >> >> >

      > >> >> >

      > >> >> > "Don Guillett" wrote:

      > >> >> >

      > >> >> >> sub copytonextcol()

      > >> >> >> mr=5 'row

      > >> >> >> lastcol=cells(mr,columns.count).end(xltoleft).column+1

      > >> >> >> Range("D5:D93").copy

      > >> >> >> cells(mr,lastcol).PasteSpecial Paste:=xlPasteValues

      > >> >> >> end sub

      > >> >> >> --

      > >> >> >> Don Guillett

      > >> >> >> Microsoft MVP Excel

      > >> >> >> SalesAid Software

      > >> >> >> dguillett1.excel.todaysummary.com.austin.rr.com

      > >> >> >> "Youlan" <Youlan.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      > >> >> >> news:6C74DF1B-FAF1-4EBD-BE90-24562544B18B.excel.todaysummary.com.microsoft.com...

      > >> >> >> > 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; Thu, 22 May 2008 06:06:00 GMT
    • 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

      dguillett1.excel.todaysummary.com.austin.rr.com

      "Youlan" <Youlan.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      news:ED64556E-2A43-4247-A395-F74DE395A448.excel.todaysummary.com.microsoft.com...

      > 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

      >> dguillett1.excel.todaysummary.com.austin.rr.com

      >> "Youlan" <Youlan.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      >> news:F3DD3923-1528-4916-AE6E-F7D20A922A6B.excel.todaysummary.com.microsoft.com...

      >> > 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).column+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

      >> >> dguillett1.excel.todaysummary.com.austin.rr.com

      >> >> "Youlan" <Youlan.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      >> >> news:3C8ED230-B8DA-4F4F-AD35-17C9A15E8753.excel.todaysummary.com.microsoft.com...

      >> >> > 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

      >> >> >> dguillett1.excel.todaysummary.com.austin.rr.com

      >> >> >> "Youlan" <Youlan.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      >> >> >> news:45935120-3F98-465B-A8B7-E340F2CBF3F7.excel.todaysummary.com.microsoft.com...

      >> >> >> >

      >> >> >> >

      >> >> >> > "Don Guillett" wrote:

      >> >> >> >

      >> >> >> >> sub copytonextcol()

      >> >> >> >> mr=5 'row

      >> >> >> >> lastcol=cells(mr,columns.count).end(xltoleft).column+1

      >> >> >> >> Range("D5:D93").copy

      >> >> >> >> cells(mr,lastcol).PasteSpecial Paste:=xlPasteValues

      >> >> >> >> end sub

      >> >> >> >> --

      >> >> >> >> Don Guillett

      >> >> >> >> Microsoft MVP Excel

      >> >> >> >> SalesAid Software

      >> >> >> >> dguillett1.excel.todaysummary.com.austin.rr.com

      >> >> >> >> "Youlan" <Youlan.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      >> >> >> >> news:6C74DF1B-FAF1-4EBD-BE90-24562544B18B.excel.todaysummary.com.microsoft.com...

      >> >> >> >> > 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; Thu, 22 May 2008 06:08:00 GMT