Regarding Multiple workbooks... Please help!

[SimmonsDeux]

Guys,
I have a workbook called "MasterSchedule" with a worksheet called "Manufacturing roster" that is updated on a normal basis. I have names listed in Column B and shifts (1st 2nd 3rd) listed in Column D. I have another workbook called "1stShift" with a worksheet called "dump" that I need all rows containing names of individuals on 1st shift from "MasterSchedule" to auto update into. The range to search in "MasterSchedule" starts at B7 and ends at the last row with data.

"MasterSchedule" is in C:\Users\Person\Dropbox\MASTERS

"1stShift" is in C:\Users\Person\Dropbox\MASTERS\Attendance

I have been pulling my hair out trying to get this to work..

 

[CharlesChuckieCharles]

Its quite difficult to understand exactly what you want

do you want to gather the data from multiple work book into one or create multiple workbook from one or something else?

 

[SimmonsDeux]

Its quite difficult to understand exactly what you want

do you want to gather the data from multiple work book into one or create multiple workbook from one or something else?

Excellent question, and thank you for your response.

I need the range "B7:Last row with data" from the "Manufacturing roster" sheet in the "MasterSchedule" workbook to populate into the "dump" sheet in the "1stShift" workbook.

I'd also like to filter the list so that only the rows with "1st" in column D are populated.

If more clarification is needed, please let me know. I'm terrible at explaining things...

 

[JLGWhiz]

I am sure you are sitting looking at your worksheets and wondering why others are asking all these questions. It is because we cannot see your worksheets. We need to know, in terms of colums and rows, the specific location of data to be copied or exported on one worksheet and the location it is to be pasted to on the other worksheet. An example would be: Copy the entire row in sheet 1 for each item that has 'X' in column A to the next available row beginning in column A of sheet 2. Or Copy columns B:G on sheet 1 for each row that has 'x' in column A and paste the data in their respective columns on sheet 2 on the same row number as they were taken from sheet 1. With these typy of descriptive narratives, we can write the code to do the job. But if I wrote code based on the current information, it would be nothing more than to copy the sheet used range from one workbook to the second workbook. While that might be what you want, the narrative is not definitive enough to tell for sure.

 

[JLGWhiz]

Here is what I think you want.
This will filter the rows where column D contains "1st", then copy and paste them beginning at the next available row in sheet 2

Sub copyStuff()
Dim wb1 As Workbook, wb2 As Workbook, sh1 As Worksheet, sh2 As Worksheet, lr As Long, lc As Long
Set wb1 = Workbooks("MasterSchedule.xlsx")
Set wb2 = Workbooks("1stShift")
Set sh1 = wb1.Sheets("Manufacturing roster")
Set sh2 = wb2.Sheets("dump")
lr = sh1.Cells(Rows.Count, "B").End(xlUp).Row
lc = sh1.Cells.Find("*", , xlFormulas, xlPart, xlByColumns, xlPrevious).Column
sh1.Range("A6", sh1.Cells(lr, lc)).AutoFilter 4, "1st"
sh1.Range("A7", sh1.Cells(lr, lc)).SpecialCells(xlCellTypeVisible).Copy sh2.Cells(Rows.Count, 1).End(xlUp)(2)
sh1.Range("A6", sh1.Cells(lr, lc)).AutoFilter
End Sub

 

[SimmonsDeux]

Thanks for the code and advice! I'm not the best when it comes to explaining things, and I'll make sure to be more clear in the future. Thanks again!!

 

[JLGWhiz]

Happy to help,
Regards, JLG