Abbreviate Workbook/Sheet/range
 

I want refer a W/S/R in the shortest possible way because of the number of
repetitions. At the end I want:
C_str.Copy: P_str.PasteSpecial Paste:=xlPasteValues

What I have so far is (and I think that C_str as String may be incorrect):

Dim C_str As String, P_str As String, C_bk As Workbook, C_sh As
Worksheet, C_rng As Range, P_bk As Workbook, P_sh As Worksheet, P_rng As Range

Set C_bk = Workbooks("1. FINANCE.xls"): Set C_sh = Sheets("Refinance"):
Set C_rng = Range("L201")

Set P_bk = Workbooks("1. FINANCE.xls"): Set P_sh = Sheets("Refinance"):
Set P_rng = Range("L202")
Regards, Brett

Abbreviate Workbook/Sheet/range
 

This is the correct way

Dim C_str As String, P_str As String, C_bk As Workbook, C_sh As
Worksheet, C_rng As Range, P_bk As Workbook, P_sh As Worksheet, P_rng As Range

Set C_bk = Workbooks("1. FINANCE.xls")
Set C_sh = C_bk.Sheets("Refinance")
Set C_rng = C_sh.Range("L201")

Set P_bk = Workbooks("1. FINANCE.xls")
Set P_sh = P_bk.Sheets("Refinance")
Set P_rng = P_sh.Range("L202")




"Brettjg" wrote:

I want refer a W/S/R in the shortest possible way because of the number of
repetitions. At the end I want:
C_str.Copy: P_str.PasteSpecial Paste:=xlPasteValues

What I have so far is (and I think that C_str as String may be incorrect):

Dim C_str As String, P_str As String, C_bk As Workbook, C_sh As
Worksheet, C_rng As Range, P_bk As Workbook, P_sh As Worksheet, P_rng As Range

Set C_bk = Workbooks("1. FINANCE.xls"): Set C_sh = Sheets("Refinance"):
Set C_rng = Range("L201")

Set P_bk = Workbooks("1. FINANCE.xls"): Set P_sh = Sheets("Refinance"):
Set P_rng = Range("L202")
Regards, Brett

Abbreviate Workbook/Sheet/range
 

Hi Joel, absolute cracker! That will save me tens of thousands of characters
in PERSONAL.XLS.
TVM, Brett

"Joel" wrote:

This is the correct way

Dim C_str As String, P_str As String, C_bk As Workbook, C_sh As
Worksheet, C_rng As Range, P_bk As Workbook, P_sh As Worksheet, P_rng As Range

Set C_bk = Workbooks("1. FINANCE.xls")
Set C_sh = C_bk.Sheets("Refinance")
Set C_rng = C_sh.Range("L201")

Set P_bk = Workbooks("1. FINANCE.xls")
Set P_sh = P_bk.Sheets("Refinance")
Set P_rng = P_sh.Range("L202")




"Brettjg" wrote:

I want refer a W/S/R in the shortest possible way because of the number of
repetitions. At the end I want:
C_str.Copy: P_str.PasteSpecial Paste:=xlPasteValues

What I have so far is (and I think that C_str as String may be incorrect):

Dim C_str As String, P_str As String, C_bk As Workbook, C_sh As
Worksheet, C_rng As Range, P_bk As Workbook, P_sh As Worksheet, P_rng As Range

Set C_bk = Workbooks("1. FINANCE.xls"): Set C_sh = Sheets("Refinance"):
Set C_rng = Range("L201")

Set P_bk = Workbooks("1. FINANCE.xls"): Set P_sh = Sheets("Refinance"):
Set P_rng = Range("L202")
Regards, Brett