Select sheet based on variable declared - VBA Code Query

[imgs1607]

Hi All,


I am new to VBA and this is my first post in this forum. I am currently stuck with an VBA error and can somebody help me on this plz.


Case Scenario : Copy paste sheets (cells.select) from one book (test.xlsx) to another (book1.xlsm) with sheet names declared with variables C1, C2, C3 & P1, P2, P3.. i.e., Copy paste C1 to P1 etc..


Below is the sample code, which I am trying to achieve. I am currently facing errors "Subscript out of range".




Sub Macro1()
'
' Macro1 Macro
'


'
Dim C, P As Worksheet
Workbooks.Open Filename:="C:\Users\girish.gs\Desktop\test.xlsx"

C1 = "abc" 'Sheetname in the workbook test.xlsx
C2 = "def" 'Sheetname in the workbook test.xlsx
C3 = "lmn" 'Sheetname in the workbook test.xlsx

P1 = "jkl" 'Sheetname in the workbook Book1.xlsm
P2 = "fgh" 'Sheetname in the workbook Book1.xlsm
P3 = "xyz" 'Sheetname in the workbook Book1.xlsm


For i = 1 To 3
C = "C" & i
P = "P" & i

Windows("test.xlsx").Activate
Worksheets(C).Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book1").Activate
Worksheets(P).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Next i


End Sub

 

[Joe4]

Welcome to the Board!

That is not going to work, as C will be the literal string C1, not equal to variable C1.

Also, you need to declare each variable explicitly.
In this statement:

Dim C, P As Worksheet

P would be dimmed a Worksheet, but C is dimmed as Variant

Use Arrays to loop through your sheets. Note that Arrays are 0 based by default. So to loop through three values, you would go from 0 to 2.
So try this code:

Sub Macro1()
'
' Macro1 Macro
'

Dim C As Variant, P As Variant
Dim i As Integer

Workbooks.Open Filename:="C:\Temp\test.xlsx"

C = Array("abc", "def", "lmn")  'Sheetnames in the workbook test.xlsx
P = Array("jkl", "fgh", "xyz")  'Sheetnames in the workbook Book1.xlsm

For i = 0 To 2
    Windows("test.xlsx").Activate
    Sheets(C(i)).Activate
    Cells.Copy
    Windows("Book1.xlsm").Activate
    Sheets(P(i)).Activate
    Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Next i

End Sub

 

[jim may]

Welcome to the Board!

That is not going to work, as C will be the literal string C1, not equal to variable C1.

Also, you need to declare each variable explicitly.
In this statement:

P would be dimmed a Worksheet, but C is dimmed as Variant

Use Arrays to loop through your sheets. Note that Arrays are 0 based by default. So to loop through three values, you would go from 0 to 2.
So try this code:

Sub Macro1()
'
' Macro1 Macro
'

Dim C As Variant, P As Variant
Dim i As Integer

Workbooks.Open Filename:="C:\Temp\test.xlsx"

C = Array("abc", "def", "lmn")  'Sheetnames in the workbook test.xlsx
P = Array("jkl", "fgh", "xyz")  'Sheetnames in the workbook Book1.xlsm

For i = 0 To 2
    Windows("test.xlsx").Activate
    Sheets(C(i)).Activate
    Cells.Copy
    Windows("Book1.xlsm").Activate
    Sheets(P(i)).Activate
    Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Next i

End Sub

What if Book1.xlsm is NOT open before running this Macro?

 

[Joe4]

What if Book1.xlsm is NOT open before running this Macro?

Putting on my detective hat, I am assuming that this Macro is located in that particular workbook, because it has a .xlsm extension.
If that is the case, they wouldn't have access to this VBA code unless the workbook was open, which kind of makes it a moot point.

If it is otherwise, I am sure that they will let us know.
In that case, you just need to add code to open the workbook and move around between the correct workbooks.
It is not hard, that code already exists in the current code to open the other file and move between them. So it wouldn't be introducing any new VBA concepts.

 

[jim may]

Thanks Joe

 

[imgs1607]

Welcome to the Board!

That is not going to work, as C will be the literal string C1, not equal to variable C1.

Also, you need to declare each variable explicitly.
In this statement:

P would be dimmed a Worksheet, but C is dimmed as Variant

Use Arrays to loop through your sheets. Note that Arrays are 0 based by default. So to loop through three values, you would go from 0 to 2.
So try this code:

Sub Macro1()
'
' Macro1 Macro
'

Dim C As Variant, P As Variant
Dim i As Integer

Workbooks.Open Filename:="C:\Temp\test.xlsx"

C = Array("abc", "def", "lmn")  'Sheetnames in the workbook test.xlsx
P = Array("jkl", "fgh", "xyz")  'Sheetnames in the workbook Book1.xlsm

For i = 0 To 2
    Windows("test.xlsx").Activate
    Sheets(C(i)).Activate
    Cells.Copy
    Windows("Book1.xlsm").Activate
    Sheets(P(i)).Activate
    Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Next i

End Sub

Thanks Joe, this works perfect. Thanks so much for the help

 

[Joe4]

You are welcome.