OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Calc: macro to copy rows

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
jkimmel
General User
General User


Joined: 17 Aug 2011
Posts: 6

PostPosted: Wed Aug 17, 2011 11:54 pm    Post subject: Calc: macro to copy rows Reply with quote

I'd like to:

Copy first row of actual sheet named Tabelle1 to A1 of sheet named Tabelle2 then
copy second row of actual sheet named Tabelle1 to BV1 of sheet named Tabelle2 then
copy third row of actual sheet named Tabelle1 to A2 of sheet named Tabelle2 then
copy fourth row of actual sheet named Tabelle1 to BV2 of sheet named Tabelle2
and so on until the first row to be copied is empty

Edited, trying to make my intention more clear.



Moderation probe1: moved to MACROS AND API section, where all macro related questions belong to; edited subject
Back to top
View user's profile Send private message
jkimmel
General User
General User


Joined: 17 Aug 2011
Posts: 6

PostPosted: Sat Aug 20, 2011 2:49 am    Post subject: Reply with quote

No answer?

Maybe I'm still not clear enough about my intentions?
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sat Aug 20, 2011 3:18 am    Post subject: Reply with quote

If you can not do even this most simple task, then I assume that you don't have any question. Actually you want others to do your work, so you don't need to learn even the most fundamental basics about macro programming.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
jkimmel
General User
General User


Joined: 17 Aug 2011
Posts: 6

PostPosted: Sat Aug 20, 2011 7:15 am    Post subject: Reply with quote

Villeroy wrote:
If you can not do even this most simple task, then I assume that you don't have any question. Actually you want others to do your work, so you don't need to learn even the most fundamental basics about macro programming.



Yes you're quite right.So I only know there are the possibilties of macros but it won't make much sense to me to learn to program them. The reason is I'm so old there will be not so much time left to use them.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sat Aug 20, 2011 7:37 am    Post subject: Reply with quote

Others are in a hurry because of artificial dead lines. Most are plain lazy or dumb.
About 1 or 2 dozends of regular contributors to this forum have written many thousands lines of macro code for others and for free. The same stupid basics over and over again. Do you really believe that our life times are less precious than yours? We are no human macro recorders!
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
jkimmel
General User
General User


Joined: 17 Aug 2011
Posts: 6

PostPosted: Sun Aug 21, 2011 2:09 am    Post subject: Reply with quote

I learned the lesson.

So I tried my best and that's what came out:
Code:

Sub zeilen_zusammenfassen()
Dim i As Long, lngCount As Long
Dim wksQ As Worksheet, wksZ As Worksheet
Set wksQ = ThisComponent.Sheets("Tabelle 1")    'Tabellennamen anpassen
Set wksZ = ThisComponent.Sheets("Tabelle 2")    'Tabellennamen anpassen
lngCount = 1    'Startzeile in Zieltabelle
For i = 1 To wksQ.Used
Dim oSheet as Object
oSheet = ThisComponent.CurrentController.ActiveSheet
oSheet.getCellRangeByName($1).Rows.Count Step 2
    wksQ.Dim oSheet as Object
oSheet = ThisComponent.CurrentController.ActiveSheet
oSheet.getCellRangeByName($1)(wksQ.Cells(i, 1), wksQ.Cells(i, 26)).Copy wksZ.Cells(lngCount, 1)
    wksQ.Dim oSheet as Object
oSheet = ThisComponent.CurrentController.ActiveSheet
oSheet.getCellRangeByName($1)(wksQ.Cells(i + 1, 1), wksQ.Cells(i + 1, 26)).Copy wksZ.Cells(lngCount, 27)
    lngCount = lngCount + 1
Next i
End Sub



There are still numerous errors which I can't get rid off.

Maybe I showed effort enough that somebody helps me out from here
Back to top
View user's profile Send private message
probe1
Moderator
Moderator


Joined: 18 Aug 2004
Posts: 2560
Location: Chonburi Thailand Asia

PostPosted: Sun Aug 21, 2011 2:34 am    Post subject: Reply with quote

Why you not try to work on your errors?


Unknown datatype "worksheet"
Besides the error message is very clear: you don't have to declare objects


Property or Method not found: Used
In StarBasic use UsedRange. Search in this forum gets you 303 results


oSheet.getCellRangeByName($1)
Method getCellRangeByName expects a proper name, like "A1"
http://api.openoffice.org/docs/common/ref/com/sun/star/table/XCellRange.html#getCellRangeByName
_________________
Cheers
Winfried
My Macros
DateTime2 extension: insert date, time or timestamp, formatted to your needs
Back to top
View user's profile Send private message Visit poster's website
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sun Aug 21, 2011 9:20 am    Post subject: Re: Calc: macro to copy rows Reply with quote

jkimmel wrote:
I'd like to:

Copy first row of actual sheet named Tabelle1 to A1 of sheet named Tabelle2 then
copy second row of actual sheet named Tabelle1 to BV1 of sheet named Tabelle2 then
copy third row of actual sheet named Tabelle1 to A2 of sheet named Tabelle2 then
copy fourth row of actual sheet named Tabelle1 to BV2 of sheet named Tabelle2
and so on until the first row to be copied is empty

You can not copy an entire row of 1024 cells to column BV because the last 74 columns would "fall off" the target sheet.
Let's say your list on Tabelle1 occupies A1:Z9999
AA1 =MOD(ROW();2)
copy down AA1 until AA9999

Select Tabelle1.A1:AA9999
Data>Filter>Standard Filter...
<Column AA> <equals> 1
[More options]
Copy result to: Tabelle2.A1

Select Tabelle1.A1:AA9999
Data>Filter>Standard Filter...
<Column AA> <equals> 0
[More options]
Copy result to: Tabelle2.BV1

Done.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sun Aug 21, 2011 9:27 am    Post subject: Reply with quote

Select Tabelle2.A1:Z1
=INDEX($Tabelle1.$A$1:$Z$9999;2*ROW()-1) [Ctrl+Shift+Enter rather than Enter]
Copy Tabelle2.A1:Z1
Select Tabelle2.A2:Z5000 and paste

Select Tabelle2.BV1:CU1
=INDEX($Tabelle1.$A$1:$Z$9999;2*ROW()) [Ctrl+Shift+Enter rather than Enter]
Copy Tabelle2.BV1:CU1
Select Tabelle2.BV2:CU5000 and paste

Done.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group