Reuse VBA Code with other variables

KiwiGrue

New Member
Joined
Oct 24, 2021
Messages
25
Office Version
  1. 365
Platform
  1. MacOS
I have developed some vba code to extract monthly financial data from one workbook to copy to another checking that the date of the data is the next (and valid) month. I have multiple sub routines to achieve this but I am struggling to tidy up the code and reuse it rather than have several subs.

The Profit & Loss variables are:

1. Trading Income/Total Trading Income
2. Other Income/Total Other Income
3. Cost of Sales/Total Cost of Sales
4 Operating Expenses/Total Operating Expenses

The P&L categories and sub-categories may vary from month to month.

The sub routine for Trading Income/Total Trading Income is attached below - how do I reuse the code efficiently to work through the 4 categories sequentially?

I am new to VBA so any insights or assistance would be appreciated.

Cheers


VBA Code:
Sub CopyPasteTradingIncomeData()

'Extract data from monthly P&L account and paste in Master data document.
Dim wkbk As Workbook
Dim dataBook As Workbook
Dim cell1 As Range
Dim cell2 As Range
Dim rw As Integer
Dim Startdate As Date
Dim Enddate As Date
Dim Checkdate As Date
Dim IrTarget As String
Dim IntervalType As String

'Check the last month data was pasted in the Master Data Workbook.

'Specifies "m" as month interval.
IntervalType = "m"

'Ask user to input month of data to be pasted to the Master data workbook
Startdate = InputBox("Enter month ending for P&L data to be pasted in Master Data workbook - Format dd/mm/yyyy", "Information Month Ending")

'Set workbook to destination workbook to paste information.
Set dataBook = Workbooks("Financial Performance.xlsm")
dataBook.Activate

'Finds last cell with data.
lrTarget = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row

'Date in last cell of Master Data workbook.
Enddate = Cells(lrTarget, 1).Value

'Calculate the date for the next month to insert data.
Checkdate = DateAdd("m", 1, Enddate)
  
    If Checkdate = Startdate Then

    Else

    MsgBox ("WARNING: The data is not for the next month!")
   
    End If
   
'Set workbook to source of financial data.
For Each wkbk In Workbooks
        If wkbk.Name Like "*Form_Limited_-_Profit_and_Loss*" Then
            Workbooks(wkbk.Name).Activate
            Exit For
        End If

Next wkbk

'Find start cell and end cell of P&L type to establish range to copy.
Set cell1 = Range("A:A").Find("Trading Income", lookat:=xlWhole)

    If Not cell1 Is Nothing Then
   
    Set cell1 = Range("A:A").Find("Trading Income", lookat:=xlWhole).Offset(1, 0)
    Set cell2 = Range("A:A").Find("Total Trading Income", lookat:=xlWhole).Offset(-1, 0)
   
    Else: MsgBox ("No P&L data for this category this month")
   
    Exit Sub
   
    End If

'Copy Trading Income data.
Range(cell1, cell2).Copy

'Count number of rows with data in them to copy.
 rw = Range(cell1, cell2).Count

'Set workbook to destination workbook to paste information.
Set dataBook = Workbooks("Financial Performance.xlsm")
dataBook.Activate

'Finds first empty cell to insert data.
lrTarget = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row

'Select cell to insert P&L item.
Cells(lrTarget + 1, 2).Select
ActiveSheet.Paste

Range(cell1.Offset(0, 1), cell2.Offset(0, 1)).Copy

Cells(lrTarget + 1, 4).Select
ActiveSheet.Paste

'Copy month into column A and set format as dd/mmm/yyyy.
Range(Cells(lrTarget + 1, 1), Cells(lrTarget + rw, 1)).Value = Startdate
Columns("A").NumberFormat = "dd-mmm-yyyy"

'Copy P&L category into columnC.
Range(Cells(lrTarget + 1, 3), Cells(lrTarget + rw, 3)).Value = "Trading Income"

'Fit data in columns.
Columns("A:D").AutoFit


End Sub
 
Last edited by a moderator:

KiwiGrue

New Member
Joined
Oct 24, 2021
Messages
25
Office Version
  1. 365
Platform
  1. MacOS
Ok, sounds like you have the situation handled.
No not at all ... perhaps I have not been clear enough.

What I would like to know is how do I reuse the following code (is it For ...Loop or another alternative) and make the P&L categories variables:

1. Trading Income/Total Trading Income
2. Other Income/Total Other Income
3. Cost of Sales/Total Cost of Sales
4 Operating Expenses/Total Operating Expenses

So there are two answers I am seeking - (1). how do I make the P&L categories variables and insert the variables rather than the absolute name in the code below? (2). how do I 'loop' through that same code for each of the four variable sets (rather than having four separate subroutines)?


'Find start cell and end cell of P&L type to establish range to copy.
Set cell1 = Range("A:A").Find("Trading Income", lookat:=xlWhole)

If Not cell1 Is Nothing Then

Set cell1 = Range("A:A").Find("Trading Income", lookat:=xlWhole).Offset(1, 0)
Set cell2 = Range("A:A").Find("Total Trading Income", lookat:=xlWhole).Offset(-1, 0)

Else: MsgBox ("No P&L data for this category this month")

Exit Sub

End If

'Copy Trading Income data.
Range(cell1, cell2).Copy

'Count number of rows with data in them to copy.
rw = Range(cell1, cell2).Count

'Set workbook to destination workbook to paste information.
Set dataBook = Workbooks("Financial Performance.xlsm")
dataBook.Activate

'Finds first empty cell to insert data.
lrTarget = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row

'Select cell to insert P&L item.
Cells(lrTarget + 1, 2).Select
ActiveSheet.Paste

Range(cell1.Offset(0, 1), cell2.Offset(0, 1)).Copy

Cells(lrTarget + 1, 4).Select
ActiveSheet.Paste

'Copy month into column A and set format as dd/mmm/yyyy.
Range(Cells(lrTarget + 1, 1), Cells(lrTarget + rw, 1)).Value = Startdate
Columns("A").NumberFormat = "dd-mmm-yyyy"

'Copy P&L category into columnC.
Range(Cells(lrTarget + 1, 3), Cells(lrTarget + rw, 3)).Value = "Trading Income"
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
2,091
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
@KiwiGrue if you want my help then please answer the questions. I am trying to determine how one file name equates to 'Trading Income/Total Trading Income' another file equates to 'Other Income/Total Other Income' etc. If you don't want to assist me in assisting you, so be it.
 

KiwiGrue

New Member
Joined
Oct 24, 2021
Messages
25
Office Version
  1. 365
Platform
  1. MacOS
@KiwiGrue if you want my help then please answer the questions. I am trying to determine how one file name equates to 'Trading Income/Total Trading Income' another file equates to 'Other Income/Total Other Income' etc. If you don't want to assist me in assisting you, so be it.
Sorry that was not clear.

1636857597414.png
1636857633366.png


Here are two examples of the source workbook format. What the code does is calculates the range between say "Trading Income" and "Total Trading Income" copies Column A and B and pastes them into column 2 and 4 in another workbook that will run various pivot tables. The date is pasted into Column 1 and column 3 the type of P&L category.

Then I would like to do the same for each of the other three P&L categories - this is currently done via three additional subroutines. I am simply trying to reduce the code to one creation and 'reuse' it for the other 3 P&L categories.
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
2,091
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
You are still not clear. Please answer my question. What are the names of the workbook files and how are they distinguished?

For example, I can't say 'Some VagueNameFile' take data from this file and use it. Where would I use it at?
 
Last edited:

KiwiGrue

New Member
Joined
Oct 24, 2021
Messages
25
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

As I advised earlier the workbook file names which the data is sourced from is "Physio_Form_Limited_-_Profit_and_Loss August 2021.xlsx"

The other source workbook names vary in that they may or may not include "Physio" but recent files are:

"Physio_Form_Limited_-_Profit_and_Loss May 2021.xlsx"
"Form_Limited_-_Profit_and_Loss June 2021.xlsx"
"Form_Limited_-_Profit_and_Loss July 2021.xlsx"

I do not understand why you need this information?
 

KiwiGrue

New Member
Joined
Oct 24, 2021
Messages
25
Office Version
  1. 365
Platform
  1. MacOS
As I advised earlier the workbook file names which the data is sourced from is "Physio_Form_Limited_-_Profit_and_Loss August 2021.xlsx"

The other source workbook names vary in that they may or may not include "Physio" but recent files are:

"Physio_Form_Limited_-_Profit_and_Loss May 2021.xlsx"
"Form_Limited_-_Profit_and_Loss June 2021.xlsx"
"Form_Limited_-_Profit_and_Loss July 2021.xlsx"

I do not understand why you need this information?
the target workbook is always the same "Financial Performance.xlsm"
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
2,091
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
After a night of sleep. Let's try this one more time.

Since you want to open all of the files, I need to have a way to determine which type of P&L is opened.

1. Trading Income/Total Trading Income
2. Other Income/Total Other Income
3. Cost of Sales/Total Cost of Sales
4. Operating Expenses/Total Operating Expenses

So does the 'Trading Income' Heading appear in the same cell that 'Other Income', Cost of Sales', & 'Operating Expenses' appear on in the other files?

We need some way to identify which file has been opened, so the corresponding variables can be set.

Maybe you have an idea that could distinguish which file has been opened.
 
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,151,772
Messages
5,766,387
Members
425,350
Latest member
procha

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