Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy range to closed workbook
Hi
I'm trying to copy a range from the issue worksheet which is in the open workbook to the Issue workbook. The sheet name is from a dynamic formula on the lookuplists sheet which is open but hidden. It fails on the set Destrange line. Thanks Wendy Sub Movepost() Dim DestWB As Workbook Dim SourceRange As Range Dim DestRange As Range Dim WS As Worksheet Dim LRow As Integer Dim DestRow As Integer Dim DestWs As Worksheet Dim Destwsname As String Set WS = Sheets("LookupLists") Destwsname = Range("C1").Value 'week number and day eg W1Mon Set WS = Nothing Set WS = Worksheets("Issue") LRow = WS.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row - 1 Set SourceRange = ThisWorkbook.Worksheets("Issue").Range("A2:B" & LRow) SourceRange.Copy Set DestRange = Workbooks("d:\Issue.xls").Worksheets(Destwsname).R ange("A" & LRow) DestRange.PasteSpecial xlPasteValues, , False, False End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy range to closed workbook
Why not open the destination workbook first and then do the copy?
-- Gary''s Student - gsnu200778 "Wendy" wrote: Hi I'm trying to copy a range from the issue worksheet which is in the open workbook to the Issue workbook. The sheet name is from a dynamic formula on the lookuplists sheet which is open but hidden. It fails on the set Destrange line. Thanks Wendy Sub Movepost() Dim DestWB As Workbook Dim SourceRange As Range Dim DestRange As Range Dim WS As Worksheet Dim LRow As Integer Dim DestRow As Integer Dim DestWs As Worksheet Dim Destwsname As String Set WS = Sheets("LookupLists") Destwsname = Range("C1").Value 'week number and day eg W1Mon Set WS = Nothing Set WS = Worksheets("Issue") LRow = WS.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row - 1 Set SourceRange = ThisWorkbook.Worksheets("Issue").Range("A2:B" & LRow) SourceRange.Copy Set DestRange = Workbooks("d:\Issue.xls").Worksheets(Destwsname).R ange("A" & LRow) DestRange.PasteSpecial xlPasteValues, , False, False End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy range to closed workbook
The first thing is that the issue.xls workbook has to be open for this to run.
The second thing is that you refer to (the already open workbook) like: Set DestRange = Workbooks("Issue.xls").Worksheets(Destwsname).Rang e("A" & LRow) (don't include the drive or path in the workbooks()) And it looks like you're overwriting the last cell. LRow = WS.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row - 1 This goes from the bottom of column A to the last used cell in column A. Then drops down (with the .offset(1,0), but then you subtract 1. If you really wanted that, you could use: LRow = WS.Cells(Rows.Count, 1).End(xlUp).Row But I would think you'd want: LRow = WS.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row (the next available row) ===== If you really wanted lRow to be the last used row, then you could change this: Set DestRange _ = Workbooks("Issue.xls").Worksheets(Destwsname).Rang e("A" & LRow) to Set DestRange _ = Workbooks("Issue.xls").Worksheets(Destwsname).Rang e("A" & LRow + 1) Wendy wrote: Hi I'm trying to copy a range from the issue worksheet which is in the open workbook to the Issue workbook. The sheet name is from a dynamic formula on the lookuplists sheet which is open but hidden. It fails on the set Destrange line. Thanks Wendy Sub Movepost() Dim DestWB As Workbook Dim SourceRange As Range Dim DestRange As Range Dim WS As Worksheet Dim LRow As Integer Dim DestRow As Integer Dim DestWs As Worksheet Dim Destwsname As String Set WS = Sheets("LookupLists") Destwsname = Range("C1").Value 'week number and day eg W1Mon Set WS = Nothing Set WS = Worksheets("Issue") LRow = WS.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row - 1 Set SourceRange = ThisWorkbook.Worksheets("Issue").Range("A2:B" & LRow) SourceRange.Copy Set DestRange = Workbooks("d:\Issue.xls").Worksheets(Destwsname).R ange("A" & LRow) DestRange.PasteSpecial xlPasteValues, , False, False End Sub -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy range to closed workbook
Hi
The Destrange statement doesn't work when the issue workbook is open or closed, with or without the drivepath - I tried both. On the LRow I only want the data that is currently there copying to the issue workbook and choosing the correct worksheet name. Wendy "Dave Peterson" wrote in message ... The first thing is that the issue.xls workbook has to be open for this to run. The second thing is that you refer to (the already open workbook) like: Set DestRange = Workbooks("Issue.xls").Worksheets(Destwsname).Rang e("A" & LRow) (don't include the drive or path in the workbooks()) And it looks like you're overwriting the last cell. LRow = WS.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row - 1 This goes from the bottom of column A to the last used cell in column A. Then drops down (with the .offset(1,0), but then you subtract 1. If you really wanted that, you could use: LRow = WS.Cells(Rows.Count, 1).End(xlUp).Row But I would think you'd want: LRow = WS.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row (the next available row) ===== If you really wanted lRow to be the last used row, then you could change this: Set DestRange _ = Workbooks("Issue.xls").Worksheets(Destwsname).Rang e("A" & LRow) to Set DestRange _ = Workbooks("Issue.xls").Worksheets(Destwsname).Rang e("A" & LRow + 1) Wendy wrote: Hi I'm trying to copy a range from the issue worksheet which is in the open workbook to the Issue workbook. The sheet name is from a dynamic formula on the lookuplists sheet which is open but hidden. It fails on the set Destrange line. Thanks Wendy Sub Movepost() Dim DestWB As Workbook Dim SourceRange As Range Dim DestRange As Range Dim WS As Worksheet Dim LRow As Integer Dim DestRow As Integer Dim DestWs As Worksheet Dim Destwsname As String Set WS = Sheets("LookupLists") Destwsname = Range("C1").Value 'week number and day eg W1Mon Set WS = Nothing Set WS = Worksheets("Issue") LRow = WS.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row - 1 Set SourceRange = ThisWorkbook.Worksheets("Issue").Range("A2:B" & LRow) SourceRange.Copy Set DestRange = Workbooks("d:\Issue.xls").Worksheets(Destwsname).R ange("A" & LRow) DestRange.PasteSpecial xlPasteValues, , False, False End Sub -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy range to closed workbook
First, don't include the drive/path.
Set DestRange = Workbooks("Issue.xls").Worksheets(Destwsname).Rang e("A" & LRow) So there's only a few things that can go wrong with this statement. One, issue.xls isn't open. Second, issue.xls doesn't have a worksheet that's named destwsname. Maybe it doesn't exist--or maybe there's a typo (leading/trailing space or 0 or o mixup???). Wendy wrote: Hi The Destrange statement doesn't work when the issue workbook is open or closed, with or without the drivepath - I tried both. On the LRow I only want the data that is currently there copying to the issue workbook and choosing the correct worksheet name. Wendy "Dave Peterson" wrote in message ... The first thing is that the issue.xls workbook has to be open for this to run. The second thing is that you refer to (the already open workbook) like: Set DestRange = Workbooks("Issue.xls").Worksheets(Destwsname).Rang e("A" & LRow) (don't include the drive or path in the workbooks()) And it looks like you're overwriting the last cell. LRow = WS.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row - 1 This goes from the bottom of column A to the last used cell in column A. Then drops down (with the .offset(1,0), but then you subtract 1. If you really wanted that, you could use: LRow = WS.Cells(Rows.Count, 1).End(xlUp).Row But I would think you'd want: LRow = WS.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row (the next available row) ===== If you really wanted lRow to be the last used row, then you could change this: Set DestRange _ = Workbooks("Issue.xls").Worksheets(Destwsname).Rang e("A" & LRow) to Set DestRange _ = Workbooks("Issue.xls").Worksheets(Destwsname).Rang e("A" & LRow + 1) Wendy wrote: Hi I'm trying to copy a range from the issue worksheet which is in the open workbook to the Issue workbook. The sheet name is from a dynamic formula on the lookuplists sheet which is open but hidden. It fails on the set Destrange line. Thanks Wendy Sub Movepost() Dim DestWB As Workbook Dim SourceRange As Range Dim DestRange As Range Dim WS As Worksheet Dim LRow As Integer Dim DestRow As Integer Dim DestWs As Worksheet Dim Destwsname As String Set WS = Sheets("LookupLists") Destwsname = Range("C1").Value 'week number and day eg W1Mon Set WS = Nothing Set WS = Worksheets("Issue") LRow = WS.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row - 1 Set SourceRange = ThisWorkbook.Worksheets("Issue").Range("A2:B" & LRow) SourceRange.Copy Set DestRange = Workbooks("d:\Issue.xls").Worksheets(Destwsname).R ange("A" & LRow) DestRange.PasteSpecial xlPasteValues, , False, False End Sub -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy range to closed workbook
Hi Dave
Destwsname is a value, as shown in my original sub. Set WS = Sheets("LookupLists") Destwsname = Range("C1").Value 'week number and day eg W1Mon It fails if my issue.xls is open or closed it still doesn't work, even when I give the actual sheetname rather than the variable name. This is the highlighted line Set DestRange = Workbooks("Issue.xls").Worksheets("W2Fri") Run time error 13 type mismatch. I need the value from destwsname to become the worksheet name as it changes daily. Wendy "Dave Peterson" wrote in message ... First, don't include the drive/path. Set DestRange = Workbooks("Issue.xls").Worksheets(Destwsname).Rang e("A" & LRow) So there's only a few things that can go wrong with this statement. One, issue.xls isn't open. Second, issue.xls doesn't have a worksheet that's named destwsname. Maybe it doesn't exist--or maybe there's a typo (leading/trailing space or 0 or o mixup???). Wendy wrote: Hi The Destrange statement doesn't work when the issue workbook is open or closed, with or without the drivepath - I tried both. On the LRow I only want the data that is currently there copying to the issue workbook and choosing the correct worksheet name. Wendy "Dave Peterson" wrote in message ... The first thing is that the issue.xls workbook has to be open for this to run. The second thing is that you refer to (the already open workbook) like: Set DestRange = Workbooks("Issue.xls").Worksheets(Destwsname).Rang e("A" & LRow) (don't include the drive or path in the workbooks()) And it looks like you're overwriting the last cell. LRow = WS.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row - 1 This goes from the bottom of column A to the last used cell in column A. Then drops down (with the .offset(1,0), but then you subtract 1. If you really wanted that, you could use: LRow = WS.Cells(Rows.Count, 1).End(xlUp).Row But I would think you'd want: LRow = WS.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row (the next available row) ===== If you really wanted lRow to be the last used row, then you could change this: Set DestRange _ = Workbooks("Issue.xls").Worksheets(Destwsname).Rang e("A" & LRow) to Set DestRange _ = Workbooks("Issue.xls").Worksheets(Destwsname).Rang e("A" & LRow + 1) Wendy wrote: Hi I'm trying to copy a range from the issue worksheet which is in the open workbook to the Issue workbook. The sheet name is from a dynamic formula on the lookuplists sheet which is open but hidden. It fails on the set Destrange line. Thanks Wendy Sub Movepost() Dim DestWB As Workbook Dim SourceRange As Range Dim DestRange As Range Dim WS As Worksheet Dim LRow As Integer Dim DestRow As Integer Dim DestWs As Worksheet Dim Destwsname As String Set WS = Sheets("LookupLists") Destwsname = Range("C1").Value 'week number and day eg W1Mon Set WS = Nothing Set WS = Worksheets("Issue") LRow = WS.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row - 1 Set SourceRange = ThisWorkbook.Worksheets("Issue").Range("A2:B" & LRow) SourceRange.Copy Set DestRange = Workbooks("d:\Issue.xls").Worksheets(Destwsname).R ange("A" & LRow) DestRange.PasteSpecial xlPasteValues, , False, False End Sub -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy range to closed workbook
You dropped the range portion:
Set DestRange _ = Workbooks("Issue.xls").Worksheets(Destwsname).Rang e("A" & LRow) To test: Set DestRange _ = Workbooks("Issue.xls").Worksheets("W2Fri").Range(" A1") And after you add the range portion and if issue.xls is open, then there is no worksheet with that name. I'd still look for spelling differences. Wendy wrote: Hi Dave Destwsname is a value, as shown in my original sub. Set WS = Sheets("LookupLists") Destwsname = Range("C1").Value 'week number and day eg W1Mon It fails if my issue.xls is open or closed it still doesn't work, even when I give the actual sheetname rather than the variable name. This is the highlighted line Set DestRange = Workbooks("Issue.xls").Worksheets("W2Fri") Run time error 13 type mismatch. I need the value from destwsname to become the worksheet name as it changes daily. Wendy "Dave Peterson" wrote in message ... First, don't include the drive/path. Set DestRange = Workbooks("Issue.xls").Worksheets(Destwsname).Rang e("A" & LRow) So there's only a few things that can go wrong with this statement. One, issue.xls isn't open. Second, issue.xls doesn't have a worksheet that's named destwsname. Maybe it doesn't exist--or maybe there's a typo (leading/trailing space or 0 or o mixup???). Wendy wrote: Hi The Destrange statement doesn't work when the issue workbook is open or closed, with or without the drivepath - I tried both. On the LRow I only want the data that is currently there copying to the issue workbook and choosing the correct worksheet name. Wendy "Dave Peterson" wrote in message ... The first thing is that the issue.xls workbook has to be open for this to run. The second thing is that you refer to (the already open workbook) like: Set DestRange = Workbooks("Issue.xls").Worksheets(Destwsname).Rang e("A" & LRow) (don't include the drive or path in the workbooks()) And it looks like you're overwriting the last cell. LRow = WS.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row - 1 This goes from the bottom of column A to the last used cell in column A. Then drops down (with the .offset(1,0), but then you subtract 1. If you really wanted that, you could use: LRow = WS.Cells(Rows.Count, 1).End(xlUp).Row But I would think you'd want: LRow = WS.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row (the next available row) ===== If you really wanted lRow to be the last used row, then you could change this: Set DestRange _ = Workbooks("Issue.xls").Worksheets(Destwsname).Rang e("A" & LRow) to Set DestRange _ = Workbooks("Issue.xls").Worksheets(Destwsname).Rang e("A" & LRow + 1) Wendy wrote: Hi I'm trying to copy a range from the issue worksheet which is in the open workbook to the Issue workbook. The sheet name is from a dynamic formula on the lookuplists sheet which is open but hidden. It fails on the set Destrange line. Thanks Wendy Sub Movepost() Dim DestWB As Workbook Dim SourceRange As Range Dim DestRange As Range Dim WS As Worksheet Dim LRow As Integer Dim DestRow As Integer Dim DestWs As Worksheet Dim Destwsname As String Set WS = Sheets("LookupLists") Destwsname = Range("C1").Value 'week number and day eg W1Mon Set WS = Nothing Set WS = Worksheets("Issue") LRow = WS.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row - 1 Set SourceRange = ThisWorkbook.Worksheets("Issue").Range("A2:B" & LRow) SourceRange.Copy Set DestRange = Workbooks("d:\Issue.xls").Worksheets(Destwsname).R ange("A" & LRow) DestRange.PasteSpecial xlPasteValues, , False, False End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy from closed workbook again! ;( | Excel Programming | |||
Copy data from a closed workbook (ADO) | Excel Programming | |||
Possible to copy sheets into another (closed!) workbook? | Excel Discussion (Misc queries) | |||
copy worksheet from closed workbook to active workbook using vba | Excel Worksheet Functions | |||
How to copy a range to a closed workbook | Excel Programming |