VBA UBound Help

Glasgowsmile

Active Member
Joined
Apr 14, 2018
Messages
280
Office Version
  1. 365
Platform
  1. Windows
I've been using UBound for this worksheet for a while now but I'm not really familiar with how UBound itself works.

I've added a new variable (rtc) which is for the second tab within the current workbook and I added a new With RTC statement to copy data from another sheet and paste into that second tab instead of the first where everything else is currently going.

I don't get any error messages but the data also isn't showing up after running so I'm not sure where it's being pasted.

VBA Code:
Sub BA1()
   Application.DisplayAlerts = False
 
   Dim wkbCrntWorkBook As Workbook
   Dim wkbSourceBook As Workbook
 
   Dim sh As Worksheet, ws As Worksheet, rtc As Worksheet <-- Added this
   Dim rg1 As Variant, rg2 As Variant, rg3 As Variant, rg4 As Variant, rg5 As Variant
   Dim r1 As Long, c1 As Long
   Dim r2 As Long, c2 As Long
   Dim r3 As Long, c3 As Long
   Dim r4 As Long, c4 As Long
   Dim r5 As Long, c5 As Long <-- Added this
 
   Set wkbCrntWorkBook = ActiveWorkbook
   Set sh = wkbCrntWorkBook.Sheets(1)
   Set rtc = wkbCrntWorkBook.Sheets(2) <-- Added this
 
   With Application.FileDialog(msoFileDialogOpen)
      .Filters.Clear
      .Filters.Add "Excel 2007-13", "*.xlsx; *.xls; *.xlsm; *.xlsa; *.csv"
      .AllowMultiSelect = False
      .Show
      If .SelectedItems.Count > 0 Then
         Workbooks.Open .SelectedItems(1)
         Set wkbSourceBook = ActiveWorkbook
         Set ws = wkbSourceBook.Sheets(1)
      
        ' Clearing old data to make room for new data
      
        If Not IsEmpty(Sheet3.Range("A3")) Then
            Sheet3.Range("$A$3:$I$1000").Copy
            Sheet3.Range("$V$3").PasteSpecial xlPasteValues
        End If
      
        ''''''''''''''''''''''''''''''''''''''''''''
      
        Sheets(3).Range("$I$9:$Q$1000").Copy
        Sheet3.Range("$A$3").PasteSpecial xlPasteValues
      
        '''''''''''''''''''''''''''''''''''''''''''''
     
      
         If Not IsEmpty(Sheet1.Range("$B$5, $G$5, $J$5, $M$5")) Then
              
                Sheet1.Range("$O$2:$P$2").ClearContents
                Sheet1.Range("$O$5:$Q$32").ClearContents
              
                Sheet1.Range("$K$2:$L$2").Copy
                Sheet1.Range("$O$2").PasteSpecial xlPasteValues
              
                Sheet1.Range("$K$5:$M$32").Copy
                Sheet1.Range("$O$5").PasteSpecial xlPasteValues
              
                Sheet1.Range("$G$2:$H$2").Copy
                Sheet1.Range("$K$2").PasteSpecial xlPasteValues
              
                Sheet1.Range("$G$5:$I$32").Copy
                Sheet1.Range("$K$5").PasteSpecial xlPasteValues
              
                Sheet1.Range("$B$2:$C$2").Copy
                Sheet1.Range("$G$2").PasteSpecial xlPasteValues
              
                Sheet1.Range("$B$5:$D$32").Copy
                Sheet1.Range("$G$5").PasteSpecial xlPasteValues
            End If
       
         With ws
            rg1 = .Range("S4:S5").Value
            rg2 = .Range("P9:Q36").Value
            rg3 = .Range("J123:J147").Value
            rg4 = .Range("F123:O147").Value
            rg5 = .Range("C161:H192").Value <-- Added this
       
            r1 = UBound(rg1): c1 = UBound(rg1, 2)
            r2 = UBound(rg2): c2 = UBound(rg2, 2)
            r3 = UBound(rg3): c3 = UBound(rg3, 2)
            r4 = UBound(rg4): c4 = UBound(rg4, 2)
            r5 = UBound(rg5): c5 = UBound(rg5, 2) <-- Added this
         End With
       
         With sh
         .Range("$B$2").Resize(c1, r1).Value = Application.Transpose(rg1)
         .Range("$B$5").Resize(r2, c2).Value = rg2
         .Range("$D$5").Resize(r3, c3).Value = rg3
         .Range("$C$90").Resize(r4, c4).Value = rg4
         End With
      
        With rtc <-- Added this
        .Range("B5").Resize(r5, c5).Value = rg5
        End With
      
         wkbSourceBook.Close False
        End If
        End With
      
      
   Range("$B$31:$R$31").ClearContents
  Range("B1").Select
  Application.CutCopyMode = False
End Sub
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Update as I cannot edit - I'm finding that it is pasting in the right place but it's only pasting the first two columns instead of the 6 expected.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top