Excel vba

Hi Jens & Doug

Hope you are both well.

I have a double query with a macro I'm trying to implement. I'll try to explain in two parts.

Part 1

I have been tasked with adding a macro to a worksheet via a button. The 'button' itself is a rounded rectangle shape drawn in Excel and isn't a form control or an active x control. I just right click on the shape and 'assign macro'.

The worksheet already has a macro which is also activated via a similar button. (This 'button' is also a rounded rectangle shape and isn't a form control or an active x control)

Due to the structure of the worksheet, the only place I can insert my new button is by adding in a new row directly below the existing button.

However, here is my problem. When I add the new row, the existing macro wont run when I click the button.

Here is the existing module code. I appreciate most of this isn't relevant to my problem but I'm hoping you guys can hopefully identify the issue. For background, what we use the macro for is to extract info from a database based on ref numbers input into a worksheet column. the macro (when the button is pressed) then spits out all the data relating to these ref number in the form of rows and up to 86 columns worth of data.



Sub UpdateQuery()

Application.Calculation = xlCalculationManual

StopSub = False

'1: check cell A1 contains 1 or 2
If Not Range("A1") = 1 Then
If Not Range("A1") = 2 Then MsgBox ("Error: Cell A1 should contain '1' for ECO1 or '2' for ECO2")
End If
ECOPeriod = Range("A1").Value

'2: Clear contents, check there's some MRNs in the list
Worksheets("ECO1 Results").Range("A2:BV1000000").ClearContents
Worksheets("ECO2 Results").Range("A2:CH1000000").ClearContents
If IsEmpty(Worksheets("List of MRNs").Range("B5")) Then End

'3:
KeepPowerOn

ErrorTotal = 0
TotalMeasures = WorksheetFunction.Counta(Worksheets("List of MRNs").Range("B5:B1000000"))

'4: Start loop
For Counter = 1 To TotalMeasures

'5: Read MRN and enter it into relevant sheet
CurrentMRN = Worksheets("List of MRNs").Range("B4").Offset(Counter, 0).Value
If ECOPeriod = 1 Then
Worksheets("DWH Pivot & Workings").Range("F2").Value = CurrentMRN
End If

If ECOPeriod = 2 Then
Worksheets("DWH Pivot & Workings").Range("F23").Value = CurrentMRN
End If

'6: Calculate workings sheet
Worksheets("DWH Pivot & Workings").Calculate

DoEvents

'7: Update relevant table with new query
If ECOPeriod = 1 Then
On Error GoTo ErrorFound
With Worksheets("DWH Returns").Range("A3").ListObject.QueryTable
.Connection = Array( _
"OLEDB;Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=ECO;Data Source=lonp-ecoBe01;MDX Compatibi" _
, "lity=1;Safety Options=2;MDX Missing Member Mode=Error")
.CommandType = xlCmdDefault
.BackgroundQuery = False
.CommandText = Worksheets("DWH Pivot & Workings").Range("F8").Value
.Refresh
End With
End If

If ECOPeriod = 2 Then
On Error GoTo ErrorFound
With Worksheets("ECO2 Returns").Range("A3").ListObject.QueryTable
.Connection = Array( _
"Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=ECO2;Data Source=lonp-ECOBE01;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error")
.CommandType = xlCmdDefault
.BackgroundQuery = False
.CommandText = Worksheets("DWH Pivot & Workings").Range("F32").Value
.Refresh
End With
End If

DoEvents

PasteRow = Counter + 1

DoEvents

'8: Copy results out
If ECOPeriod = 1 Then
ResultsPaste = "A" & PasteRow & ":BV" & PasteRow
Worksheets("DWH Returns").Range("A4:BV4").Copy
Worksheets("ECO1 Results").Range(ResultsPaste).PasteSpecial xlPasteValues
Worksheets("ECO1 Results").Visible = True
Worksheets("ECO1 Results").Activate
End If

If ECOPeriod = 2 Then
ResultsPaste = "A" & PasteRow & ":CH" & PasteRow
Worksheets("ECO2 Returns").Range("A4:CH4").Copy
Worksheets("ECO2 Results").Range(ResultsPaste).PasteSpecial xlPasteValues
Worksheets("ECO2 Results").Visible = True
Worksheets("ECO2 Results").Activate
End If

DoEvents
GoTo GoNext

'9: Error handling
ErrorFound:
ErrorTotal = ErrorTotal + 1
PasteRow = Counter + 1

If ECOPeriod = 1 Then
ResultsPaste = "A" & PasteRow & ":BV" & PasteRow
Worksheets("ECO1 Results").Range(ResultsPaste).Value = "NOT FOUND"
Worksheets("ECO1 Results").Range("D" & PasteRow).Value = Worksheets("List of MRNs").Range("B4").Offset(Counter, 0).Value
Worksheets("ECO1 Results").Visible = True
Worksheets("ECO1 Results").Activate
End If

If ECOPeriod = 2 Then
ResultsPaste = "A" & PasteRow & ":CH" & PasteRow
Worksheets("ECO2 Results").Range(ResultsPaste).Value = "NOT FOUND"
Worksheets("ECO2 Results").Range("D" & PasteRow).Value = Worksheets("List of MRNs").Range("B4").Offset(Counter, 0).Value
Worksheets("ECO2 Results").Visible = True
Worksheets("ECO2 Results").Activate
End If

Resume GoNext

'10 Update progress indicator
GoNext:

Progress.MeasuresRemaining = TotalMeasures - Counter
Progress.PercentComplete2 = (Counter / TotalMeasures) * 100
Progress.Repaint
If StopSub = True Then GoTo EndofSub

Next Counter

EndofSub:
If ErrorTotal > 0 Then MsgBox (ErrorTotal & " MRNs not found in DWH, marked as NOT FOUND")
StopTimer
Progress.Hide

End Sub



Let me know if it's more helpful if I email you a copy of the workbook for context.

Part 2

I want to assign this macro I've created to my second 'new' button. What this does is extract specific columns from the data which is output when the first original button/macro is clicked. However I want to keep them independent. Would you advise putting this in a separate module? If not, how would I incorporate this into the existing module?

[I]

Sub Chop()
Set DestSht = Sheets.Add(After:=Sheets(Sheets.Count))
DestSht.Name = "Extract1"
Sheets("ECO2 Results").Columns("D:D").Copy Destination:=DestSht.Range("A1")
Sheets("ECO2 Results").Columns("C:C").Copy Destination:=DestSht.Range("B1")
Sheets("ECO2 Results").Columns("AR:AR").Copy Destination:=DestSht.Range("C1")
Sheets("ECO2 Results").Columns("AL:AL").Copy Destination:=DestSht.Range("D1")
Sheets("ECO2 Results").Columns("BW:BW").Copy Destination:=DestSht.Range("E1")
Sheets("ECO2 Results").Columns("AQ:AQ").Copy Destination:=DestSht.Range("F1")

Application.CutCopyMode = False

End Sub

[/I]

Again let me know if you need the worksheet. Apologies for the long winded email to what is probably not a very complex macro, but I thought I should provide as much info as possible :)

Any help would be much appreciated. Regards, Cal.