Copy Subtotals from Source Workbook to Current Workbook

[Lelewiwi]

Hello helpful peeps!

Here is what I have:

Workbook1 = Consolidation
Sheet1 = Annuity
Sheet2 = Annuity Adj (will be hidden)

Workbook2 = Variable Name
Sheet1 = Remit (Variable rows)
Sheet2 = Adjust (Variable rows)

I have current code that sets up the Annuity and Annuity Adj worksheets in the Consolidation Workbook. The Remit and Adjust sheets in the Variable Named Workbook will also have variable data. What I need, is code to copy ONLY subtotals ($) from column G in the Remit worksheet and paste in the Annuity worksheet starting at cell B3. I also need the code to copy column A (from A2) and column G ($ from G2) from the Adjust sheet to the Annuity Adj sheet starting at A2. From there it will do some calculations on its own.

From here I have some others copying that I will need to do, but this is the starting point. That I am stuck on. Hopefully, I will be able to figure out the rest of the copying from this code. Thanks for all your help!

 

[DanteAmor]

What I need, is code to copy ONLY subtotals ($) from column G

I don't understand exactly where the subtotals are, would it help if you gave an example, or how do I identify where the subtotals are?


Try the following macro, copy column G from cell G2 and to the last cell with data in column G of the "Remit" sheet.
The macro assumes that you will have 2 workbooks open, the "Consolidation" workbook and another workbook with a variable name but with the "Remit" and "Adjust" sheets.

Sub copy_data()
  Dim wb1 As Workbook, wb2 As Workbook, wb As Workbook
  Dim sh11 As Worksheet, sh12 As Worksheet, sh21 As Worksheet, sh22 As Worksheet
  Dim sName As String
  Dim lr As Long
  
  Application.ScreenUpdating = False
  DoEvents
  
  'Set Workbook1
  Set wb1 = Workbooks("Consolidation")
  Set sh11 = wb1.Sheets("Annuity")
  Set sh12 = wb1.Sheets("Annuity Adj")
  
  'Set Workbook2
  sName = "Remit"
  For Each wb In Workbooks
    If wb.Name <> wb1.Name Then
      wb.Activate
      If Evaluate("ISREF('" & sName & "'!A1)") Then
        Set wb2 = wb
        Set sh21 = wb2.Sheets("Remit")
        Set sh22 = wb2.Sheets("Adjust")
        Exit For
      End If
    End If
  Next
  
  If Not wb2 Is Nothing Then
    'copy ONLY subtotals ($) from column G in the Remit worksheet
    'and paste in the Annuity worksheet starting at cell B3
    sh21.Range("G2", sh21.Range("G" & Rows.Count).End(3)).Copy
    sh11.Range("B3").PasteSpecial xlPasteValues
    
    'copy column A (from A2) and column G ($from G2) from the Adjust sheet
    'to the Annuity Adj sheet starting at A2
    lr = sh22.Range("A" & Rows.Count).End(3).Row
    sh22.Range("A2:A" & lr & ",G2:G" & lr).Copy
    sh12.Range("A2").PasteSpecial xlPasteValues
  End If

  Application.ScreenUpdating = True
  Application.CutCopyMode = False
End Sub

If it is not what you need, you could detail with examples.

I hope to hear from you very soon.
Cordially
Dante Amor
----- --