Selection.Copy used in a variable.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rick S. - 24 Jan 2008 19:29 GMT

How do I pass the variable "vData"?
'======
lRow = Range("A1").End(xlDown).Address
'MsgBox lRow 'for testing
vData = Range("A1:" & lRow).Copy
With Workbooks("TEMPLATE.xlsx")
.Worksheets.Add After:=.Worksheets(.Worksheets.Count)
vData.Paste 'Object required?
End With
'======

Signature

Regards

VBA.Noob.Confused
XP Pro
Office 2007

Reply to this Message

Mike H - 24 Jan 2008 19:58 GMT

Rick,

Maybe

Sub jj()
lRow = Range("A1").End(xlDown).Address
'MsgBox lRow 'for testing
vData = Range("A1:" & lRow).Copy
With ActiveWorkbook
.Worksheets.Add After:=.Worksheets(.Worksheets.Count)
ActiveSheet.Paste 'Object required?
End With
End Sub

Mike

> How do I pass the variable "vData"?
> '======
[quoted text clipped - 6 lines]
> End With
> '======

Reply to this Message

Rick S. - 24 Jan 2008 20:30 GMT

Activating the worksheet seemed to be one key, your bit of code also revealed
another key.

This is what I ended up with that works.
'======
lRow = Range("A1").End(xlDown).Address
Sheets(1).Range("A1:" & lRow).Copy
'need to get new workbook name as variable 01.24.08
With Workbooks("TEMPLATE.xlsx")
.Worksheets.Add After:=.Worksheets(.Worksheets.Count)
Workbooks("TEMPLATE.xlsx").Activate 'activate workbook
ActiveSheet.Paste 'paste data from workbook "ListA"
End With
ActiveWindow.SelectedSheets.Visible = False
Workbooks("ListA.xlsx").Close
'======

Thanks for your help!

Signature

Regards

VBA.Noob.Confused
XP Pro
Office 2007

> Rick,
>
[quoted text clipped - 22 lines]
> > End With
> > '======

Reply to this Message

Mike H - 24 Jan 2008 20:00 GMT

Rick,

I forgot to mention that I changed
With Workbooks("TEMPLATE.xlsx")
to
With ActiveWorkbook

For easier testing but it shouldn't make any difference when you change it
back

Mike

> How do I pass the variable "vData"?
> '======
[quoted text clipped - 6 lines]
> End With
> '======

Reply to this Message

Jim Thomlinson - 24 Jan 2008 20:04 GMT

You do not copy cells to a variable and then pass the variable. Try something
more like this...

With Workbooks("TEMPLATE.xlsx")
Range("A1", Cells(Rows.Count, "A").End(xlUp)).Copy _
.Worksheets.Add(After:=.Worksheets(.Worksheets.Count)).Range("A1")
end with

Signature

HTH...

Jim Thomlinson

> How do I pass the variable "vData"?
> '======
[quoted text clipped - 6 lines]
> End With
> '======

Reply to this Message

Rick S. - 24 Jan 2008 20:31 GMT

I was afraid this was true.
Thanks for your help!

Signature

Regards

VBA.Noob.Confused
XP Pro
Office 2007

> You do not copy cells to a variable and then pass the variable. Try something
> more like this...
[quoted text clipped - 14 lines]
> > End With
> > '======

Reply to this Message