Snasui Excel Forum

Code: Select all

Sub Report()
    Dim Book_r As String
    Dim Book_d As String
   

    Dim Sheet_r As String
    Dim Sheet_d As String
    
 

    Book_r = "report.xlsm"
    Book_d = "data.xlsx"

    Sheet_r = "ข้อมูล"
    Sheet_d = "data"


     
    Workbooks(Book_r).Sheets(Sheet_r).Range("a1:c8").ClearContents
    Workbooks(Book_d).Sheets(Sheet_d).Range("a2:c9").Copy
    Workbooks(Book_r).Sheets(Sheet_r).Range("b3").PasteSpecial Paste:=xlPasteValues
    
    Ir = Range("d" & Rows.Count).End(xlUp).Row
    
    For Each r In Workbooks(Book_r).Sheets(Sheet_r).Range("c1:c" & Ir)

         If r = "" Then
        
            r.Value = Range("g2").Value
            
        End If
        
    Next r
        
End Sub

Code: Select all

Ir = Range("d" & Rows.Count).End(xlUp).Row
    
    For Each r In Workbooks(Book_r).Sheets(Sheet_r).Range("c1:c" & Ir)

         If r = "" Then
        
            r.Value = Range("g2").Value
            
        End If
        
    Next r
        
End Sub

Code: Select all

Sub Report()

    Dim Book_r As String, Sheet_r As String
    Dim Book_d As String, Sheet_d As String

    Book_r = "report.xlsm": Sheet_r = "¢éÍÁÙÅ"
    Book_d = "data.xlsx": Sheet_d = "data"
    
    With Workbooks(Book_r).Sheets(Sheet_r)
    
        .Range("a1:c8").ClearContents
        Workbooks(Book_d).Sheets(Sheet_d).Range("a2:c9").Copy
        .Range("b3").PasteSpecial Paste:=xlPasteValues
        
        Ir = .Range("d" & .Rows.Count).End(xlUp).Row
        
        For Each r In .Range("c3:c" & Ir)
             If r = "" Then
                r.Value = .Range("g2").Value
            End If
        Next r
        
    End With
    
End Sub