Splitting up information into separate tabs

[Mellzer]

[FONT=&quot]I have this macro that copies several files into a master spreadsheet. All of the files names remain the same except one, the date constantly changes day to day. How can i do this? Here is one example of my code.

Workbooks.Open Filename:= _

"\\barracuda\sls_depts\DEPTS\SRG\Logistics\INVENTORY\CSG HALLMARK MASTER INVENTORY\AISLES\AISLE 7 JEWELRY CABINET.xlsx"

Range("B2:J3000").Select
Selection.Copy
Windows("OHCompare.xlsx").Activate
ActiveSheet.Paste
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Windows("AISLE 7 JEWELRY CABINET.xlsx").Activate
ActiveWindow.Close


This is the location and file name of the file, the name always remains the same except for the date. If its updated several times a day it has the same date but -2 or -3 for the number of times its been updated. Ex (8-3-18-2)

"\\barracuda\SLS_Depts\DEPTS\<wbr>SRG\Logistics\INVENTORY\9991 & 9998 DC ON HAND FOR 8-3-18.xlsx"[/FONT][FONT=&quot]

[/FONT]

 

[Mellzer]

So the problem im having with this is that once the file opens, i need it to copy starting at cell a2 to M, to the bottom of the page (which changes) and paste into a workbook(OHCompare) i already have open on tab2 (OnHand) at the next available space. And then close the workbook that i copied from.

Currently it is copying over the current data already in the tab instead of at the next available space at the bottom.
The workbook to be copied into is OHCompare and the TAB is called OnHand.

 

[Joe4]

Can you post the second of your code that does the copy/paste?
Actually, it may be better to post ALL the code, to make sure it is interacting with the rest of the code properly too.

 

[Mellzer]

Here is my whole code, the code in previous threads are not included but should come at the end.
I appologize, i didnt know how to put the code in the nice box like you did.



Sub Aisles()
'
' Aisles Macro
'

'
Application.DisplayAlerts = False
Workbooks.Open Filename:= _
"\\barracuda\sls_depts\DEPTS\SRG\Logistics\INVENTORY\CSG HALLMARK MASTER INVENTORY\AISLES\AISLE 7 JEWELRY CABINET.xlsx"
Range("B2:J3000").Select
Selection.Copy
Windows("OHCompare.xlsx").Activate
ActiveSheet.Paste
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Windows("AISLE 7 JEWELRY CABINET.xlsx").Activate
ActiveWindow.Close
Workbooks.Open Filename:= _
"\\barracuda\sls_depts\DEPTS\SRG\Logistics\INVENTORY\CSG HALLMARK MASTER INVENTORY\AISLES\Food Room Book 2.0.xlsx"
Range("A2:J3000").Select
Selection.Copy
Windows("OHCompare.xlsx").Activate
ActiveSheet.Paste
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Windows("Food Room Book 2.0.xlsx").Activate
ActiveWindow.Close
Workbooks.Open Filename:= _
"\\barracuda\sls_depts\DEPTS\SRG\Logistics\INVENTORY\CSG HALLMARK MASTER INVENTORY\AISLES\AISLE 6 SEASONAL & J356 MUGS.xlsx"
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Range("A2:J3000").Select
Selection.Copy
Windows("OHCompare.xlsx").Activate
ActiveSheet.Paste
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Windows("AISLE 6 SEASONAL & J356 MUGS.xlsx").Activate
ActiveWindow.Close
Workbooks.Open Filename:= _
"\\barracuda\sls_depts\DEPTS\SRG\Logistics\INVENTORY\CSG HALLMARK MASTER INVENTORY\AISLES\AISLE 7 CAGE.xlsx"
Range("A2:J3000").Select
Selection.Copy
Windows("OHCompare.xlsx").Activate
ActiveSheet.Paste
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Windows("AISLE 7 CAGE.xlsx").Activate
ActiveWindow.Close
Workbooks.Open Filename:= _
"\\barracuda\sls_depts\DEPTS\SRG\Logistics\INVENTORY\CSG HALLMARK MASTER INVENTORY\AISLES\AISLE 7 NON-CONVEYABLE.xlsx"
Range("A2:J3000").Select
Selection.Copy
Windows("OHCompare.xlsx").Activate
ActiveSheet.Paste
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Windows("AISLE 7 NON-CONVEYABLE.xlsx").Activate
ActiveWindow.Close
Workbooks.Open Filename:= _
"\\barracuda\sls_depts\DEPTS\SRG\Logistics\INVENTORY\CSG HALLMARK MASTER INVENTORY\AISLES\AISLE 8 CUBE AREA.xlsx"
Range("A2:J3000").Select
Selection.Copy
Windows("OHCompare.xlsx").Activate
ActiveSheet.Paste
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Windows("AISLE 8 CUBE AREA.xlsx").Activate
ActiveWindow.Close
Workbooks.Open Filename:= _
"\\barracuda\sls_depts\DEPTS\SRG\Logistics\INVENTORY\CSG HALLMARK MASTER INVENTORY\AISLES\AISLE 8 FRAGRANCE RACKS.xlsx"
Range("A2:J3000").Select
Selection.Copy
Windows("OHCompare.xlsx").Activate
ActiveSheet.Paste
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Windows("AISLE 8 FRAGRANCE RACKS.xlsx").Activate
ActiveWindow.Close
Workbooks.Open Filename:= _
"\\barracuda\sls_depts\DEPTS\SRG\Logistics\INVENTORY\CSG HALLMARK MASTER INVENTORY\AISLES\AISLE 9.xlsx"
Range("A2:J3000").Select
Selection.Copy
Windows("OHCompare.xlsx").Activate
ActiveSheet.Paste
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Windows("AISLE 9.xlsx").Activate
ActiveWindow.Close
Workbooks.Open Filename:= _
"\\barracuda\sls_depts\DEPTS\SRG\Logistics\INVENTORY\CSG HALLMARK MASTER INVENTORY\AISLES\AISLE 10.xlsx"
Range("A2:J3000").Select
Selection.Copy
Windows("OHCompare.xlsx").Activate
ActiveSheet.Paste
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Windows("AISLE 10.xlsx").Activate
ActiveWindow.Close
Workbooks.Open Filename:= _
"\\barracuda\sls_depts\DEPTS\SRG\Logistics\INVENTORY\CSG HALLMARK MASTER INVENTORY\AISLES\AISLE 11.xlsx"
Range("A2:J3000").Select
Selection.Copy
Windows("OHCompare.xlsx").Activate
ActiveSheet.Paste
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Windows("AISLE 11.xlsx").Activate
ActiveWindow.Close
Workbooks.Open Filename:= _
"\\barracuda\sls_depts\DEPTS\SRG\Logistics\INVENTORY\CSG HALLMARK MASTER INVENTORY\AISLES\AISLE 12 FRAGRANCE.xlsx"
Range("A2:J3000").Select
Selection.Copy
Windows("OHCompare.xlsx").Activate
ActiveSheet.Paste
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Windows("AISLE 12 FRAGRANCE.xlsx").Activate
ActiveWindow.Close
Workbooks.Open Filename:= _
"\\barracuda\sls_depts\DEPTS\SRG\Logistics\INVENTORY\CSG HALLMARK MASTER INVENTORY\AISLES\AISLE 14.xlsx"
Range("A2:J3000").Select
Selection.Copy
Windows("OHCompare.xlsx").Activate
ActiveSheet.Paste
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Windows("AISLE 14.xlsx").Activate
ActiveWindow.Close
Workbooks.Open Filename:= _
"\\barracuda\sls_depts\DEPTS\SRG\Logistics\INVENTORY\CSG HALLMARK MASTER INVENTORY\AISLES\AISLE 20.xlsx"
Range("A2:J3000").Select
Selection.Copy
Windows("OHCompare.xlsx").Activate
ActiveSheet.Paste
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Windows("AISLE 20.xlsx").Activate
ActiveWindow.Close
Range("A1:G799").Select
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("Master Sheet").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Master Sheet").Sort.SortFields.Add Key:=Range( _
"C2:C798"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Master Sheet").Sort
.SetRange Range("A1:J40000")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
Application.ScreenUpdating = False
Columns("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Application.ScreenUpdating = True
Sheets("Master Sheet").Select
Sheets("Master Sheet").Copy After:=Sheets(1)
Sheets("Master Sheet (2)").Select
Sheets("Master Sheet (2)").Name = "OnHand"
Sheets("Master Sheet").Select
Columns("H:J").Select
Selection.Delete Shift:=xlToLeft
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Range("A1:F7156").Select
ActiveWorkbook.Worksheets("Master Sheet").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Master Sheet").Sort.SortFields.Add Key:=Range( _
"A2:A7156"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Master Sheet").Sort
.SetRange Range("A1:F7156")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("B:D").Select
Selection.FormatConditions.AddUniqueValues
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).DupeUnique = xlDuplicate
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("A2").Select
Sheets("OnHand").Select
Range("A2").Select
End With
End Sub

 

[Joe4]

I appologize, i didnt know how to put the code in the nice box like you did.

At the end of the menu bar in the editor, there is a "code tags" button that looks like a hashtag.
If you press that button, you will get two HTML Code tags, surrounded by square brackets. Place the code between those brackets.
- or -
Paste your code, highlight the whole block of code, and then press the Code tags button.
- or -
you can do it these ways: https://www.mrexcel.com/forum/board-announcements/515787-how-post-your-vba-code.html

So, which section of your code is the one that is not working properly? Can you highlight that in red font?