please correct my code
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
Is it something wrong in your code?
yes,,, this my file
What is wrong in your file?
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 ,,
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.
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,,
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.
