Macro to call another macro in another folder after closing all existing file and copy paste values

Hi All,
Below is the macro that will close existing open excel files and then copy paste values


Public Sub Macro1()
' Call another macro
Call test2
 
    Windows("Book1.xlsm").Activate
    Range("B1:B4").Select
    Selection.Copy
    Workbooks.Open Filename:="C:\Documents and Settings\S*****\My Documents\Macro\Book2.xlsx"
    lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
Range("A" & lMaxRows + 1).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=True
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    ActiveWorkbook.Save
    ActiveWorkbook.Close
   
End Sub
Public Sub test2()
    Dim path As Variant
    Dim excelfile As Variant
    Dim WkbkName As Object
    path = "C:\Documents and Settings\S*****\My Documents\Macro\"
    excelfile = Dir("*.xlsx")
    Do While excelfile <> ""
           
          Application.ScreenUpdating = False

          For Each WkbkName In Application.Workbooks()
'Do not close the existing workbook and the one with the name "Book1.xlsm" but close every other excel file
              If WkbkName.Name <> ThisWorkbook.Name And WkbkName.Name <> "Book1.xlsm" Then WkbkName.Close
          Next

          ' If everything runs all right, exit the sub.
          Exit Sub
    Loop
End Sub

SQL query to get the previous or last month data only

Hi folks,
Find below the query that fetches the previous month data only



Select * from EmpJD
Where evaluations.quality_date >= DATEADD(MONTH, DATEDIFF(MONTH, 31, getdate()), 0)
AND evaluations.quality_date < DATEADD(MONTH, DATEDIFF(MONTH, 0,  getdate()), 0)

Macro to paste values in next available cell or row

Hi All,
I was given a task which required me to copy, transpose and paste data from one worksheet to another. I completed this task in just 5 minutes with the following macro


Sub Macro1()
'
' Macro1 Macro
'

'
    Range("B1:B4").Select
    Selection.Copy
    Workbooks.Open Filename:="C:\Documents and Settings\ShruSoor\My Documents\Macro\Book2.xlsx"
    lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
Range("A" & lMaxRows + 1).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=True
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    ActiveWorkbook.Save
    ActiveWorkbook.Close
   
End Sub

Enjoy!!