Copy Sheets to Master, from multiple workbooks, using special paste

JasonH001

New Member
Joined
Feb 12, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • billingsummary.png
    billingsummary.png
    32.6 KB · Views: 8
  • MasterSheet.png
    MasterSheet.png
    37.7 KB · Views: 9

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Change this
VBA Code:
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


To this

VBA Code:
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).PasteSpecial xlPasteValuesAndNumberFormats
wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).PasteSpecial xlPasteFormats
End With
 
Upvote 0
That is perfect. Thank you. Any chance we can add in something to close the second file or is that not possible?
 
Upvote 0
VBA Code:
wkbSource.Close False

Atter the 'End With' and before the 'End Sub'
 
Upvote 0
Solution

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top