Hi All,I'm relatively new to VBA and am reintroducing myself to it again now after a short hiatus.My current situation sees me compiling and producing reports based on data we get output from Oracle datatbases. Currently we run an Oracle report that contains multiple worksheets and export them to desktop as an Excel XLS format. Once opened in Excel, we manually copy and paste the data on each sheet from the oracle reports to their corresponding worksheet on our main Reporting Workbook called 'KPIs' overwriting all existing data held on those sheets.There are roughly 10 or 12 worksheets created by the Oracle report, and finishing a manual copy/paste of this data to the existing workbook takes one person in excess of half an hour each day - then along come I saying "there must be an easier way to do this with Excel VBAs".I wrote this little bit of code, but it doesn't quite work as expected...
As you can see, the above is run from within the main KPI Workbook that needs fresh data every morning. As the workbook can be updated by anybody, I built in a manual open dialog for the user to find the file they need to copy from (usually on their desktop, but you never know!). It then selects the first sheet on the KPI wb and clears the existing data.The final section of the code was expected to select the data to copy from the Oracle wb sheets and paste this into their corresponding worksheets on the KPI wb.What I actually get is that ALL the ws from the Oracle wb are pasted into the first ws on my main KPI wb - called 'Alerts'.I hop I haven't made this too complicated, but could somebody please point me in the right direction?
Sub ImportData() Dim wb1 As Workbook Dim wb2 As Workbook Dim Sheet As Worksheet Dim PasteStart As Range Set wb1 = ActiveWorkbook Set PasteStart = [Alerts!A4] 'this section selects the first KPI sheet to copy to Sheets("Alerts").Select LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row Range("A4:E" & LastRow).Select Selection.ClearContents 'this section opens the Oracle report saved to your PC FileToOpen = Application.GetOpenFilename _ (title:="Please choose a Report to Parse", _ FileFilter:="Report Files *.xls (*.xls),") If FileToOpen = False Then MsgBox "No File Specified.", vbExclamation, "ERROR" Exit Sub 'this section selects the data on Oracle report and pasts to the KPI sheets Else Set wb2 = Workbooks.Open(fileName:=FileToOpen) LastRow = ActiveSheet.Range("A2" & Rows.Count).End(xlUp).Row Range("A2:Z" & LastRow).Select For Each Sheet In wb2.Sheets With Sheet.UsedRange .Copy PasteStart Set PasteStart = PasteStart.Offset(.Rows.Count) End With Next Sheet End If wb2.Close End Sub
Last edited by a moderator: