copy rows programmatically from one worksheet to another

Deodiaus · 09-09-2005, 06:05 PM

Hi,
I have Worksheet1 with lots of data
I want to create Worksheet2 with a subset of this data, where I want to
just specify the rows to copy.
I did this by specifing the cell, and replicating it for all the cells
that I need to, but that is a lot of work??
Any suggestions?
Sorry, but I am a novice at this, and don't know how to find it.

Gary''s Student · 09-09-2005, 06:05 PM

Copying rows is just as easy as copying cells, take row 5 for example

Rows("5:5").Select
Selection.Copy
Sheets("Sheet2").Select
Rows("5:5").Select
ActiveSheet.Paste
--
Gary''s Student

"Deodiaus" wrote:

> Hi,
> I have Worksheet1 with lots of data
> I want to create Worksheet2 with a subset of this data, where I want to
> just specify the rows to copy.
> I did this by specifing the cell, and replicating it for all the cells
> that I need to, but that is a lot of work??
> Any suggestions?
> Sorry, but I am a novice at this, and don't know how to find it.
>
>

Deodiaus · 09-12-2005, 11:05 PM

Ok, I created this, but I get an error at range saying that VB did not
understand the user defined type
-----------------------------------------------------------------
Private Sub Command1_Click()
Dim QtrSS As Object
'Dim wksht As Excel.Worksheets
Dim rng As Range' error here

Set QtrSS = _
GetObject("c:\foo\test.xls")

rng = QtrSS.Worksheets("Sheet1").Range("1:1")

MsgBox "The worksheet " _
& vbCrLf _
& "is named '" _
& "'", vbOKOnly, " see!"

QtrSS.Application.Quit
Set QtrSS = Nothing

End Sub

Deodiaus · 09-16-2005, 12:05 AM

I have this VBA script that worked with just a few selections. I know
made it bigger, and get this error

Run-time error 1004
Method 'Rows' of Object '_Global' failed
Is there a limit on a selection length
--------------------- here is DCIS_IBC.bas file ---------------
Attribute VB_Name = "DCIS_IBC"
Sub DCIS_IBC()
Attribute DCIS_IBC.VB_Description = "Macro recorded 9/13/2005 by
deodiaus"
Attribute DCIS_IBC.VB_ProcData.VB_Invoke_Func = " \n14"
'
' DCIS_IBC Macro
'
' location dir and name of file to save
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\deodiaus\My
Documents\DEODIAUS_80_93_DCIS_IBC.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

' here are the items to select
'>Run-time error 1004
'>Method 'Rows' of Object '_Global' failed

Range("14:14,57:57,80:80,100:100,120:120,123:123,137:137,157:157,164:164,176:176,237:237,273:273,300:300,318:318,341:341,371:371,393:393,402:402,403:403,409:409,423:423,427:427,438:438,443:443,445:445,458:458,459:459,496:496,500:500,508:508,527:527,541:541,563:563,584:584,586:586,601:601,603:603,610:610,620:620,651:651,669:669,691:691,698:698,714:714,729:729,735:735,750:750,755:755,765:765,769:769,814:814,819:819,871:871,886:886,893:893,905:905,909:909,913:913,915:915,917:917,979:979,988:988,992:992,1011:1011,1022:1022,1028:1028,1047:1047,1058:1058,1072:1072,1107:1107,1123:1123,1161:1161,1184:1184,1187:1187,1201:1201,1219:1219,1230:1230,1244:1244,1273:1273,1274:1274,1295:1295,1297:1297,1308:1308,1329:1329,1330:1330,1352:1352,1354:1354,1386:1386,1398:1398,1409:1409,1454:1454,1466:1466,1485:1485,1491:1491,1506:1506,1524:1524,1534:1534")
' is there a limit on the lenght of this string
' this is the row of the last one
Range("A1534").Activate
Selection.Copy
Sheets.Add
Range("A1").Select
ActiveSheet.Paste Link:=True

Sheets(1).Range("1934,1935:1935,1936:1936,1942:1942,1950:1950,1968:1968,1983:1983,1987:1987,2006:2006,2082:2082,2100:2100,2108:2108,2111:2111,2139:2139,2144:2144,2161:2161,2169:2169,2185:2185,2214:2214,2263:2263,2264:2264,2270:2270,2273:2273,2295:2295,2298:2298,2307:2307,2324:2324,2337:2337,2346:2346,2347:2347,2348:2348,2375:2375,2376:2376,2412:2412,2418:2418,2421:2421,2430:2430,2433:2433,2441:2441,2446:2446,2460:2460,2495:2495,2497:2497,2499:2499,2501:2501,2506:2506,2540:2540,2544:2544,2553:2553,2557:2557,2564:2564,2565:2565,2567:2567,2585:2585,2589:2589,2594:2594,2599:2599,2610:2610,2625:2625,2634:2634,2635:2635,2647:2647,2648:2648,2655:2655,2670:2670").Select
Sheets(1).Range("A2670").Activate
Selection.Copy
'Sheets.Add
Sheets(2).Range("A1934").Select
ActiveSheet.Paste Link:=True

' this Sheet must be the newly created one
'Sheets("Sheet1").Select
'Sheets("Sheet1").Name = "DCIS_IBC"
Sheets(1).Select
Sheets(1).Name = "DCIS_IBC"

' move it over to end
Sheets("DCIS_IBC").Select
Sheets("DCIS_IBC").Move After:=Sheets(2)

Application.CutCopyMode = False

ActiveWorkbook.Save

End Sub

Deodiaus · 09-16-2005, 12:05 AM

I changed the bas file to this, but I still get the same error
--------------------------------------------
' here are the items to select
'>Run-time error 1004
'>Method 'Rows' of Object '_Global' failed
Dim theSel0 As String
Dim theSel1 As String
Dim theSel2 As String
theSel0 =
"14:14,57:57,80:80,100:100,120:120,123:123,137:137,157:157,164:164,176:176,237:237,273:273,300:300,318:318,341:341,371:371,393:393,402:402,403:403,409:409,423:423,427:427,438:438,443:443,445:445,458:458,459:459,496:496,500:500,508:508,527:527,541:541,563:563,584:584,586:586,601:601,603:603,610:610,620:620,651:651,669:669,691:691,698:698,714:714,729:729,735:735,750:750,755:755,765:765,769:769,814:814,819:819,871:871,886:886,893:893,905:905,909:909,913:913,915:915,917:917,979:979,988:988,992:992,1011:1011,1022:1022,1028:1028,1047:1047,1058:1058,1072:1072,1107:1107,1123:1123,1161:1161,1184:1184,1187:1187,1201:1201,1219:1219,1230:1230,1244:1244,1273:1273,1274:1274,1295:1295,1297:1297,1308:1308,1329:1329,1330:1330,1352:1352,1354:1354,1386:1386,1398:1398,1409:1409,1454:1454,1466:1466,1485:1485,1491:1491,1506:1506,1524:1524,1534:1534"
theSel1 =
",1543:1543,1585:1585,1593:1593,1607:1607,1634:1634,1714:1714,1723:1723,1797:1797,1800:1800,1808:1808,1824:1824,1872:1872,1884:1884,1894:1894,1901:1901,1921:1921,1930:1930,1934:1934,1935:1935,1936:1936,1942:1942,1950:1950,1968:1968,1983:1983,1987:1987,2006:2006,2082:2082,2100:2100,2108:2108,2111:2111,2139:2139,2144:2144,2161:2161,2169:2169,2185:2185,2214:2214,2263:2263,2264:2264,2270:2270,2273:2273,2295:2295,2298:2298,2307:2307,2324:2324,2337:2337,2346:2346,2347:2347,2348:2348,2375:2375,2376:2376,2412:2412,2418:2418,2421:2421,2430:2430,2433:2433,2441:2441,2446:2446,2460:2460,2495:2495,2497:2497,2499:2499,2501:2501,2506:2506,2540:2540,2544:2544,2553:2553,2557:2557,2564:2564,2565:2565,2567:2567,2585:2585,2589:2589,2594:2594,2599:2599,2610:2610,2625:2625,2634:2634,2635:2635,2647:2647,2648:2648,2655:2655,2670:2670"
theSel2 = theSel0 + theSel1
Range(theSel2).Select
' this is the row of the last one
Range("A2670").Activate
--------------------------------------------
Help

I don't think you can select outside of the displayed page??
Any suggestions on how to scroll the page correctly automatically??

Rowan · 09-16-2005, 01:05 AM

When you refer to a range the string can have no more than 256
characters. Try it like this:

Dim theSel As Range
Set theSel = Union(Rows(14), Rows(57), Rows(80), Rows(100)) 'etc
Set theSel = Union(theSel, Rows(123), Rows(137)) 'etc
theSel.Select

The union command can include 30 arguments at a time. Hence the second
set command where you can join an addition 29 rows to the range you
created in the first set. Continue this until you have all the rows you
need.

Hope this makes sense
Rowan

Deodiaus wrote:
> I changed the bas file to this, but I still get the same error
> --------------------------------------------
> ' here are the items to select
> '>Run-time error 1004
> '>Method 'Rows' of Object '_Global' failed
> Dim theSel0 As String
> Dim theSel1 As String
> Dim theSel2 As String
> theSel0 =
> "14:14,57:57,80:80,100:100,120:120,123:123,137:137,157:157,164:164,176:176,237:237,273:273,300:300,318:318,341:341,371:371,393:393,402:402,403:403,409:409,423:423,427:427,438:438,443:443,445:445,458:458,459:459,496:496,500:500,508:508,527:527,541:541,563:563,584:584,586:586,601:601,603:603,610:610,620:620,651:651,669:669,691:691,698:698,714:714,729:729,735:735,750:750,755:755,765:765,769:769,814:814,819:819,871:871,886:886,893:893,905:905,909:909,913:913,915:915,917:917,979:979,988:988,992:992,1011:1011,1022:1022,1028:1028,1047:1047,1058:1058,1072:1072,1107:1107,1123:1123,1161:1161,1184:1184,1187:1187,1201:1201,1219:1219,1230:1230,1244:1244,1273:1273,1274:1274,1295:1295,1297:1297,1308:1308,1329:1329,1330:1330,1352:1352,1354:1354,1386:1386,1398:1398,1409:1409,1454:1454,1466:1466,1485:1485,1491:1491,1506:1506,1524:1524,1534:1534"
> theSel1 =
> ",1543:1543,1585:1585,1593:1593,1607:1607,1634:1634,1714:1714,1723:1723,1797:1797,1800:1800,1808:1808,1824:1824,1872:1872,1884:1884,1894:1894,1901:1901,1921:1921,1930:1930,1934:1934,1935:1935,1936:1936,1942:1942,1950:1950,1968:1968,1983:1983,1987:1987,2006:2006,2082:2082,2100:2100,2108:2108,2111:2111,2139:2139,2144:2144,2161:2161,2169:2169,2185:2185,2214:2214,2263:2263,2264:2264,2270:2270,2273:2273,2295:2295,2298:2298,2307:2307,2324:2324,2337:2337,2346:2346,2347:2347,2348:2348,2375:2375,2376:2376,2412:2412,2418:2418,2421:2421,2430:2430,2433:2433,2441:2441,2446:2446,2460:2460,2495:2495,2497:2497,2499:2499,2501:2501,2506:2506,2540:2540,2544:2544,2553:2553,2557:2557,2564:2564,2565:2565,2567:2567,2585:2585,2589:2589,2594:2594,2599:2599,2610:2610,2625:2625,2634:2634,2635:2635,2647:2647,2648:2648,2655:2655,2670:2670"
> theSel2 = theSel0 + theSel1
> Range(theSel2).Select
> ' this is the row of the last one
> Range("A2670").Activate
> --------------------------------------------
> Help
>
> I don't think you can select outside of the displayed page??
> Any suggestions on how to scroll the page correctly automatically??
>

Deodiaus · 09-16-2005, 11:05 AM

Wow, you are really up on all of this!
I have been pissing around with all sorts of solutions all night long.
I thought the problem was in the section of an off page item, and
introducing scrolling into my formulas
Thanks a million!