CODEWITHSUNDEEP
vbaexcel
NormX
NormX

Reputation: 125

Copying a range from a hidden sheet

i have a vba code to copy and paste a range of data from multiple excel files in a folder. The sheet that has the data is hidden though. i need to modify my code to copy the hidden sheets range.

Sub Import_to_Master() 
Dim sFolder As String 
Dim sFile As String 
Dim wbD As Workbook, wbS As Workbook

 Application.ScreenUpdating = False Set wbS = ThisWorkbook sFolder =
 wbS.Path & "\"

 sFile = Dir(sFolder) Do While sFile <> ""

 If sFile <> wbS.Name Then Set wbD = Workbooks.Open(sFolder & sFile)
 'open the file; add condition to

 ' >>>>>> Adapt this part wbD.Sheets("data").Range("A3:BD3").Copy
 wbS.Activate Sheets("data scorecards").Range("A" &
 Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 Application.CutCopyMode = False ' >>>>>> wbD.Close savechanges:=True
 'close without saving End If

 sFile = Dir 'next file Loop Application.ScreenUpdating = True

End Sub

Upvotes: 1

Views: 4603

Answers (1)

user4039065
user4039065

Reputation:

This looks appropriate. I've used direct value transfer instead of copy, paste special, values.

Option Explicit

Sub Import_to_Master()
    Dim sFolder As String, sFile As String
    Dim wbS As Workbook

     Application.ScreenUpdating = False

     Set wbS = ThisWorkbook
     sFolder = wbS.Path & "\"
     sFile = Dir(sFolder & "*.xl*")

     Do While sFile <> ""
        If sFile <> wbS.Name Then
            'open the file; add condition to
            With Workbooks.Open(sFolder & sFile)
                ' >>>>>> Adapt this part wbD
                With .Worksheets("data").Range("A3:BD3")
                    wbS.Worksheets("data scorecards").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Resize(.Rows.Count, .Columns.Count) = .Value
                End With
                'close without saving
                .Close savechanges:=False
             End With
        End If
        sFile = Dir 'next file
     Loop

     Application.ScreenUpdating = True

End Sub

Upvotes: 1

Related Questions