PC Review
Home Forums Reviews Articles Register
Member Login:

PC Review > Forums > Newsgroups > Microsoft Excel > Microsoft Excel Programming > How to copy and paste a partial row (i.e. not the EntireRow)

Reply
Thread Tools Rate Thread

How to copy and paste a partial row (i.e. not the EntireRow)

 
 
CROD
Guest
Posts: n/a
 
      12th Nov 2008
I am looking to copy only a segement of a row from a primary database to a
summary sheet. Currently, my script below copies the EntireRow
(c.EntireRow.Copy_). Is there a way to set a range such that I only copy
over...say "d to Z" versus the EntireRow?

Sub Rectangle2_Click()

CodeName = InputBox("Enter Project Code", Range("a1:a3").Find(Cells(1,
1)).Offset(rowOffset:=2, columnOffset:=1).Value)

Range("a1:a5").Find(Cells(1, 1)).Offset(rowOffset:=1, columnOffset:=2).Value
= CodeName

Worksheets("Extract").Select
Worksheets("Extract").Range("a8:z2000").ClearContents

RowCount = 8

With Worksheets("Database").Range("a1:z2000")
Set c = .Find(Cells(3, 2))

If Not c Is Nothing Then
firstAddress = c.Address
Do

c.EntireRow.Copy _
Destination:=Worksheets("APTS Extract").Rows(RowCount)
RowCount = RowCount + 1

Set c = .FindNext(c)

Loop While Not c Is Nothing And c.Address <> firstAddress
End If

End With

End Sub
 
Reply With Quote
 
 
 
 
Don Guillett
Guest
Posts: n/a
 
      12th Nov 2008
Range(cells(c.row,"d"),cells(c.row,"z")).copy

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"CROD" <(E-Mail Removed)> wrote in message
news:B9165EF2-BDF0-457D-B39C-(E-Mail Removed)...
>I am looking to copy only a segement of a row from a primary database to a
> summary sheet. Currently, my script below copies the EntireRow
> (c.EntireRow.Copy_). Is there a way to set a range such that I only copy
> over...say "d to Z" versus the EntireRow?
>
> Sub Rectangle2_Click()
>
> CodeName = InputBox("Enter Project Code", Range("a1:a3").Find(Cells(1,
> 1)).Offset(rowOffset:=2, columnOffset:=1).Value)
>
> Range("a1:a5").Find(Cells(1, 1)).Offset(rowOffset:=1,
> columnOffset:=2).Value
> = CodeName
>
> Worksheets("Extract").Select
> Worksheets("Extract").Range("a8:z2000").ClearContents
>
> RowCount = 8
>
> With Worksheets("Database").Range("a1:z2000")
> Set c = .Find(Cells(3, 2))
>
> If Not c Is Nothing Then
> firstAddress = c.Address
> Do
>
> c.EntireRow.Copy _
> Destination:=Worksheets("APTS Extract").Rows(RowCount)
> RowCount = RowCount + 1
>
> Set c = .FindNext(c)
>
> Loop While Not c Is Nothing And c.Address <> firstAddress
> End If
>
> End With
>
> End Sub
 
Reply With Quote
 
CROD
Guest
Posts: n/a
 
      12th Nov 2008
Don,

Thanks for the response!

I've replace "c.EntireRow" with your script
"Range(cells(c.row,"d"),cells(c.row,"z")).copy and get a "Run-time
error'1004". Your further assistance is greatly appreciated!

"Don Guillett" wrote:

> Range(cells(c.row,"d"),cells(c.row,"z")).copy
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "CROD" <(E-Mail Removed)> wrote in message
> news:B9165EF2-BDF0-457D-B39C-(E-Mail Removed)...
> >I am looking to copy only a segement of a row from a primary database to a
> > summary sheet. Currently, my script below copies the EntireRow
> > (c.EntireRow.Copy_). Is there a way to set a range such that I only copy
> > over...say "d to Z" versus the EntireRow?
> >
> > Sub Rectangle2_Click()
> >
> > CodeName = InputBox("Enter Project Code", Range("a1:a3").Find(Cells(1,
> > 1)).Offset(rowOffset:=2, columnOffset:=1).Value)
> >
> > Range("a1:a5").Find(Cells(1, 1)).Offset(rowOffset:=1,
> > columnOffset:=2).Value
> > = CodeName
> >
> > Worksheets("Extract").Select
> > Worksheets("Extract").Range("a8:z2000").ClearContents
> >
> > RowCount = 8
> >
> > With Worksheets("Database").Range("a1:z2000")
> > Set c = .Find(Cells(3, 2))
> >
> > If Not c Is Nothing Then
> > firstAddress = c.Address
> > Do
> >
> > c.EntireRow.Copy _
> > Destination:=Worksheets("APTS Extract").Rows(RowCount)
> > RowCount = RowCount + 1
> >
> > Set c = .FindNext(c)
> >
> > Loop While Not c Is Nothing And c.Address <> firstAddress
> > End If
> >
> > End With
> >
> > End Sub
>
>
 
Reply With Quote
 
Don Guillett
Guest
Posts: n/a
 
      13th Nov 2008
Sorry, I forgot to include the dot for the WITH

.Range(cells(c.row,"d"),cells(c.row,"z")).copy
or
.Range(.cells(c.row,"d"),.cells(c.row,"z")).copy


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"CROD" <(E-Mail Removed)> wrote in message
news:E757CF36-1FE9-4A05-A6D8-(E-Mail Removed)...
> Don,
>
> Thanks for the response!
>
> I've replace "c.EntireRow" with your script
> "Range(cells(c.row,"d"),cells(c.row,"z")).copy and get a "Run-time
> error'1004". Your further assistance is greatly appreciated!
>
> "Don Guillett" wrote:
>
>> Range(cells(c.row,"d"),cells(c.row,"z")).copy
>>
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> (E-Mail Removed)
>> "CROD" <(E-Mail Removed)> wrote in message
>> news:B9165EF2-BDF0-457D-B39C-(E-Mail Removed)...
>> >I am looking to copy only a segement of a row from a primary database to
>> >a
>> > summary sheet. Currently, my script below copies the EntireRow
>> > (c.EntireRow.Copy_). Is there a way to set a range such that I only
>> > copy
>> > over...say "d to Z" versus the EntireRow?
>> >
>> > Sub Rectangle2_Click()
>> >
>> > CodeName = InputBox("Enter Project Code", Range("a1:a3").Find(Cells(1,
>> > 1)).Offset(rowOffset:=2, columnOffset:=1).Value)
>> >
>> > Range("a1:a5").Find(Cells(1, 1)).Offset(rowOffset:=1,
>> > columnOffset:=2).Value
>> > = CodeName
>> >
>> > Worksheets("Extract").Select
>> > Worksheets("Extract").Range("a8:z2000").ClearContents
>> >
>> > RowCount = 8
>> >
>> > With Worksheets("Database").Range("a1:z2000")
>> > Set c = .Find(Cells(3, 2))
>> >
>> > If Not c Is Nothing Then
>> > firstAddress = c.Address
>> > Do
>> >
>> > c.EntireRow.Copy _
>> > Destination:=Worksheets("APTS Extract").Rows(RowCount)
>> > RowCount = RowCount + 1
>> >
>> > Set c = .FindNext(c)
>> >
>> > Loop While Not c Is Nothing And c.Address <> firstAddress
>> > End If
>> >
>> > End With
>> >
>> > End Sub
>>
>>
 
Reply With Quote
 
CROD
Guest
Posts: n/a
 
      13th Nov 2008
Don,

Thanks!!.....it runs great (I had placed dots in several locations but could
not get the correct order).

Thanks again for all your help!,

Chris

"Don Guillett" wrote:

> Sorry, I forgot to include the dot for the WITH
>
> .Range(cells(c.row,"d"),cells(c.row,"z")).copy
> or
> .Range(.cells(c.row,"d"),.cells(c.row,"z")).copy
>
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "CROD" <(E-Mail Removed)> wrote in message
> news:E757CF36-1FE9-4A05-A6D8-(E-Mail Removed)...
> > Don,
> >
> > Thanks for the response!
> >
> > I've replace "c.EntireRow" with your script
> > "Range(cells(c.row,"d"),cells(c.row,"z")).copy and get a "Run-time
> > error'1004". Your further assistance is greatly appreciated!
> >
> > "Don Guillett" wrote:
> >
> >> Range(cells(c.row,"d"),cells(c.row,"z")).copy
> >>
> >> --
> >> Don Guillett
> >> Microsoft MVP Excel
> >> SalesAid Software
> >> (E-Mail Removed)
> >> "CROD" <(E-Mail Removed)> wrote in message
> >> news:B9165EF2-BDF0-457D-B39C-(E-Mail Removed)...
> >> >I am looking to copy only a segement of a row from a primary database to
> >> >a
> >> > summary sheet. Currently, my script below copies the EntireRow
> >> > (c.EntireRow.Copy_). Is there a way to set a range such that I only
> >> > copy
> >> > over...say "d to Z" versus the EntireRow?
> >> >
> >> > Sub Rectangle2_Click()
> >> >
> >> > CodeName = InputBox("Enter Project Code", Range("a1:a3").Find(Cells(1,
> >> > 1)).Offset(rowOffset:=2, columnOffset:=1).Value)
> >> >
> >> > Range("a1:a5").Find(Cells(1, 1)).Offset(rowOffset:=1,
> >> > columnOffset:=2).Value
> >> > = CodeName
> >> >
> >> > Worksheets("Extract").Select
> >> > Worksheets("Extract").Range("a8:z2000").ClearContents
> >> >
> >> > RowCount = 8
> >> >
> >> > With Worksheets("Database").Range("a1:z2000")
> >> > Set c = .Find(Cells(3, 2))
> >> >
> >> > If Not c Is Nothing Then
> >> > firstAddress = c.Address
> >> > Do
> >> >
> >> > c.EntireRow.Copy _
> >> > Destination:=Worksheets("APTS Extract").Rows(RowCount)
> >> > RowCount = RowCount + 1
> >> >
> >> > Set c = .FindNext(c)
> >> >
> >> > Loop While Not c Is Nothing And c.Address <> firstAddress
> >> > End If
> >> >
> >> > End With
> >> >
> >> > End Sub
> >>
> >>
>
>
 
Reply With Quote
 
Don Guillett
Guest
Posts: n/a
 
      14th Nov 2008
Glad to help

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"CROD" <(E-Mail Removed)> wrote in message
news:3E011484-FDB8-4361-9D9B-(E-Mail Removed)...
> Don,
>
> Thanks!!.....it runs great (I had placed dots in several locations but
> could
> not get the correct order).
>
> Thanks again for all your help!,
>
> Chris
>
> "Don Guillett" wrote:
>
>> Sorry, I forgot to include the dot for the WITH
>>
>> .Range(cells(c.row,"d"),cells(c.row,"z")).copy
>> or
>> .Range(.cells(c.row,"d"),.cells(c.row,"z")).copy
>>
>>
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> (E-Mail Removed)
>> "CROD" <(E-Mail Removed)> wrote in message
>> news:E757CF36-1FE9-4A05-A6D8-(E-Mail Removed)...
>> > Don,
>> >
>> > Thanks for the response!
>> >
>> > I've replace "c.EntireRow" with your script
>> > "Range(cells(c.row,"d"),cells(c.row,"z")).copy and get a "Run-time
>> > error'1004". Your further assistance is greatly appreciated!
>> >
>> > "Don Guillett" wrote:
>> >
>> >> Range(cells(c.row,"d"),cells(c.row,"z")).copy
>> >>
>> >> --
>> >> Don Guillett
>> >> Microsoft MVP Excel
>> >> SalesAid Software
>> >> (E-Mail Removed)
>> >> "CROD" <(E-Mail Removed)> wrote in message
>> >> news:B9165EF2-BDF0-457D-B39C-(E-Mail Removed)...
>> >> >I am looking to copy only a segement of a row from a primary database
>> >> >to
>> >> >a
>> >> > summary sheet. Currently, my script below copies the EntireRow
>> >> > (c.EntireRow.Copy_). Is there a way to set a range such that I only
>> >> > copy
>> >> > over...say "d to Z" versus the EntireRow?
>> >> >
>> >> > Sub Rectangle2_Click()
>> >> >
>> >> > CodeName = InputBox("Enter Project Code",
>> >> > Range("a1:a3").Find(Cells(1,
>> >> > 1)).Offset(rowOffset:=2, columnOffset:=1).Value)
>> >> >
>> >> > Range("a1:a5").Find(Cells(1, 1)).Offset(rowOffset:=1,
>> >> > columnOffset:=2).Value
>> >> > = CodeName
>> >> >
>> >> > Worksheets("Extract").Select
>> >> > Worksheets("Extract").Range("a8:z2000").ClearContents
>> >> >
>> >> > RowCount = 8
>> >> >
>> >> > With Worksheets("Database").Range("a1:z2000")
>> >> > Set c = .Find(Cells(3, 2))
>> >> >
>> >> > If Not c Is Nothing Then
>> >> > firstAddress = c.Address
>> >> > Do
>> >> >
>> >> > c.EntireRow.Copy _
>> >> > Destination:=Worksheets("APTS Extract").Rows(RowCount)
>> >> > RowCount = RowCount + 1
>> >> >
>> >> > Set c = .FindNext(c)
>> >> >
>> >> > Loop While Not c Is Nothing And c.Address <> firstAddress
>> >> > End If
>> >> >
>> >> > End With
>> >> >
>> >> > End Sub
>> >>
>> >>
>>
>>
 
Reply With Quote
 
 
 
Reply

« Trapping errors. | how can i find ' as the first characture in a cell using vba »
Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
MACRO: Selecting "Cancel" from a drop down to copy and paste entirerow to another sheet Nicole Hannington Microsoft Excel Misc 1 20th Jul 2009 07:11 PM
MACRO: Selecting "Cancel" from a drop down to copy and paste entirerow to another sheet Nicole Hannington Microsoft Excel Worksheet Functions 1 20th Jul 2009 06:51 PM
find specific data in row and select and copy entirerow =?Utf-8?B?SnVuaW9yNzI4?= Microsoft Excel Programming 3 8th Aug 2005 01:31 PM
Copy & Paste Object without using the Excel Copy Paste functions =?Utf-8?B?R2Fueg==?= Microsoft Excel New Users 0 10th Mar 2004 07:06 AM
Copy & Paste Object without using the Excel Copy Paste functions =?Utf-8?B?R2Fueg==?= Microsoft Excel Misc 0 10th Mar 2004 07:06 AM


Features
Forums Startup Database Reviews Articles FAQ / Guidelines About Us
 

Advertising
 

Newsgroups
Windows Vista Windows XP Microsoft Word Microsoft DotNet Windows 2000 AntiSpyware Microsoft Access Microsoft Excel Microsoft Outlook Hardware
 


All times are GMT +1. The time now is 05:15 AM.
Powered by vBulletin®. Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2010, Crawlability, Inc.
Contact Us - Archive - Privacy Statement - Top