Selection.Copy used in a variable.
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 '====== -- Regards VBA.Noob.Confused XP Pro Office 2007 |
Selection.Copy used in a variable.
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 "Rick S." wrote: 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 '====== -- Regards VBA.Noob.Confused XP Pro Office 2007 |
Selection.Copy used in a variable.
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 "Rick S." wrote: 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 '====== -- Regards VBA.Noob.Confused XP Pro Office 2007 |
Selection.Copy used in a variable.
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.Cou nt)).Range("A1") end with -- HTH... Jim Thomlinson "Rick S." wrote: 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 '====== -- Regards VBA.Noob.Confused XP Pro Office 2007 |
Selection.Copy used in a variable.
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! -- Regards VBA.Noob.Confused XP Pro Office 2007 "Mike H" wrote: 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 "Rick S." wrote: 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 '====== -- Regards VBA.Noob.Confused XP Pro Office 2007 |
Selection.Copy used in a variable.
I was afraid this was true.
Thanks for your help! -- Regards VBA.Noob.Confused XP Pro Office 2007 "Jim Thomlinson" wrote: 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.Cou nt)).Range("A1") end with -- HTH... Jim Thomlinson "Rick S." wrote: 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 '====== -- Regards VBA.Noob.Confused XP Pro Office 2007 |
All times are GMT +1. The time now is 11:39 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com