Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Range, Paste Value in New Workbook
Hi All,
I am sure this is not as difficult as my man flu addled brain is making it. I have a number of worksheets in a workbook that have the relevant information I want to copy in the same range (A71:I71). I then have a seperate workbook that I want to paste the values onto the next blank row, so I can make a summary page. The following code is what I have cobbled together from various posts (Kudos to those who may recognise bits of their code! I know some of it came from Ron db) The source ranges will be present on all of the worksheets within the workbook but will all have different names and the number of worksheets will differ everytime this macro is run. The code seems to fall down as it only copy & pastes from the first worksheet and then seems to ignore the rest! The source worksheets are in a password protected workbook (that bit works fine!) Any help greatly appreciated. Sub CopyDetails() Dim wbk As Workbook Dim sh As Worksheet Dim Sourcewb As Workbook Set wbk = Workbooks.Open(Filename:="filelocation", UpdateLinks:=True, Password:="password") Set Sourcewb = ThisWorkbook For Each sh In Sourcewb.Worksheets Range("A71:I71").Copy Destination:=Workbooks("Report").Worksheets("TM Overview").Cells(Rows.Count, 1).End(xlUp)(2) Next sh End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Range, Paste Value in New Workbook
Make sure you pick up the values in the correct sheet:
Sh.Range("A71:I71").Copy _ Destination:=Workbooks("Report").Worksheets("TM Overview") _ .Cells(Rows.Count, 1).End(xlUp)(2) Those unqualified ranges will point at the active sheet. BaggieDan wrote: Hi All, I am sure this is not as difficult as my man flu addled brain is making it. I have a number of worksheets in a workbook that have the relevant information I want to copy in the same range (A71:I71). I then have a seperate workbook that I want to paste the values onto the next blank row, so I can make a summary page. The following code is what I have cobbled together from various posts (Kudos to those who may recognise bits of their code! I know some of it came from Ron db) The source ranges will be present on all of the worksheets within the workbook but will all have different names and the number of worksheets will differ everytime this macro is run. The code seems to fall down as it only copy & pastes from the first worksheet and then seems to ignore the rest! The source worksheets are in a password protected workbook (that bit works fine!) Any help greatly appreciated. Sub CopyDetails() Dim wbk As Workbook Dim sh As Worksheet Dim Sourcewb As Workbook Set wbk = Workbooks.Open(Filename:="filelocation", UpdateLinks:=True, Password:="password") Set Sourcewb = ThisWorkbook For Each sh In Sourcewb.Worksheets Range("A71:I71").Copy Destination:=Workbooks("Report").Worksheets("TM Overview").Cells(Rows.Count, 1).End(xlUp)(2) Next sh End Sub -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Range, Paste Value in New Workbook
Thank you sir,
2 little letters sorted it! "Dave Peterson" wrote: Make sure you pick up the values in the correct sheet: Sh.Range("A71:I71").Copy _ Destination:=Workbooks("Report").Worksheets("TM Overview") _ .Cells(Rows.Count, 1).End(xlUp)(2) Those unqualified ranges will point at the active sheet. BaggieDan wrote: Hi All, I am sure this is not as difficult as my man flu addled brain is making it. I have a number of worksheets in a workbook that have the relevant information I want to copy in the same range (A71:I71). I then have a seperate workbook that I want to paste the values onto the next blank row, so I can make a summary page. The following code is what I have cobbled together from various posts (Kudos to those who may recognise bits of their code! I know some of it came from Ron db) The source ranges will be present on all of the worksheets within the workbook but will all have different names and the number of worksheets will differ everytime this macro is run. The code seems to fall down as it only copy & pastes from the first worksheet and then seems to ignore the rest! The source worksheets are in a password protected workbook (that bit works fine!) Any help greatly appreciated. Sub CopyDetails() Dim wbk As Workbook Dim sh As Worksheet Dim Sourcewb As Workbook Set wbk = Workbooks.Open(Filename:="filelocation", UpdateLinks:=True, Password:="password") Set Sourcewb = ThisWorkbook For Each sh In Sourcewb.Worksheets Range("A71:I71").Copy Destination:=Workbooks("Report").Worksheets("TM Overview").Cells(Rows.Count, 1).End(xlUp)(2) Next sh End Sub -- Dave Peterson . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to copy a range of cells and paste into a new workbook in differentcolumns | Excel Discussion (Misc queries) | |||
Column range to search from a source workbook and then copy/paste | Excel Programming | |||
Copy paste a range from one Workbook to another | Excel Programming | |||
Need a macro to copy a range in one workbook and paste into another workbook | Excel Programming | |||
Copy a range of cells in an unopened workbook and paste it to the current workbook | Excel Programming |