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

JasonH001

New Member
Joined
Feb 12, 2021
Messages
6
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: 4
  • MasterSheet.png
    MasterSheet.png
    37.7 KB · Views: 4

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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
 

JasonH001

New Member
Joined
Feb 12, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
That is perfect. Thank you. Any chance we can add in something to close the second file or is that not possible?
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
VBA Code:
wkbSource.Close False

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

Watch MrExcel Video

Forum statistics

Threads
1,127,653
Messages
5,626,105
Members
416,161
Latest member
David1966Lewis

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
Top