45188

OpenOffice.org: macro help

Question:

In OOo Calc

I need to copy a column (only <strong>the values</strong>, not the format) from one sheet to another (in the same worksheet) using a macro assigned to a button.

I browsed a bit around but found nothing significant :-(

Answer1:

To answer the original question:

use a data array, which will be significally faster on large ranges of cells

Source = ThisWeek.getCellRangeByName("H12:H206") source_data = Source.getDataArray() Target = Steering.getCellRangeByName("M12:AU206").setDataArray(source_data())

Answer2:

OK, I could build the answer and started learning OOo Basic, which I managed to avoid until now ;-)

I give it as is.

Sub UpdateThisWeek Dim Doc As Object Dim ThisWeek As Object Dim Steering As Object Dim Source As Object Dim Target As Object Dim Week as Integer Doc = ThisComponent ThisWeek = Doc.Sheets.getByName("This week") Steering = Doc.Sheets.getByName("Steering") Week = Steering.getCellByPosition(6,4).Value Source = ThisWeek.getCellRangeByName("H12:H206") Target = Steering.getCellRangeByName("M12:AU206").getCellRangeByPosition(Week-19,0,Week-19,194) Dim i, s For i = 0 To 194 s = Source.getCellByPosition(0, i).Value If s > 0 Then Target.getCellByPosition(0, i).Value = s Else Target.getCellByPosition(0, i).String = "" End If Next i End Sub

Recommend

  • Regex - Compare number with Constant
  • Excel IF function greater than x but less than y
  • Is it possible to reference discrete ranges in google scripts?
  • Bokeh, combination of bar and line chart
  • karplus equation in gnuplot
  • How to store time greater than 24 Hours in Mysql?
  • How to get pixel coordinates from canvas polygon (filled area)
  • Check failed: 1 == NumElements() (1 vs. 1792)Must have a one element tensor in Tensorflow C++
  • How to return the maximum element of a list in Python?
  • How Do I Find Common Dates in Two Ranges
  • update query with multiple values
  • Looking up a value in a range that is between two given values round 2
  • Postgresql regexp_replace
  • Force ggplot legend to show all categories when no values are present [duplicate]
  • Range Multiplication VB.NET (What is wrong with this code?)
  • Representing intervals or ranges? [closed]
  • Binary Tree Traversal Sum Of Each Depth
  • Small video playback
  • Selecting a subset of data in ServiceStack.OrmLite
  • Who propagate bugfixes across branches (corporate development)?
  • xcode don't localize specific strings
  • How can I speed up CURL tasks?
  • Ajax Loaded meta Tags
  • ilmerge with a PFX file
  • Xamarin Forms - UWP Fonts
  • Is my CUDA kernel really runs on device or is being mistekenly executed by host in emulation?
  • What is Eclipse's Declaration View used for?
  • TFS: Get latest causes slow project reloading
  • Arrow is showed instead of the material design version hamburger icon. Why doesn't syncState in
  • ActionScript 2 vs ActionScript 3 performance
  • Rearranging Cells in UITableView Bug & Saving Changes
  • Arrays break string types in Julia
  • Benchmarking RAM performance - UWP and C#
  • Angular 2 constructor injection vs direct access
  • embed rChart in Markdown
  • IndexOutOfRangeException on multidimensional array despite using GetLength check
  • Running Map reduces the dimensions of the matrices
  • How to get NHibernate ISession to cache entity not retrieved by primary key
  • How can I use `wmic` in a Windows PE script?
  • Unable to use reactive element in my shiny app