Copy worksheet columns from a second workbook based on the worksheet name in the first workbook - how?

[kntrigirl1908]

Hi! After searching the internet I happened to find myself here. I'm very new to vb and know that it can be used to automate repetitive tasks.

So here is my dilemma. I have several worksheets in a workbook where I have identified a category variance in cell P2. The category variance could be one of six and each are stored as a separate worksheet in a second workbook. I'm trying to work out how to use cell P1 in the first workbook as a reference to go to the same worksheet name in the second workbook and copy a range of columns. The worksheets never change in the second workbook but can for the first which I've built a reference table to handle that.

My thinking was to use a vlookup function to use the worksheet name in the first workbook to find the category variance worksheet name in the 2nd workbook and that works. However, I'm have trouble referencing the value in P2
as part of the formula when copying and pasting between workbooks. CategoryVariance is the 2nd workbook and Generate is the 1st workbook.

My error is "Run-time error '9': Subscript out of range". What am I doing wrong?

Sub Generate ()
Dim Lastws As Integer

Lastws = Application.Worksheets.Count

For i = 3 To Lastws
Worksheets(i).Activate
ActiveSheet.Cells(1, 16).Value = ActiveSheet.Name
ActiveSheet.Cells(2, 16).Value = Application.WorksheetFunction.VLookup(Range("P1").Value, Range("FluxWs"), 3, 0)
Workbooks("CategoryVariance.xlsm").Worksheets(Cells(2, 16)).Range("P:AF").Copy Workbooks("Generate.xlxm").Worksheets(3).Range("P1")

 

[kntrigirl1908]

Hi! After searching the internet I happened to find myself here. I'm very new to vb and know that it can be used to automate repetitive tasks.

So here is my dilemma. I have several worksheets in a workbook where I have identified a category variance in cell P2. The category variance could be one of six and each are stored as a separate worksheet in a second workbook. I'm trying to work out how to use cell P1 in the first workbook as a reference to go to the same worksheet name in the second workbook and copy a range of columns. The worksheets never change in the second workbook but can for the first which I've built a reference table to handle that.

My thinking was to use a vlookup function to use the worksheet name in the first workbook to find the category variance worksheet name in the 2nd workbook and that works. However, I'm have trouble referencing the value in P2
as part of the formula when copying and pasting between workbooks. CategoryVariance is the 2nd workbook and Generate is the 1st workbook.

My error is "Run-time error '9': Subscript out of range". What am I doing wrong?

Sub Generate ()
Dim Lastws As Integer

Lastws = Application.Worksheets.Count

For i = 3 To Lastws
Worksheets(i).Activate
ActiveSheet.Cells(1, 16).Value = ActiveSheet.Name
ActiveSheet.Cells(2, 16).Value = Application.WorksheetFunction.VLookup(Range("P1").Value, Range("FluxWs"), 3, 0)
Workbooks("CategoryVariance.xlsm").Worksheets(Cells(2, 16)).Range("P:AF").Copy Workbooks("Generate.xlxm").Worksheets(3).Range("P1")

I found the answer that works for me, thanks!