Stops after opening Workbook + Sheets(array(....)

[finaljustice]

Hello, i'm quite new to VBA and I am trying to develope a macro that updates a specific workbook.

There are two questions I would like to ask,

1) How to Select 4 (or more) sheets which their names were saved as variables.

Eg. Sheets(Array("var1","var2","var3","var4")).Select

Where Var1, Var2, Var3 are variable which got their values from cells. (This I know how to do)

Could this be done by using a Array variable, if so could it look something like: (lets say my array variable is Avar() )
Sheets(Avar()).select


2)
I've explained what my problem is within the code.

Sub AtualizarForecast()
Sheets(1).Select
Range("H4").Select
Do While ActiveCell <> Empty 'list of names of workbooks that must be opened
    NomeSuperint = ActiveCell.Value 'Name of the workbooks that must be opened
    MsgBox "Selecione a pasta para o arquivo: " & NomeSuperint 'shows the user what workbook must be "opened"
    DiretForecast = Application.GetOpenFilename
    Application.Workbooks.Open (DiretForecast) 'opens work
 
    'now here is where I am stumbling, once it gets to this point the newly opened workbook stops the macro. I need to copy information from the opened workbook and paste it back on the first workbook
 
    Sheets(1).Select
    Range("C15").Select
    Range(ActiveCell, Cells(ActiveCell.Row, 256).End(xlToLeft)).Copy
 
    ActiveCell.Offset(1, 0).Select
 
 
 Loop
 
End Sub

So basically that is my issue, once vba opens the new workbook it becomes the active workbook and the macro stops runing. I wish to know, how do I make Vba open this new workbook and continue running the macro.



Thank you very much for your attention and help, this will greatly help me.

Yours sincerely,
final

 

[Andrew Poulsom]

For you first question this worked for meL

Sub Test()
    Dim Avar As Variant
    Avar = Array("Sheet1", "Sheet2", "Sheet3")
    Sheets(Avar).Select
End Sub

For you second question assign the ActiveSheet to an object variable before opening the other workbook. Then use that object variable to qualify the range you want to copy to.

 

[finaljustice]

For you first question this worked for meL

Sub Test()
    Dim Avar As Variant
    Avar = Array("Sheet1", "Sheet2", "Sheet3")
    Sheets(Avar).Select
End Sub

For you second question assign the ActiveSheet to an object variable before opening the other workbook. Then use that object variable to qualify the range you want to copy to.

Hi there I tried doing what you said, but now I'm getting an error on the line "wbf.Worksheets(wsa).Range("C15").Select 'goes back to wbf,selects the sheets with same name as wsa and pastes the value."

I would really appreciate if you could help me out here, I've never actually used VBA with more than one workbook open, and my vba jargon is really poor.

I tried to explain every step of the way of what i'm trying to do, if you could please help me to fix it and make it work I would be very greatfull!!

Sub AtualizarForecast2()
Sheets(1).Select
Range("H4").Select
Set wbf = ActiveWorkbook
Do While ActiveCell <> Empty 'list of names of workbooks that must be opened
    NomeSuperint = ActiveCell.Value 'Name of the workbooks that must be opened
    MsgBox "Selecione a pasta para o arquivo: " & NomeSuperint 'shows the user what workbook must be "opened"
    DiretForecast = Application.GetOpenFilename
    Application.Workbooks.Open (DiretForecast) 'opens workbook
    Set wba = ActiveWorkbook
        For i = 1 To wba.Worksheets.Count
            Sheets(i).Select
            wsa = ActiveSheet.Name 'retrieves the name of the worksheet so that the same sheet can be opened on the initial workbook (wbf)
            Range("C15").Select
            Range(ActiveCell, Cells(ActiveCell.Row, 256).End(xlToLeft)).Copy
            wbf.Worksheets(wsa).Range("C15").Select 'goes back to wbf,selects the sheets with same name as wsa and pastes the value.
            Range("C15").PasteSpecial (xlPasteValues)
 
        Next
wba.Close False 'closes the open workbook and returns to the wbf workbook
 
 Loop 'loops down the list of names of workbooks that should be opened to repeat the process.
 
End Sub

 

[Andrew Poulsom]

You can only select a range on the ActiveSheet. But selecting isn't necessary:

wbf.Worksheets(wsa).Range("C15").PasteSpecial xlPasteValues