Can I use variable as sheetname (VBA)

Question

I am a newcomer to VBA. I am trying to copy selected range from different workbooks and pasted to a target workbook with different sheetname correspondingly to the name of source file.

The code as below:

'open file

Sub RstChk()
Dim StrFileName As String
Dim StrFilePath As String
Dim TimeStr As String
Dim Version As Integer
Dim x As Workbook
Dim y As Workbook
Dim PstTgt As String

'define filename as array

Dim FN(10) As String

FN(1) = "CIO Wholesale"
FN(2) = "RMG"
FN(3) = "DCM"
FN(4) = "DivHeadOth"
FN(5) = "Runoff"
FN(6) = "Other Risk Subs"
FN(7) = "FIC"
FN(8) = "Treasury"
FN(9) = "Cash Equities"
FN(10) = "Global Derivatives"

'define file path

StrFilePath = "V:\RISKMIS\PUBLIC\apps\MORNING\RMU 1.5 Report\Consolidated\"

'define TimeStr

TimeStr = Format(Now() - 1, "mm-dd-yyyy")

Set y = Workbooks.Open("H:\Eform\Report_checking.xls")

'applying filename from array using loop
'----------------------------------------------------------------
For i = 1 To 10

'define changing file name with path & loop

For Version = 65 To 68

StrFileName = (StrFilePath & FN(i) & "_" & TimeStr & "_" & Chr(Version) & ".xls")

Set x = Workbooks.Open(StrFileName)

'-------------------------------------------------
If Chr(Version) = "A" Then
PstTgt = "A3"

ElseIf Chr(Version) = "B" Then
PstTgt = "E3"


ElseIf Chr(Version) = "C" Then
PstTgt = "I3"

Else

PstTgt = "M3"

End If

'copy the column and paste to report checking

y.Worksheets(FN(i)).PstTgt.Copy Destination = x.Sheets("Risk Summary").Range    ("AA5:AC118")


Application.CutCopyMode = False

x.Close

Next Version

Next i

End Sub

I get error when I try to copy the range from source file (x) to target file (Y).

Run-time error '13', type mismatch

Just can't figure out what went wrong.

Thanks very much for your help.

Dan

score 0 · Answer 1 · answered Aug 04 '15 at 20:32

You got this error because your variable PstTgt is a string and not a range "type mismatch"

If you look at the documentation of Range.Copy https://msdn.microsoft.com/en-us/library/office/ff837760.aspx

You have two choices :

Make PstTgt a range and referencing directly to the range in your endif

' Redefine PstTgt as a range
dim PstTgt as Range

' set value of PstTgt
If Chr(Version) = "A" Then
    set PstTgt = y.Worksheets(FN(i)).Range("A3")
endif
...
' Copy the range where you want
PstTgt.Copy destination:=x.Sheets("Risk Summary").Range("AA5")

You keep your code like that and just correct your copy by adding Range

y.Worksheets(FN(i)).Range(PstTgt).Copy Destination = x.Sheets("Risk Summary").Range("AA5")

Can I use variable as sheetname (VBA)

1 Answers1