=Ledger!$A$2:INDEX(Ledger!$A:$W,MATCH(REPT("z",20),Ledger!$A:$W))
'Now resize the range that the data was just added to ->>
Set rng = wksLedger.Range("A" & wksLedger.Rows.Count).End(xlUp).Offset(1, 0) 'append to end
rng.Resize(, UBound(vData) + 1).value
Set Rng = wksLedger.Range("A2:W" & Cells(Rows.Count, "A").End(xlUp).Row)
'Activate worksheet ->>
ActiveWorkbook.Sheets("Ledger").Activate
Range("$A$2").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.value = strtxtNDate
ActiveCell.Offset(0, 1) = strtxtDate
ActiveCell.Offset(0, 2) = strtxtFY
ActiveCell.Offset(0, 3) = strtxtQuarter
ActiveCell.Offset(0, 4) = strtxtWeekNo
ActiveCell.Offset(0, 5) = strtxtWeekdayNb
ActiveCell.Offset(0, 6) = strtxtICash
ActiveCell.Offset(0, 7) = strtxtIEftpos
ActiveCell.Offset(0, 8) = strtxtICash + strtxtIEftpos
ActiveCell.Offset(0, 9) = strtxtODrinks
ActiveCell.Offset(0, 10) = strtxtOCoffee
ActiveCell.Offset(0, 11) = strtxtOBakery
ActiveCell.Offset(0, 12) = strtxtOSmallGoods
ActiveCell.Offset(0, 13) = strtxtOPremadeFood
ActiveCell.Offset(0, 14) = strtxtOFruitVeges
ActiveCell.Offset(0, 15) = strtxtOMiscellaneous
ActiveCell.Offset(0, 16) = strtxtORent
ActiveCell.Offset(0, 17) = strtxtOEnergy
ActiveCell.Offset(0, 18) = strtxtOInsurance
ActiveCell.Offset(0, 19) = strtxtOTelephone
ActiveCell.Offset(0, 20) = strtxtOGas
ActiveCell.Offset(0, 21) = strtxtOSalariesWages
ActiveCell.Offset(0, 22) = strtxtODrinks + strtxtOCoffee + strtxtOBakery + strtxtOSmallGoods + strtxtOPremadeFood + strtxtOFruitVeges _
+ strtxtOMiscellaneous + strtxtORent + strtxtOEnergy + strtxtOInsurance + strtxtOTelephone + strtxtOGas + strtxtOSalariesWages
Dim rng As Range, wksLedger As Worksheet
Set rng = wksLedger.Range("A2:W" & Cells(Rows.Count, "A").End(xlUp).Row)
'Set back to default ->>
Range("$A$1").Select
'Saving the workbook ->>
ActiveWorkbook.Save
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
ThisWorkbook.Names("tblLedger").RefersTo = Range(ThisWorkbook.Names("LedgerPush").RefersToRange.Address(external:=True))
On Error GoTo 0
End Sub
=Ledger!$A$2:INDEX(Ledger!$W:$W,MATCH(99^99,Ledger!$A:$A,1))
Thanks so much for the effort you put into answering my question.
I am unsure if you managed to look into the range names in my workbook but followig your guidelines, here is what I did:
1. I left the existing named range tblLedger as it is =Ledger!$A$1:$W$12 because this is the range I am extracting SQL queries on. Most importantly, this is the one I need to update properly for me so I can execute the following types of queries against it like a table:
Open in new window
2. I changed the range named LedgerPush from what it was i.e. =Ledger!$A$2:INDEX(Ledger!
I might be missing something but please do pardon me as I cannot seem to spot it.
CF