Posts by Maqbool

Dim strDate As String

'paste below code after "IF sheets("Mandatory").Cells("i,1)...................................... Then"

With Sheets("Mandatory") 
strDate = Me.Textbox4.Value
If IsNumber(strDate) Then  'check the value is in textbox 
.Cells(i, 5).Value = strDate 
Else 
MsgBox "Invalid Date."
 End If
End With

Sub SumSheets()


Sheets("Summary").Range("A2:A1000").ClearContents  ' clear everything in summary before pasting


Dim sh As Worksheet
    For Each sh In ThisWorkbook.Sheets
        If sh.Name <> "Summary" And sh.Name <> "Sheet1" And sh.Name <> "Sheet2" Then
        
            sh.Range("F17:F66").Copy
            Sheets("Summary").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlValues
        End If
    Next


End Sub

Sub Test()
Dim wbSls As Workbook
Dim wbMnthly As String
Dim wsSls As Worksheet
Dim x As Long
Dim strDir As String
Dim lstRw As Long
Dim strLkpArra As String




Set wbSls = ThisWorkbook
strPath = "'C:\Test\["  ' change accordingly
strSht = "Master Data'!R7C2:R"


With wbSls
    Set wsSls = .Sheets("Sales")
End With


n = 11


With wsSls
    Do Until n = lstRw
        lstRwInClsd = .Range("D4").Value + 6
        x = .Range("D8").End(xlToRight).Column
        lstRw = .Rows.Count
        lstRw = .Range("D" & lstRw).End(xlUp).Offset(0, -1).Row
   
        For x = 4 To x
            strfile = .Cells(8, x)
            strLkpArray = strPath & strfile & "]" & strSht
            'levae row 1 empty on consolidated workbook - this helper row to get the last row in closed workbook
            .Cells(1, x).Value = "=COUNTA('" & strPath & strfile & "]Master Data'!$B$8:$B$10000)"
            lstRwInClsd = .Cells(1, x).Value + 6 '(6 rows blank on the monthly workbook - may need to adjust accordingly)
            .Cells(n, x).Value = "=VLOOKUP(RC2," & "'" & strLkpArray & lstRwInClsd & "C11,10,FALSE)"
            .Cells(1, x).Value = ""
        Next
        n = n + 1
    Loop
End With


End Sub

Sub Test()
Dim wbSls As Workbook
Dim wbMnthly As String
Dim wsSls As Worksheet
Dim x As Long
Dim strDir As String
Dim lstRw As Long
Dim rngFmula As Range


Set wbSls = ThisWorkbook
strpath = "'C:\Test\["  ' change accordingly
strSht = "Master Data'!$B$7:$K$33,10,FALSE)"


With wbSls
    
    Set wsSls = .Sheets("Sales")
  
End With
n = 11


With wsSls
    Do Until n = lstRw
        x = .Range("D8").End(xlToRight).Column
        lstRw = .Rows.Count
        lstRw = .Range("D" & lstRw).End(xlUp).Offset(0, -1).Row
   
        For x = 4 To x
            strfile = .Cells(8, x)
            strlkparray = strpath & strfile & "]" & strSht
           .Cells(n, x).Value = "=VLOOKUP($B11," & strlkparray
            
        Next
        n = n + 1
    Loop
End With


End Sub

Sub Test()
Dim wbSls As Workbook
Dim wbMnthly As String
Dim wsSls As Worksheet
Dim x As Long
Dim strDir As String


Set wbSls = ThisWorkbook
strpath = "'C:\Test\["  ' change accordingly
strSht = "Master Data'!$B$7:$K$33,10,FALSE)"


With wbSls
    Set wsSls = .Sheets("Sales")
End With


With wsSls
    x = .Range("D8").End(xlToRight).Column
    
    For x = 4 To x
        strfile = .Cells(8, x)
        strlkparray = strpath & strfile & "]" & strSht
        Debug.Print strlkparray
        .Cells(11, x).Value = "=VLOOKUP($B11," & strlkparray
    Next
End With


End Sub

Sub Test()
Dim wsOne As Worksheet
Dim rngOne As Range
Dim testOne As String
Dim testTwo As String


Set wsOne = ThisWorkbook.Sheets("Sheet1")


With wsOne
    x = .Rows.Count
    Set rngOne = .Range("F2", .Range("F" & x).End(xlUp))
End With


With rngOne
    n = rngOne.Count
    For x = n To 1 Step -1
        testOne = .Cells(x, 1).Value
        testTwo = .Cells(x, -1).Value
        Debug.Print testOne
        If testOne = "" And testTwo = "AFC" Then
            .Cells(x, -1).Font.Bold = True  'this will highlight the cell which is going to be deleted
            '.Cells(x, -1).EntireRow.Delete 'to delete entire row - enable this part, if everything works as expected
        End If
    Next
End With




End Sub

Set Rng = ActiveSheet.Range("E11:E20")


With Rng
    For Each c In Rng
        curid = c.Value
        strAddress = strAddress & ";" & curid
    Next


End With


 strAddress = Mid(strAddress, 2, Len(strAddress))


 
With oMail
    .To = strAddress


End With