Office Script power automate

[Dyrock]

I have a long sheet with different criteria which includes a table. each criteria has been entered into a separate sheet with their related tables. what script can I run to be able to copy the entries for each cell from each sheet and paste it to the main sheet according to their rows? 

[Joseph_Fadero]

Hi @Dyrock,

Let me state my understanding of your request and give a possible recommendation. 

You have a Master sheet file, and you have sheets 1 Which contains data you need to populate into the master sheet file, Shame as sheet 2 and 3.

If that's the requirement, You don't necessarily need to run an office script you could use Power Query feature in excel to help you pull in data from a table or range, then do your necessary merge or append to consolidate the information in one file.

Kindly feel free to like my post and Mark as a solution if it meets your requirements to aid others with similar issues

[Dyrock]

I have a Master sheet file, and I have sheets 1 to 6 Which contain data I need to populate into the master sheet file. 

Rows 6 to 99 from sheet one will be copied to rows 6 to 99 in the master sheet.

Rows 100 to 300 from sheet 2 will be copied to rows 100 to 400 in the master sheet, etc.

VB is disabled in my organization.

How can I update the master sheet according to the data that are entered in sheets 2 to 6?

[Dyrock]

Hi @Joseph_Fadero 

Thanks for the hint. I used Power Query and consolidated the information in one file.

This can be an alternative solution for me. 
I would like to be able to keep the format of my master sheet and be able to copy rows from other sheets to the master sheet.

[Dyrock]

with this office script, I can combine tables from other sheets into newly created sheet.
How can I address data in each row from multiple sheets to be pasted into the master sheet?

@SudeepGhatakNZ @SamLed @Nived_Nambiar 

function main(workbook: ExcelScript.Workbook) {
// Delete the "Combined" worksheet, if it's present.
workbook.getWorksheet('Combined')?.delete();

// Create a new worksheet named "Combined" for the combined table.
const newSheet = workbook.addWorksheet('Combined');

// Get the header values for the first table in the workbook.
// This also saves the table list before we add the new, combined table.
const tables = workbook.getTables();
const headerValues = tables[0].getHeaderRowRange().getTexts();
console.log(headerValues);

// Copy the headers on a new worksheet to an equal-sized range.
const targetRange = newSheet.getRange('A1').getResizedRange(headerValues.length-1, headerValues[0].length-1);
targetRange.setValues(headerValues);

// Add the data from each table in the workbook to the new table.
const combinedTable = newSheet.addTable(targetRange.getAddress(), true);
for (let table of tables) {
let dataValues = table.getRangeBetweenHeaderAndTotal().getTexts();
let rowCount = table.getRowCount();

// If the table is not empty, add its rows to the combined table.
if (rowCount > 0) {
combinedTable.addRows(-1, dataValues);
}
}
}

[SudeepGhatakNZ]

I will let @SamLed or @Nived_Nambiar respond. I don't have much expertise with Office scripts.

[Joseph_Fadero]

Hi @Dyrock ,

Good day,

Thank you for your response,

Based on your requirement I can recall Leila Gharani created a Video on Youtube just for that specific use case scenario kindly use the Link: Script to access it.

I believe the steps carried out in the video are easy to replicate,

Kindly feel free to give a thumbs up to my post and mark my post as a solution if it meets your requirements, to aid others in the community

[Nived_Nambiar]

Hi @Dyrock 

May be bit confusing, is fixed order you are copying from each sheet to master sheet ?

Like row 6 to 99 from sheet 1 to master sheet like that ?

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[Dyrock]

Hi @Nived_Nambiar 

Yes, from sheet 1 (rows 6 to 99 ), from sheet 2 (rows 100 to 300), from sheet 3 (rows 301 to 495), etc.

I have it in VB individually for each sheet. wondering how I can convert it to office script :

Sub CopyPasteWriting()
'set variable
Dim wsSource As Worksheet
Dim wsTarget As Worksheet

'insert source and destination sheets
Set wsSource = Worksheets("Writing")
Set wsTarget = Worksheets("AfCL-L&N Precursors")

'copy data from the source
wsSource.Range("F6:Q55").Copy

'Paste data in the destination
Call wsTarget.Range("F195:Q244").PasteSpecial(Paste:=xlPasteValues)

End Sub

Thanks & Regards,

[Nived_Nambiar]

Hi @Dyrock 

One question, does sheet 1 have only 93 values /rows inside it ?

in another way, you want tocopy whatever is present in sheet 1 to destination sheet , and simmilar for other sheets ?

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[Dyrock]

Hi @Nived_Nambiar 

Yes, it has just a fixed number of rows in each sheet.

I like to be able to copy and paste the entered data in each sheet to the master sheet.

Regards,

Dayrak