Dweller95

New Member
Joined
Nov 11, 2021
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
Hi everyone,

I'm trying to copy the range of the second table (green background), however I can only get it to copy the blue background table. I was trying to set it up so the "LastRow" was set to the row that the string "Total" is located. Which worked for my first function addSetB, which grabs the blue table if that option was selected.

I'm not able to get this working to copy over the green table and wondering how I should change my function around or how do I go about accomplishing this.

Appreciate any feedback and anyone who takes some time to try and help me out.

1636637289067-png.50969


Here is the code I used to copy SetB (Blue Background) which functions correctly.
VBA Code:
' ADD Data from SetB (Blue Background) to 'DATA' Sheet
Sub addSetB()
    
    Dim LastRow As Integer
    LastRow = Cells.Find(What:="Total", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("L3:Q" & LastRow).Copy
    
    Sheets("DATA").Activate
    Cells(Range("A" & rows.Count).End(xlUp).Row + 1, 1).PasteSpecial xlPasteValues
          
End Sub

Here is my attempt at copying SetC (Green Background) which is not working properly at all.
VBA Code:
' ADD Data from SetC (Green Background) to 'DATA' Sheet
Sub addSetC()
    
    Dim LastRow As Integer
    Dim StartRow As Integer
    StartRow = Cells.Find(What:="Total", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    LastRow = Cells.Find(What:="Total", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    Range("L" & StartRow & ":Q" & LastRow).Copy
    
    Sheets("DATA").Activate
  
End Sub
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Dweller95

New Member
Joined
Nov 11, 2021
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
Here is my attempt at copying SetC (Green Background) which is not working properly at all.
VBA Code:
' ADD Data from SetC (Green Background) to 'DATA' Sheet
Sub addSetC()
   
    Dim LastRow As Integer
    Dim StartRow As Integer
    StartRow = Cells.Find(What:="Total", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    LastRow = Cells.Find(What:="Total", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
   
    Range("L" & StartRow & ":Q" & LastRow).Copy
   
    Sheets("DATA").Activate
 Cells(Range("A" & rows.Count).End(xlUp).Row + 1, 1).PasteSpecial xlPasteValues
End Sub
Sub addSetC() did not contain the paste in the actual post, so fixed here.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,571
Messages
5,770,920
Members
425,652
Latest member
Pemby

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
Top