Range Concatenation Help Needed

[JeffL]

I'm at my wits end over this, I've spent hours trying everything except the right thing :banghead:

In this statment -
With ThisWorkbook.Worksheets("StreetSmart").Range("A1:P2")

I need to use a variable in place of the "2" where you see A1:P2.

Can anyone help?

[sassora]

ThisWorkbook.Worksheets("StreetSmart").Range("A1:P" & variablename

[mdmackillop]

You can also use the Cells method

ThisWorkbook.Worksheets("StreetSmart").Range(Cells(1, 1), Cells(1, variablename)).Select

[JeffL]

Thanks for the suggestions, but I'm still having a problem.
I'm trying to copy the cell values of one worksheet to another.

When I use this code (that I found in a forum), with all literals, it works fine:


Mydata = "='C:\jeff\temp\[StreetSmart.xls]StreetSmart'!$A$1:$P$54"
With ThisWorkbook.Worksheets("StreetSmart").Range("A1:P54")
'Load Formulas into old price spreadsheet in the main workbook
.Formula = Mydata
'Convert formulas to values
.Value = .Value
End With

But when I use a variable in place of 54 with this code:


Mydata = "='C:\jeff\temp\[StreetSmart.xls]StreetSmart'!$A$1:$P$" & PriceRow & """"
'With ThisWorkbook.Worksheets("StreetSmart").Range("A1:P" & PriceRow)
'Load Formulas into old price spreadsheet in the main workbook
.Formula = Mydata
'Convert formulas to values
.Value = .Value
End With

I get the following message at the .Formula = Mydata line:

Run Time Error 1004
Application-defined or Object-defined error.

[sassora]

Try this instead:

Mydata = "='C:\jeff\temp\[StreetSmart.xls]StreetSmart'!$A$1:$P$" & PriceRow

[mdmackillop]

With this sort of issue, look carefully at the string value as in

MsgBox "'C:\jeff\temp\[StreetSmart.xls]StreetSmart'!$A$1:$P$" & Pricerow & """"

In this case you'll see an extra double quote at the end.

[JeffL]

It's Snoopy Happy Dance time! :thumb

You guys are great. I'd been struggling with this for 3 nights. It's too complicated for an old COBOL programmer :bug:

What I'd done in the past is a "Move or Copy" sheet from one workbook to another, but I got a new machine with Win 8 and Office 2013, and it wouldn't let me Move a new sheet to my old Excel 2000 workbook. My new macro get's me the results I need with less mouse clicking.

Thanks again :hi: