VBA to copy paste to specific worksheet from another workbook of same worksheet name

[saleembasha]

Hi Excel Gurus

I have a 'Master.xlsm' file having worksheets like SLA1, SLA2....SLA36. Another file 'Source.xlsx' having SLA1, SLA2...so on but not all 36 sheets. I am looking for a VBA code to copy and paste the data from 'Source' to 'Master' based on same worksheet name. Previous data from A2 in 'Master' have to be delete before pasting the data.

Thank you in advance

Saleem Basha

[The_Greg]

Hello Saleem Basha, the following code will copy whole sheets from Source to Master. You will have to change number 3 to last "SLA" number in Souce. Let me know if something needs to be changed. Also Source file must be open.

Please Login or Register  to view this content.

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[bakerman2]

Please Login or Register  to view this content.

[saleembasha]

Hi Greg

Thank you. I modified the code according to my requirement. If previous data in 'Master' (Col A is date) is same as data in 'Source' (Col A is date), can we skip code for that sheet and continue the rest. For example, I have June data in Master file and now I am copying July data from Source file. If Master file sheet already exits July data, code can skip that sheet and run code rest of sheets.

Highly appreciated your help.

Sub Saleem_Basha()
Application.ScreenUpdating = False
Dim x As Long
Dim MasterWb As String, SourceWb As String
On Error Resume Next
MasterWb = ActiveWorkbook.Name
sFileName = Application.GetOpenFilename
If sFileName = False Then
Exit Sub
Else
Workbooks.Open Filename:=sFileName
End If
arr = Split(sFileName, "\")
SourceWb = arr(UBound(arr))
'SourceWb = Workbooks("Source1.xlsx").Name
For x = 1 To 36 ' CHANGE 3 TO LAST SHEET SLA NUMBER IN SOURCE WORKBOOK

Workbooks(MasterWb).Sheets("SLA" & x).UsedRange.Clear
Workbooks(SourceWb).Sheets("SLA" & x).UsedRange.Copy

ActiveSheet.Paste Destination:=Workbooks(MasterWb).Sheets("SLA" & x).Cells(1, 1)
Application.CutCopyMode = False
Next x

Application.ScreenUpdating = True
MsgBox "All done"

End Sub

[saleembasha]

I ended with the below code... Thank you

Sub CopySameSht()
Dim MasterWb As Workbook, SourceWb As Workbook
Dim sht As Worksheet

Application.ScreenUpdating = False
'On Error Resume Next
MyFileName = Application.GetOpenFilename(Title:="Please Choose Master File", _
FileFilter:="Excel Files *.xls* (*.xls*),")
Set MasterWb = Workbooks.Open(Filename:=MyFileName)

MyFileName1 = Application.GetOpenFilename(Title:="Please Choose Source File", _
FileFilter:="Excel Files *.xls* (*.xls*),")
Set SourceWb = Workbooks.Open(Filename:=MyFileName1)

For Each sht In SourceWb.Worksheets
If SheetExistsInWorkbook(sht.Name, MasterWb) Then
On Error Resume Next
SourceWb.Sheets(sht.Name).ListObjects(1).Unlist
With SourceWb.Sheets(sht.Name).UsedRange
.Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0).Copy
With MasterWb.Sheets(sht.Name).Cells(2, 1)
.PasteSpecial xlPasteFormats
.PasteSpecial xlPasteValues
End With
End With
Application.CutCopyMode = False
End If
Next sht
Application.ScreenUpdating = True
MsgBox "All Done"
End Sub
Function SheetExistsInWorkbook(strShtName As String, wb As Workbook) As Boolean
Dim sht As Worksheet

For Each sht In wb.Sheets
If sht.Name = strShtName Then
SheetExistsInWorkbook = True
Exit Function
End If
Next

SheetExistsInWorkbook = False

End Function