Hi,
I am trying to streamline some things at work and would appreciate some help. I have done quite a bit of searching and found something that almost does what I'm looking for, but needs to be tweaked just a little. I tried to find the solution before posting here, but I cant quite figure it out. The current code I have is this:
Sub Copy_Sheets_To_Master()
Application.ScreenUpdating = False
Dim flder As FileDialog, FileName As String, FileChosen As Integer, wkbSource As Workbook, wsDest As Worksheet, ws As Worksheet, lRow As Long
Set wsDest = ThisWorkbook.Sheets("Master")
Set flder = Application.FileDialog(msoFileDialogFilePicker)
flder.Title = "Please Select a folder and file."
FileChosen = flder.Show
FileName = flder.SelectedItems(1)
Set wkbSource = Workbooks.Open(FileName)
For Each ws In Sheets(Array("BILLINGSUMMARY"))
With ws
lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
.Cells(2, 1).Resize(lRow - 1, 7).Copy wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1)
End With
Next ws
Application.ScreenUpdating = True
End Sub
The current/open workbook will pull data from the "BILLINGSUMMARY" sheet in the file chosen by the user and paste it in the "Master" sheet on the current/open workbook. The thing I want to change is the "copy" function. I don't want the functions that are in the cells. My preference would be if I could paste values and formatting only. If that isn't possible, then values only. Also if it could close the file chosen and only leave the master sheet up that would be helpful.
Thank you in advance
I am trying to streamline some things at work and would appreciate some help. I have done quite a bit of searching and found something that almost does what I'm looking for, but needs to be tweaked just a little. I tried to find the solution before posting here, but I cant quite figure it out. The current code I have is this:
Sub Copy_Sheets_To_Master()
Application.ScreenUpdating = False
Dim flder As FileDialog, FileName As String, FileChosen As Integer, wkbSource As Workbook, wsDest As Worksheet, ws As Worksheet, lRow As Long
Set wsDest = ThisWorkbook.Sheets("Master")
Set flder = Application.FileDialog(msoFileDialogFilePicker)
flder.Title = "Please Select a folder and file."
FileChosen = flder.Show
FileName = flder.SelectedItems(1)
Set wkbSource = Workbooks.Open(FileName)
For Each ws In Sheets(Array("BILLINGSUMMARY"))
With ws
lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
.Cells(2, 1).Resize(lRow - 1, 7).Copy wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1)
End With
Next ws
Application.ScreenUpdating = True
End Sub
The current/open workbook will pull data from the "BILLINGSUMMARY" sheet in the file chosen by the user and paste it in the "Master" sheet on the current/open workbook. The thing I want to change is the "copy" function. I don't want the functions that are in the cells. My preference would be if I could paste values and formatting only. If that isn't possible, then values only. Also if it could close the file chosen and only leave the master sheet up that would be helpful.
Thank you in advance