Hi
I have some queries and vba. The vba I've cobbled together from posts I've found rather than having any knowledge, so apologies if it looks wrong!
A query runs (which I've kept simple - it errors 2nd time round if I delete out top row/make as header) and posts data into sheet Get new ORCS1. Get new ORCS2 cell A1=cell A1 in the table created by the query uploaded to Get new ORCS1. The formula is copied down column A.
The macro is meant to pick up data only not formulae and paste it into the next available cell in ORC Register.
The problem is Get new ORCS2 is picking up the column header from Get new ORCS1 and hence usedrange is picking up the column header in Get new ORCS2 and pasting that into ORC Register. I know I'm probably missing a simple line of code to exclude the first line from usedrange. Macro is shown below
Sub RawOtoOsort()
'
' RawOtoOsort Macro
' Runs query that takes data from raw orc (as indicated to upload) removes columns, sorts it and removes any duplicates showing the last update only
'
' Keyboard Shortcut: Ctrl+Shift+M
'
Windows("UK.xlsm").Activate
ActiveWorkbook.Save
ActiveWorkbook.Connections("Query - Raw ORC to New Issue numbers").Refresh
Dim wb1 As Workbook, sh1 As Worksheet, sh2 As Worksheet
Set wb1 = Workbooks("UK.xlsm")
Set sh1 = wb1.Sheets("Get new ORCS2")
Set sh2 = wb1.Sheets("ORC Register")
Dim mySeries As Range
Sheets("Get new ORCS2").Select
With Range(Cells(1, "A"), Cells(1, "A").End(xlDown))
.AutoFilter field:=1, Criteria1:="<>" & ""
On Error Resume Next
Set mySeries = .SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not mySeries Is Nothing Then
'update the chart using mySeries
End If
End With
sh1.UsedRange.Copy
sh2.Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues 'Copies data from source to destination, next avail row.
wb1.Save
Sheets("ORC Register").Select
ActiveWorkbook.Connections("Query - Raw ORC to ORC Sort").Refresh
Sheets("Raw ORC").Select
Range("AF3").Copy
Dim bCell As Range
Set myRange = Range(Range("Ae3"), Range("Ae3").End(xlDown))
For Each myCell In myRange
If myCell.Value <> "" Then
If bCell Is Nothing Then
Set bCell = myCell
Else
Set bCell = Union(bCell, myCell)
End If
End If
Next
If Not bCell Is Nothing Then
bCell.Select
ActiveSheet.Paste
End If
Range("AF3").Select
Selection.Copy
Range("AE3:AE20").Select
ActiveSheet.Paste
Sheets("ORC Register").Select
End Sub
Bookmarks