Workbook to workbook Hi, I\'d like to import sheets from my data workbook to Mas

Question

Workbook to workbook

Hi,

I'd like to import sheets from my data workbook to Master workbook.

I made data workbook from Master, so the data workbook has less sheets than Master.

I have to copy this data workbook and then paste to Master workbook.

Both has the same tab name, so I want to copy the same sheet name in data workbook to the same sheet name in Master workbook.

Sub PasteToMasterFromData()
' On Error Resume Next
Dim strWbDataName As Variant   
Dim wbData As Workbook 'Data workbook
Dim wbMast As Workbook 'Master workbook
Dim wsData As Worksheet 'Each Data worksheet
Dim wsMast As Worksheet 'Master worksheets
Dim rngSource As Range 'Range to be copied
  
Set wbMast = ThisWorkbook
Debug.Print wbMast.Name

strWbDataName = Application.GetOpenFilename(FileFilter:= _
"Excel Files (*.xlsm), *.xlsm", Title:="Please select a correct file")

If strWbDataName = False Then
MsgBox "No file."
Exit Sub
Else
Set wbData = Workbooks.Open(Filename:=strWbDataName)
  
  
For Each wsData In wbData.Worksheets
'Debug.Print wsData.Name

If wsMast.Name = wsData.Name Then
wsData.Copy wsMast
End If
Next wsData
End If
End Sub

I don't get errors, but nothing happened.

What was wrong?

Thanks in advance.

Explanation / Answer

%The principal problem is that you have not assigned any value to the variable wsM<ast and, in any case, for your code to work it would be necessary to find a match between the Data sheet's name and the names of the Master workbook's sheet names. Since the wsMast variable is empty, the expressed condition in the above If Then / End If construct is never satisfied. Consequently, no error is reported and no action is performed - as you have discovered!

It is not clear whether your intention is to copy the entire data sheets into the Master workbook or, perhaps, merely a range thereof onto the corresponding Master sheet.

For the purposes of this response, I have assumed that the sheets are to be copied and, accordingly, I have ensured that each of the copied data sheets is positioned immediately before the corresponding Master sheet.

Therefore, try something like:

'=========>>
Option Explicit

'--------->>
Public Sub PasteToMasterFromData()
    Dim strWbDataName As Variant
    Dim wbData As Workbook                           '\ Data workbook
    Dim wbMast As Workbook                           '\ Master workbook
    Dim SH As Worksheet                                  ' \ Each Data worksheet
    Dim arrSheets As Variant
    Dim Res As Variant
    Dim sName As String
    Dim i As Long, j As Long

    Set wbMast = ThisWorkbook
    strWbDataName = Application.GetOpenFilename( _
                    FileFilter:= _
                    "Excel Files (*.xlsm), *.xlsm", _
                    Title:="Please select a correct file")

    If strWbDataName = False Then
        MsgBox "No file."
        Exit Sub
    End If

    Set wbData = Workbooks.Open(Filename:=strWbDataName)
    With wbMast
        i = .Worksheets.Count
        ReDim arrSheets(1 To i)
        For j = 1 To i
            arrSheets(j) = .Worksheets(j).Name
        Next j
    End With

    For Each SH In wbData.Worksheets
        With SH
            sName = .Name
            Res = Application.Match(sName, arrSheets, 0)
            If Not IsError(Res) Then
                SH.Copy Before:=wbMast.Sheets(sName)
            End If
        End With
    Next SH
End Sub
'<<=========

#^#$)*

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.