Reuse VBA Code with other variables

[KiwiGrue]

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

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

 

[KiwiGrue]

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"

 

[johnnyL]

@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]

@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.

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]

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?

 

[KiwiGrue]

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]

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]

I do not understand why you need this information?

Are you done with my assistance?

 

[KiwiGrue]

Are you done with my assistance?

It has not answered my query nor solved my issues but hopefully someone may be able to assist

Cheers

 

[johnnyL]

Thank you!

 

[johnnyL]

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.