[Solved] Fill multiple sheet cells referencing sheet#

Creating a macro - Writing a Script - Using the API

[Solved] Fill multiple sheet cells referencing sheet#

Postby Tango44 » Thu Mar 25, 2021 9:55 pm

Hello

I am trying to make a macro that would automatically fill a sheets cell with data and assign a # value based on the number of the sheet.

Example
I have 20 sheets. I want every sheets A2 cell value to be filled with "=Sheet1.A#" where # would equal that sheets position in the list of sheets. So sheet #2 would get its A2 cell set as "=sheet1.A2".

I would like it to not apply for sheet 1. I also need it to fill the B2 value of every sheet with "=Sheet1.B#" in a similar fashion.
Last edited by Tango44 on Thu Mar 25, 2021 10:30 pm, edited 1 time in total.
OpenOffice 4.1.6 on Windows 10
Tango44
 
Posts: 2
Joined: Thu Mar 25, 2021 9:43 pm

Re: Macro to fill multiple sheet cells referencing sheet#

Postby MrProgrammer » Thu Mar 25, 2021 10:02 pm

Hi, and welcome to the forum.

Tango44 wrote:I want every sheet's A2 cell value to be filled with "=Sheet1.A#" where # would equal that sheets position in the list of sheets.
No need for an evil macro. Select sheets 2 (click) through 20 (shift-click) and enter formula =INDIRECT("Sheet1.A"&SHEET()) once. Takes 30 seconds, tops.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 4037
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: [Solved] Fill multiple sheet cells referencing sheet#

Postby Tango44 » Thu Mar 25, 2021 10:31 pm

Worked like a dream thank you very much!
OpenOffice 4.1.6 on Windows 10
Tango44
 
Posts: 2
Joined: Thu Mar 25, 2021 9:43 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 7 guests