You missed a setp:
Information
Use Alt F11 to open the VBA editor and you see that there are four modules in this workbook.
The code in the module named "Basic_Code_Module" will be used by every example in this workbook.
Note: you not have to change anything in this module.
Hi Andrew,
Thanks for this - I've tried running the copy sheet macro which seems to do what I need but inconsistently. The first sheet it has copied it has copid values and format which is perfect and renamed the sheet. However it has not done this with all the sheets, some have copied with values but not format and some have not changed from the original sheet name.
Any ideas how i resolve this?
Thanks
Hi Andrew,
Sorry to be a pain It does seem to be working with the one exception of the naming of the sheets, some of the sheets are copying in and renaiming to the name of the file they copied from but others are keeping there original name do you know how i can resolve this. This is what I have so far
Then if there are files in the folder we call the macro "Get_Sheet"
'There are three arguments in this macro that we can change
'1) PasteAsValues = True to paste as values (recommend)
'2) SourceShName = sheet name, if "" it will use the SourceShIndex
'3) SourceShIndex = to avoid problems with different sheet names use the index (1 is the first worksheet)
' Do not change myReturnedFiles:=myFiles
Sub RDB_Copy_Sheet()
Dim myFiles As Variant
Dim myCountOfFiles As Long
myCountOfFiles = Get_File_Names( _
MyPath:="Y:\Macro test for rolling forecast", _
Subfolders:=False, _
ExtStr:="*.xl*", _
myReturnedFiles:=myFiles)
If myCountOfFiles = 0 Then
MsgBox "No files that match the ExtStr in this folder"
Exit Sub
End If
Get_Sheet _
PasteAsValues:=True, _
SourceShName:="", _
SourceShIndex:=2, _
myReturnedFiles:=myFiles
End Sub
' Note: You not have to change the macro below, you only
' edit and run the RDB_Copy_Sheet above.
Sub Get_Sheet(PasteAsValues As Boolean, SourceShName As String, _
SourceShIndex As Integer, myReturnedFiles As Variant)
Dim mybook As Workbook, BaseWks As Worksheet
Dim CalcMode As Long
Dim SourceSh As Variant
Dim sh As Worksheet
Dim I As Long
'Change ScreenUpdating, Calculation and EnableEvents
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With
On Error GoTo ExitTheSub
'Add a new workbook with one sheet
Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
'Check if we use a named sheet or the index
If SourceShName = "" Then
SourceSh = SourceShIndex
Else
SourceSh = SourceShName
End If
'Loop through all files in the array(myFiles)
For I = LBound(myReturnedFiles) To UBound(myReturnedFiles)
Set mybook = Nothing
On Error Resume Next
Set mybook = Workbooks.Open(myReturnedFiles(I))
On Error GoTo 0
If Not mybook Is Nothing Then
'Set sh and check if it is a valid
On Error Resume Next
Set sh = mybook.Sheets(SourceSh)
If Err.Number > 0 Then
Err.Clear
Set sh = Nothing
End If
On Error GoTo 0
If Not sh Is Nothing Then
sh.Copy after:=BaseWks.Parent.Sheets(BaseWks.Parent.Sheets.Count)
On Error Resume Next
ActiveSheet.Name = mybook.Name
On Error GoTo 0
If PasteAsValues = True Then
With ActiveSheet.UsedRange
.Value = .Value
End With
End If
End If
'Close the workbook without saving
mybook.Close savechanges:=False
End If
'Open the next workbook
Next I
' delete the first sheet in the workbook
Application.DisplayAlerts = False
On Error Resume Next
BaseWks.Delete
On Error GoTo 0
Application.DisplayAlerts = True
ExitTheSub:
'Restore ScreenUpdating, Calculation and EnableEvents
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = CalcMode
End With
End Sub
Thanks again for any assistance you can offer
