July 20, 2020
Sub Tebalkan()
Dim ws As Worksheet
Dim TEBAL As Worksheet
Dim BAPK As Worksheet
Set TEBAL = Sheet34
Set BAPK = Sheet20
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
BAPK.Range("F19").UnMerge
TEBAL.Range("C19").Copy
With BAPK.Range("F19")
.PasteSpecial xlPasteValues
.Characters(WorksheetFunction.Find(TEBAL.Range("C4"), BAPK.Range("F19").Value, 1), Len(Range("C4"))).Font.Bold = True
.Characters(WorksheetFunction.Find(TEBAL.Range("F4"), BAPK.Range("F19").Value, 1), Len(Range("F4"))).Font.Bold = True
.Characters(WorksheetFunction.Find(TEBAL.Range("D4"), BAPK.Range("F19").Value, 1), Len(Range("D4"))).Font.Bold = True
.Characters(WorksheetFunction.Find(TEBAL.Range("G4"), BAPK.Range("F19").Value, 1), Len(Range("G4"))).Font.Bold = True
End With
BAPK.Range("F19:K19").Merge
With BAPK.Range("F19")
.WrapText = True
.HorizontalAlignment = xlJustify
.VerticalAlignment = xlCenter
End With
Next ws
End Sub
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
July 20, 2020
cant make bold ,,, i want the result like this : Pada hari ini Sabtu Tanggal Dua Puluh Tiga Bulan Januari Tahun Dua Ribu Dua Puluh Satu Yang bertandatangan di bawah ini, kami Kepala Sekolah yang ditunjuk berdasarkan Surat Keputusan Nomor : 800/124/9/2020 Tanggal 12 Februari 2002 ,,,
can you help please ,,
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
The problem is in LEN functions, you have references not properly qualified:
Len(Range("C4"))
Should be:
Len(TEBAL.Range("C4"))
If you don't include the worksheet name, will read from the active sheet, not from where you expect. Range("C4") refers to any active sheet, it's a relative reference.
Always use fully qualified references.
Also why do you use a loop when you ALWAYS process the BAPK sheet?
For Each ws In ActiveWorkbook.Worksheets
BAPK.Range("F19").UnMerge
TEBAL.Range("C19").Copy
With BAPK.Range("F19")
...rest of code
The For-Next loop is useless, if you have 10 sheets, BAPK.Range("F19") will be processed 10 times.
Answers Post
July 20, 2020
hi catalin ,
thanks this works ...
is there any solution or other way besides using loop ?
Can you give me an example of another way to make bold words from one sheet to many sheets ? please,,,
because of the plan, sheets "Tebal" will be used as a source of thickening words to many sheets other than "bapk "sheets,,
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
In this case, you can use a loop, but change the processed sheet:
For Each ws In ActiveWorkbook.Worksheets
ws.Range("F19").UnMerge
TEBAL.Range("C19").Copy
With ws.Range("F19")
But, you will have to read data from a different TEBAL row for each sheet, not always from:
Len(TEBAL.Range("C4"))
You need to check sheets before processing, what if you're processing TEBAL? Some sheets might need to be excluded from the loop.
1 Guest(s)