RFLundgren
New Member
- Joined
- Mar 28, 2022
- Messages
- 7
- Office Version
- 365
- Platform
- Windows
Hi there, back again!
I have some code that allows a user to select a workbook to open. Once opened, I need to select all the data on the active tab and then copy it to another workbook.
The workbook being opened only contains a single tab, and if I rename the tab and reference it directly in the code, it of course works, however each time the original report is run, the tab has a different name, although this name does have some constant elements.
The code below allows me to select a workbook to open, and works as long as the sheet name matches. how do I use a wildcard to manage this scenario?
This is probably relatively easy to do, but it has been 20 years since I last coded, so lots to try and remember!
Many thanks!
Richard
I have some code that allows a user to select a workbook to open. Once opened, I need to select all the data on the active tab and then copy it to another workbook.
The workbook being opened only contains a single tab, and if I rename the tab and reference it directly in the code, it of course works, however each time the original report is run, the tab has a different name, although this name does have some constant elements.
The code below allows me to select a workbook to open, and works as long as the sheet name matches. how do I use a wildcard to manage this scenario?
VBA Code:
Public Sub GetFreshDeskData()
'Open raw data file saved from Freshdesk to utilise in the monthly report
Dim targetWorkbook As Workbook
Set targetWorkbook = Application.ThisWorkbook
'Dim Filter As String
'Filter = "Text files (*.xlsx),*.xlsx"
Dim Caption As String
Caption = "Please Select the Freshdesk Monthly Tickets Report! "
Dim Ret As Variant
Ret = Application.GetOpenFilename '(Filter, , Caption)
If VarType(Ret) = vbBoolean And Ret = False Then Exit Sub
Dim wb As Workbook
Set wb = Workbooks.Open(Ret)
targetWorkbook.Worksheets("RawData").Cells.Clear
wb.Worksheets("Tickets").UsedRange.Copy targetWorkbook.Worksheets("RawData").Range("A1")
wb.Close SaveChanges:=False
End Sub
This is probably relatively easy to do, but it has been 20 years since I last coded, so lots to try and remember!
Many thanks!
Richard