Myself and several coworkers format reports from exported data on a biweekly basis. I want to create a macro that I can distribute to everyone in which all they do is run the macro in as little steps as possible on the data they exported to their local machine. I created a macro, but I cannot get it to work because the destination workbook (ThisWorkbook.Activesheet), is my "PERSONAL.XLSB" (which is located in "XLSTART" folder and hosts the macro)and not the workbook that it needs to (which will change with every person, each week). The main problem is that whenever I run the macro, the data from "END_OF_REPORT.xlsx" will not copy and paste because it is trying to paste to "PERSONAL.XLSB".
For example, I am location 2, sally is location 3. We both need to run reports for jan 1-jan 7, so the file (workbook) that will be ran would be "jan1-jan7_1.xlsx" for my location, and "jan1-jan7_2.xlsx" for sally's location, and both will include only "Sheet1".
when I open visual basic, the projects that are opened are "VBAProject (JAN1-JAN7_2.xlsx)", and "VBAProject (PERSONAL.XLSB)" and under that is Module 1, Module 2, Sheet1, ThisWorkbook. Module 1 is for location 1, Module 2 is for location 2. Whenever I try to run the appropriate macro, it will do everything but copy and paste the data from the workbook that I open and close within the macro.
Sub LOCATION_ONE_TRANS()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationAutomatic
Workbooks.Open "\\FILEPATH\END_OF_REPORT.xlsx"
Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim CopyLastRow As Long
Dim DestLastRow As Long
'Replace 1 with appropriate location
Set wsCopy = Workbooks("END_OF_REPORT.xlsx").Worksheets("1")
Set wsDest = ThisWorkbook.ActiveSheet
CopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
DestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row
wsCopy.Range("A1:E" & CopyLastRow).Copy _
wsDest.Range("A" & DestLastRow)
Workbooks("END_OF_REPORT.xlsx").Close SaveChanges:=False
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range _
("A:A"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Cells.Select
With Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
... ... Dim rangeSelection As Range Dim cellCurrent As Range
Set rangeSelection = Application.Selection.Columns(1).Cells
ActiveSheet.ResetAllPageBreaks
For Each cellCurrent In rangeSelection
If (cellCurrent.Row > 1) Then
If (cellCurrent.Value <> cellCurrent.Offset(-1, 0).Value) Then
ActiveSheet.Rows(cellCurrent.Row).PageBreak = _
xlPageBreakManual
End If
End If
Next cellCurrent
Selection.RowHeight = 36.6
Cells.EntireColumn.AutoFit
Application.WindowState = xlMaximized
ActiveWindow.Zoom = 50
ActiveSheet.Range("A1").Select
ActiveWorkbook.Save
End Sub
What should happen is I share the Macro with my coworkers so that they just need to open up the workbook they need to run the macro on, enable macro because they saved this macro to their "XLSTART", and then run the macro. What is actually happening is when anyone runs the macro, the worksheet is formatting but the copy/pasted data is not making it onto the correct workbook (ie, JAN1-JAN7_2.xlsx)
