Hi,
I am using the following macro to import data. However i have little bit of a problem. The filename that i am importing from will always start with "Diary" and then it can be Diary (1).xlsx or Diary (3).xlsx etc.
The macro i am using is:
How can i change this part
to import from any file that starts with the filename "Diary ".xlsx?
I am using the following macro to import data. However i have little bit of a problem. The filename that i am importing from will always start with "Diary" and then it can be Diary (1).xlsx or Diary (3).xlsx etc.
The macro i am using is:
Code:
Sub CopyData2()
Dim sBook_t As String
Dim sBook_s As String
Dim sSheet_t As String
Dim sSheet_s As String
Dim lMaxRows_t As Long
Dim lMaxRows_s As Long
Dim sMaxCol_s As String
Dim sRange_t As String
Dim sRange_s As String
sBook_t = "Warren(4).xlsm"
sBook_s = "Diary (9).xlsx"
sSheet_t = "Diary"
sSheet_s = "Diary"
lMaxRows_t = Workbooks(sBook_t).Sheets(sSheet_t).Cells(Rows.Count, "A").End(xlUp).Row
lMaxRows_s = Workbooks(sBook_s).Sheets(sSheet_s).Cells(Rows.Count, "A").End(xlUp).Row
sMaxCol_s = Workbooks(sBook_s).Sheets(sSheet_s).Cells(1, Columns.Count).End(xlToLeft).Address
sMaxCol_s = Mid(sMaxCol_s, 2, InStr(2, sMaxCol_s, "$"))
If (lMaxRows_t = 1) Then
sRange_t = "A1:" & sMaxCol_s & lMaxRows_s
sRange_s = "A1:" & sMaxCol_s & lMaxRows_s
Workbooks(sBook_t).Sheets(sSheet_t).Range(sRange_t) = Workbooks(sBook_s).Sheets(sSheet_s).Range(sRange_s).Value
Else
sRange_t = "A" & (lMaxRows_t + 1) & ":" & sMaxCol_s & (lMaxRows_t + lMaxRows_s - 1)
sRange_s = "A10:" & sMaxCol_s & lMaxRows_s
Workbooks(sBook_t).Sheets(sSheet_t).Range(sRange_t) = Workbooks(sBook_s).Sheets(sSheet_s).Range(sRange_s).Value
' ###################### NOTE #################
'the following lines are to be used of serial number is to be fixed too, instead of being copied
' if there is no need, then delete the line below
' Workbooks(sBook_t).Sheets(sSheet_t).Range("A" & lMaxRows_t).AutoFill Destination:=Workbooks(sBook_t).Sheets(sSheet_t).Range("A" & lMaxRows_t & ":A" & (lMaxRows_t + lMaxRows_s - 1)), Type:=xlFillSeries
End If
' Call DeleteDups
End Sub
How can i change this part
Code:
sBook_s = "Diary (9).xlsx"