Getting data from multiple sheets into a variable of defined for another sheet

Question

I have come across a case where I want to get the data from multiple sheets into a variable in another sheet in VBA. I want to append that variable data into a Textfile.

I dont want to export the entire data into a textfile, I only want to export the data in a particular range.

So right now for each sheet I have a variable that contains required data from each worksheet and I want to send data from variable from each sheet to a function defined in a Master Sheet.

Lets say I have a variable s1 in sheet 1 containing some data. I want that variable data to be assessable in another sheet.

How can I meet the above requirement?

Viktor West · Accepted Answer · 2020-06-16 10:33:45Z

2

In case you would like to control it from the MasterSheet, you have declare the variable as Public on the Sheets, then Give some value for the text. You can get the values from the MasterSheet:

On Sheet1

Public s1 As String

Sub giveValue()
    s1 = "Hello from Sheet1"
End Sub

On Sheet2

Public s1 As String

Sub giveValue()
    s1 = "Hello from Sheet2"
End Sub

Access using the MasterSheet on MasterSheet or any modul

Sub AssessSheetVariables()
    Debug.Print Sheet1.s1
    Debug.Print Sheet2.s1
End Sub

Output:

Hello from Sheet1
Hello from Sheet2

answered Jun 16, 2020 at 10:33

Viktor West

5746 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jamheadart · Accepted Answer · 2020-06-16 10:18:55Z

1

If you have s1 in worksheet Sheet1 you can pass it to a MasterSheet function:

On Sheet1

Sub Example()
Dim s1 As String
    s1 = "Hello"
    MasterSheet.AssessString s1
End Sub

On MasterSheet

Sub AssessString(s As String)
    ' do some stuff
End Sub

answered Jun 16, 2020 at 10:18

jamheadart

5,3737 gold badges37 silver badges87 bronze badges

Collectives™ on Stack Overflow

Getting data from multiple sheets into a variable of defined for another sheet

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related