0

I have a macro that imports data from a separate workbook into a my dashboard and puts the data into columns B-J. Every time the macro runs, it clears columns B-J to update real time information for the dashboard. I need to pull the data in columns B & D to a separate worksheet and create a running list of all the data. The document starts out blank at the start of the day and my employees use this dashboard all day refreshing the data but I need to retain these 2 columns of information on another sheet. How do I incorporate that into my code I currently have?enter image description here

Sub Get_Data_From_File()
    Dim FileToOpen As Variant
    Dim OpenBook As Workbook
    Dim sourceSheet As Worksheet
    Set sourceSheet = ActiveSheet
     
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual
        
    Sheets("Closed OB").Visible = True
    Sheets("Temp Closed").Visible = True
    
    Sheets("Closed OB").Select
    Range("A:J").ClearContents
    
    FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Import Range", FileFilter:="Excel Files (*.xls*),*xls*")
    If FileToOpen <> False Then
        Set OpenBook = Application.Workbooks.Open(FileToOpen)
        OpenBook.Sheets(1).Range("A4:G1000").Copy
        ThisWorkbook.Worksheets(3).Range("A1:G1000").Value = OpenBook.Sheets(1).Range("A4:G1000").Value
        OpenBook.Sheets(1).Range("H4:H1000").Copy
        ThisWorkbook.Worksheets(3).Range("J1:J1000").Value = OpenBook.Sheets(1).Range("H4:H1000").Value
        OpenBook.Sheets(2).Range("A4:M1000").Copy
        ThisWorkbook.Worksheets(4).Range("A2:R998").Value = OpenBook.Sheets(2).Range("A4:M1000").Value
        OpenBook.Close False
    End If
        
    ThisWorkbook.Worksheets("Closed OB").Range("G1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.TextToColumns Destination:=Range("G1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True
    
    LastRow = Sheets(4).UsedRange.SpecialCells(xlCellTypeLastCell).Row
    
    Call BackupColumns
    
    Sheets("Temp Closed").Select
    With Range("D2:D" & LastRow)
        .NumberFormat = General
        .Value = .Value
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    End With
    
    Sheets("Closed OB").Visible = False
    Sheets("Temp Closed").Visible = False
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.Calculation = xlCalculationAutomatic
         
    Call sourceSheet.Activate
    
End Sub
RMaxwell87
  • 119
  • 8
  • You should be able to copy the two columns in another sheet easily enough, by [finding the last row used](https://stackoverflow.com/questions/71180/how-can-i-find-last-row-that-contains-data-in-a-specific-column) and pasting the data below it. Side note: you may benefit from [avoiding the use of Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code. – cybernetic.nomad Jul 07 '21 at 19:56
  • Good copy. I am very much a novice at coding VBA. I have done all of this looking up on here and youtube so I bet there are better ways to do it for sure. – RMaxwell87 Jul 08 '21 at 20:24

0 Answers0