Clash Royale CLAN TAG#URR8PPP

How do I copy a range from one workbook to another in excel WITHOUT having to name it in VBA?

I'm looking for assistance regarding how to write a VBA command that allows me to copy a range of cells from different workbooks onto one master sheet. Let me explain further.

Everyday I receive a new excel document from my clients named based on the date it was uploaded ie. September 18, 2018 file would be called A20180918.

Once i've received a week's worth of excel files (A20180918-A20180921), I then have to copy certain information from the original uploaded file to a master tracking sheet.

So, my hurdle is such that each time I record my actions into a macro, the code includes the file name which then creates a subscript error when it's run on the next day's file.

So here's an example below of the code I have this far:

Sub CopyRange()

CopyRange Macro

'This is the line of the code that's causing problems given it's a specified workbook name
'and when I try to open tomorrow's workbook i'll run into the subscript error.
Windows("A20180914.xls").Activate

Range("A2:B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Master Sheet.xlsm").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub


Thank you!

Workbook

1 Answer
1

Here's two solutions. One to scan an Directory for files, and the other to select files. I see they've both been suggested in the comments already. :p

Sub Test()
' If you want to scan an "unprocessed files" directory
'Call ScanDir("C:Test")

' If you want to select files to process
Call SelectFiles
End Sub

Private Sub ScanDir(ByVal DirPath As String)
Dim oCurFile As String
oCurFile = Dir(DirPath)
Do While oCurFile <> ""
' Add the work to the file here
' Filename: DirPath & oCurFile

oCurFile = Dir()
Loop
End Sub

Private Sub SelectFiles()
Dim oFileDialog As FileDialog
Set oFileDialog = Application.FileDialog(msoFileDialogFilePicker)
oFileDialog.AllowMultiSelect = True

If oFileDialog.Show = -1 Then
Dim oFile As Variant

For Each oFile In oFileDialog.SelectedItems
' Add the work to the file here
' Filename: oFile
Next
End If

End Sub


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.