Good day all,
This is my first post and I am looking for some help with VBA. I am new to coding and macros so what I have below has been copied from other posts on this site but it does't seem to work as I had hoped.
Desired function: I have a Macro Enabled Template workbook, "MODIFICATION TEMPLATE", with a few other macros on the first worksheet named "Command Sheet". I have created a Command Button "Import FSC" and what I had hoped it to do was to create a new sheet after "Command Sheet", and name it "FSC Temp Sheet". The code would then open a dialog box to the file location of the .xls source file in which I could select the most recent file downloaded from a website, allow me to select the workbook needed, copy the data from sheet 1, paste it into the newly created "FSC Temp Sheet" and lastly close the source sheet without saving. Disclosure: I download a new copy of the source sheet each week so both the workbook and worksheet change each time which is why I chose to copy "Sheet 1".
Problem: The below code works perfectly (after much trial, frustration and reading). As I step through each line of the code, a new sheet is created and named correctly, the dialog box opens and I can select the file I want to copy, the .xls file opens momentarily, then switches back to the "MODIFICATION TEMPLATE" workbook and the "FSC Temp Sheet", and finally closes the source sheet but the data is not pasted into the "FSC Temp Sheet".
Lastly, I chose this method mainly because I don't know any better but ultimately once the data is in the FSC Temp Sheet, I can then run another macro to modify the rows and columns as I need for analysis.
I apologize for the long winded post but thought it was better to over explain. Any help or better suggestions would be greatly appreciated.
Thank you
Mater
This is my first post and I am looking for some help with VBA. I am new to coding and macros so what I have below has been copied from other posts on this site but it does't seem to work as I had hoped.
Desired function: I have a Macro Enabled Template workbook, "MODIFICATION TEMPLATE", with a few other macros on the first worksheet named "Command Sheet". I have created a Command Button "Import FSC" and what I had hoped it to do was to create a new sheet after "Command Sheet", and name it "FSC Temp Sheet". The code would then open a dialog box to the file location of the .xls source file in which I could select the most recent file downloaded from a website, allow me to select the workbook needed, copy the data from sheet 1, paste it into the newly created "FSC Temp Sheet" and lastly close the source sheet without saving. Disclosure: I download a new copy of the source sheet each week so both the workbook and worksheet change each time which is why I chose to copy "Sheet 1".
Problem: The below code works perfectly (after much trial, frustration and reading). As I step through each line of the code, a new sheet is created and named correctly, the dialog box opens and I can select the file I want to copy, the .xls file opens momentarily, then switches back to the "MODIFICATION TEMPLATE" workbook and the "FSC Temp Sheet", and finally closes the source sheet but the data is not pasted into the "FSC Temp Sheet".
Lastly, I chose this method mainly because I don't know any better but ultimately once the data is in the FSC Temp Sheet, I can then run another macro to modify the rows and columns as I need for analysis.
I apologize for the long winded post but thought it was better to over explain. Any help or better suggestions would be greatly appreciated.
Thank you
Mater
Code:
Option ExplicitSub Paste_New_FSC_Data()
'Opens a new worksheet for FSC data to be copied to
Dim ws As Worksheet
With ThisWorkbook
Set ws = .Sheets.Add(After:=.Sheets(.Sheets.Count))
ws.Name = "FSC Temp Sheet"
Worksheets("FSC Temp Sheet").Activate
End With
' Opens the FSC weekly folder to select current weeks FSC Download
Dim wbThisWB As Workbook
Dim wbImportWB As Workbook
Dim strFullPath As String
Dim lngLastRow As Long
Dim lngLastCol As Long
Set wbThisWB = ThisWorkbook
With Application.FileDialog(msoFileDialogFilePicker)
.Filters.Clear
.AllowMultiSelect = False
.Title = "Please select most recent FSC Download:"
.Filters.Add "Excel files", "*.xls", 1
.InitialFileName = "H:\MY WORKING FILES\Daves Weekly FSC Files"
.Show
On Error Resume Next 'In case the user has clicked the button
strFullPath = .SelectedItems(1)
If Err.Number <> 0 Then
wbThisWB = Nothing
Exit Sub 'Error has occurred so quit
End If
On Error GoTo 0
End With
Application.ScreenUpdating = False
Set wbImportWB = Workbooks.Open(strFullPath)
'code here to copy and paste tab from Import WB into the current workbook
On Error Resume Next 'In case there's no data or tab doesn't exist
With wbImportWB.Sheets("Sheet 1")
lngLastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lngLastCol = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
If lngLastRow > 0 And lngLastCol > 0 Then
'If the 'lngLastRow' and 'lngLastCol' variable have been set there's data to be copied.
'The following copies the entire range from tab 'Extract' in the import workbook to cell A1 in 'Sheet1' of this workbook (change to suit).
Range(.Cells(1, 1), .Cells(lngLastRow, lngLastCol)).Copy wbThisWB.Sheets("Sheet 1").Cells(1, 1)
End If
End With
On Error GoTo 0
wbImportWB.Close False 'Close the Import WB without saving any changes.
Set wbThisWB = Nothing
Set wbImportWB = Nothing
Application.ScreenUpdating = True
End Sub
