Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Selection - Paste Selection - Delete Selection | Excel Programming | |||
Copy Selection - Transpose Selection - Delete Selection | Excel Discussion (Misc queries) | |||
Using a variable to determine row selection | Excel Programming | |||
Selection of variable rows | Excel Programming | |||
Variable for column selection | Excel Programming |