| View previous topic :: View next topic |
| Author |
Message |
jkimmel
General User
Joined: 17 Aug 2011
Posts: 6
|
 Posted: Wed Aug 17, 2011 11:54 pm Post subject: Calc: macro to copy rows |
 |
|
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 |
|
 |
jkimmel
General User
Joined: 17 Aug 2011
Posts: 6
|
| Posted: Sat Aug 20, 2011 2:49 am Post subject: |
|
|
No answer?
Maybe I'm still not clear enough about my intentions? |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Sat Aug 20, 2011 3:18 am Post subject: |
|
|
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 http://forum.openoffice.org |
|
| Back to top |
|
|
jkimmel
General User
Joined: 17 Aug 2011
Posts: 6
|
| Posted: Sat Aug 20, 2011 7:15 am Post subject: |
|
|
| 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 |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Sat Aug 20, 2011 7:37 am Post subject: |
|
|
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 http://forum.openoffice.org |
|
| Back to top |
|
|
jkimmel
General User
Joined: 17 Aug 2011
Posts: 6
|
| Posted: Sun Aug 21, 2011 2:09 am Post subject: |
|
|
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 |
|
|
probe1
Moderator
Joined: 18 Aug 2004
Posts: 2465
Location: Chonburi Thailand Asia
|
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Sun Aug 21, 2011 9:20 am Post subject: Re: Calc: macro to copy rows |
|
|
| 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 http://forum.openoffice.org |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Sun Aug 21, 2011 9:27 am Post subject: |
|
|
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 http://forum.openoffice.org |
|
| Back to top |
|
|
|
