0

I have a sheet with multiple Excel named tables that copy the table data (within a certain named range) and pastes it into another sheet for recording purposes. However sometimes there may be no data, and I get a runtime error. So I am trying to work out how to create a loop or if statement to skip that table and move to the next one, but my VBA skills are limited to some recording and Googling and am lost as to how to incorporate this in my existing code...

Sub CopyTrackingHist()

' CopyTrackingHist Macro

Dim ws As Worksheet
Dim tblM As ListObject
Dim tblR As ListObject
Dim tblN As ListObject
Dim tblI As ListObject

Set ws = Sheets("Tracking")
Set tblM = ws.ListObjects("Maturities_Track")
Set tblR = ws.ListObjects("Rollovers_Track")
Set tblN = ws.ListObjects("NewDeal_Track")
Set tblI = ws.ListObjects("IntPay_Track")

    Application.ScreenUpdating = False
'Daily Maturities Copied from Tracking Sheet to Historical sheet

      Sheets("Tracking").Select
      Range(tblM & "[[Date]:[Daily Flow Client Rate Interest Mar]]").Select
      Selection.Copy
'Paste into Historical capture sheet
      Sheets("Maturities_Hist").Select
      Range("A1").Select
      Range("A1").End(xlDown).Offset(1, 0).Select

      Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

'Daily Rollovers copied to Historical Sheet
    Sheets("Tracking").Select
    Range(tblR & "[[Date Stlmt]:[Daily Flow Client Rate Interest Mar]]").Select
    Selection.Copy
'Paste into Historical capture sheet
    Sheets("Rollovers_Hist").Select
    Range("A1").Select
    Range("A1").End(xlDown).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

'Daily New Deals table to Historical sheet
    Sheets("Tracking").Select
    Range(tblN & "[[Date Stlmt]:[Daily Flow Client Rate Interest Mar]]").Select
    Selection.Copy
'Paste into Historical capture sheet
    Sheets("NewDeals_Hist").Select
    Range("A1").Select
    Range("A1").End(xlDown).Offset(0, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

'Daily Interest Payments copied to Historical Sheet
    Sheets("Tracking").Select
    Range(tblI & "[[Date]:[Daily Flow Client Rate Interest Mar]]").Select
    Selection.Copy
'Paste into Historical capture sheet
    Sheets("InterestPymts_Hist").Select
    Range("A1").Select
    Range("A1").End(xlDown).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

End Sub

The end user enters in information into each table, so I plan to use this macro before another macro that clears and resets the sheet. If you can also share how to plug this into the existing macro, that would be fabulous :) I am also open to feedback and suggestions to make the code more efficient ;) Thanks all

Antonia_VonD
  • 1
  • 1
    [This](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?rq=1) is helpful to you. – Dy.Lee Apr 23 '20 at 06:37
  • You might also find the documentation for [`If...Then...Else`](https://learn.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/if-then-else-statement) handy. With this you can evaluate a (or multiple) conditions and execute code based of it it is true or false. – Samuel Everson Apr 23 '20 at 07:40
  • Thanks Dy and Samuel, I'll have a look at your suggestions! :) – Antonia_VonD Apr 29 '20 at 02:09

0 Answers0