Private Sub cmdGetData_Click()
Dim customerBook As Workbook
Dim filter As String
Dim caption As String
Dim customerFilename As String
Dim customerWorkbook As Workbook
Dim targetWorkbook As Workbook
'\\Make weak assumption that active workbook is the target
Set targetWorkbook = Application.ActiveWorkbook
'\\Get the customer workbook
filter = "xls files (*.xls),*.xls"
caption = "Please Select Grid Supply Demand Analysis File "
customerFilename = Application.GetOpenFilename(filter, , caption)
'\\Clear the sheet
Range("A2:J200").Select
Selection.ClearContents
'\\Set the font
ActiveSheet.Range("A2:G200").Font.Name = "Calibri"
ActiveSheet.Range("A2:G200").Font.Size = 11
ActiveSheet.Range("A2:G200").Font.ColorIndex = 1 'Black
'\\Fit row/ZColumns to header
Columns("A:J").EntireColumn.AutoFit
'\\Set focus to Cell A1 on Sheet1
Range("A2").Select
Set customerWorkbook = Application.Workbooks.Open(customerFilename)
'\\Copy data from customer to target workbook
Dim targetSheet As Worksheet
Set targetSheet = targetWorkbook.ActiveSheet 'targetWorkbook.Worksheets(1)
Dim sourceSheet As Worksheet
Set sourceSheet = customerWorkbook.Worksheets(1)
targetSheet.Range("A2", "A200").Value = sourceSheet.Range("A4", "A205").Value
targetSheet.Range("B2", "B200").Value = sourceSheet.Range("C4", "C205").Value
targetSheet.Range("C2", "C200").Value = sourceSheet.Range("D4", "D205").Value
targetSheet.Range("D2", "G200").Value = sourceSheet.Range("G4", "J205").Value
'\\Close customer workbook
customerWorkbook.Close
'\\Auto fit the new columns A,J
Columns("A:J").EntireColumn.AutoFit
'\\Delete Rows starting with "BUYER"
Call DelBUYERrows
'\\Delete Rows starting with "BUYER-COIL"
Call DelBUYERcoilrows
'\\Deletes the string " - SRK - - - " (Run first or it will take the SRK and leave dashes)
Call DelSRKDash
'\\Deletes the string "SRK "
Call DelSRK
'\\Calls AddSpaces to seperate machines
Call AddSpaces
'\\Set focus to Cell A1 on Sheet1
Range("A2").Select
'Save me
ActiveWorkbook.Save
End Sub
Sub DelBUYERrows()
p = Range("A" & Rows.Count).End(xlUp).Row
For r = p To 1 Step -1
If Range("A" & r).Value = "BUYER" Then
Range("A" & r).EntireRow.Delete
End If
Next r
End Sub
Sub DelBUYERcoilrows()
n = Range("A" & Rows.Count).End(xlUp).Row
For r = n To 1 Step -1
If Range("A" & r).Value = "BUYER-COIL" Then
Range("A" & r).EntireRow.Delete
End If
Next r
End Sub
Sub DelSRKDash()
Columns("C:C").Replace What:=" - SRK - - - ", Replacement:="", LookAt:=xlPart
End Sub
Sub DelSRK()
Columns("C:C").Replace What:="SRK ", Replacement:="", LookAt:=xlPart
End Sub
, not consistent in rows and columns as such, you might want to even use an intermediate Excel workbook to put the data into a format that it can be either imported or just linked to.?
