|
Need a macro to copy a range in one workbook and paste into another workbook
Hi all,
I'm experienced in excel, but not very good with Macros. I am trying to write a macro to copy from 1 workbook into another. Using Window 2K SP4, and MS Office 2k SR1 So far I have only this using the Macro Recorder: Sub ITHDdocID() ' ' ITHDdocID Macro ' Macro recorded 25/06/2004 by husky101 ' ' Windows("ITHD Reports.xls").Activate Range("A6").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Windows("ITHD Monthly Calculator for reports V5.xls").Activate ActiveSheet.Paste Range("B3").Select End Sub When I run this macro I receive a 'Subscript out of range error', on line 5 the destination wkbk. I need to copy an unkown range down from ITHD!A6 from wkbk (ITHD Reports.xls) and paste into wkbk2 (ITHD Monthly Calculator for reports V5.xls) into sheet Time!A3 Any assistance will be greatly appreciated. Cheers Paul |
Need a macro to copy a range in one workbook and paste into another workbook
Hi.
I fixed your macro. Is it a macro what you would like to write? Sub ITHDdocID() Dim rng As Range Windows("ITHD Reports.xls").Activate Worksheets("ITHD").Range("A6").Select 'selected from ITHD!A6 to the end Set rng = Range(Selection, Selection.End(xlDown)) rng.Select rng.Copy Windows("ITHD Monthly Calculator for reports V5.xls").Activate 'pasted to Time!A3 Worksheets("Time").Range("A3").PasteSpecial xlPasteValues End Sub -- Samura "Paul" wrote in message om... Hi all, I'm experienced in excel, but not very good with Macros. I am trying to write a macro to copy from 1 workbook into another. Using Window 2K SP4, and MS Office 2k SR1 So far I have only this using the Macro Recorder: Sub ITHDdocID() ' ' ITHDdocID Macro ' Macro recorded 25/06/2004 by husky101 ' ' Windows("ITHD Reports.xls").Activate Range("A6").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Windows("ITHD Monthly Calculator for reports V5.xls").Activate ActiveSheet.Paste Range("B3").Select End Sub When I run this macro I receive a 'Subscript out of range error', on line 5 the destination wkbk. I need to copy an unkown range down from ITHD!A6 from wkbk (ITHD Reports.xls) and paste into wkbk2 (ITHD Monthly Calculator for reports V5.xls) into sheet Time!A3 |
Need a macro to copy a range in one workbook and paste into another workbook
Try:
Sub ITHDdocID() Dim rng1 as Range Dim rng2 as Range rng1 = Workbooks("ITHD Reports").Sheets("Sheet1").Range(Range("A6"),Range ("A6").End(xlDown)) rng2 = Workbooks("ITHD Monthly Calculator for reports V5").Sheets("Sheet1").Range("B3").Resize(rng1.Rows .Count,1) rng2.Formula=rng1.Formula End Sub You may need to substitute the appropriate sheet names for "Sheet1" above - I can't tell what you've called them from your code fragment. Otherwise we can use Copy & Paste, but it's less efficient and bulletproof: Sub ITHDdocID() Dim NumOfRows as Integer Windows("ITHD Reports.xls").Activate With Range(Range("A6"), Range("A6").End(xlDown)) NumOfRows=.Rows.Count .Copy End With Windows("ITHD Monthly Calculator for reports V5.xls").Activate ActiveSheet.Range("B3").Resize(NumOfRows,1).Paste End Sub We store the number of rows in the region under A6 in the variable NumOfRows, then use it to measure out the range into which we're pasting. - Ken "Paul" wrote in message om... Hi all, I'm experienced in excel, but not very good with Macros. I am trying to write a macro to copy from 1 workbook into another. Using Window 2K SP4, and MS Office 2k SR1 So far I have only this using the Macro Recorder: Sub ITHDdocID() ' ' ITHDdocID Macro ' Macro recorded 25/06/2004 by husky101 ' ' Windows("ITHD Reports.xls").Activate Range("A6").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Windows("ITHD Monthly Calculator for reports V5.xls").Activate ActiveSheet.Paste Range("B3").Select End Sub When I run this macro I receive a 'Subscript out of range error', on line 5 the destination wkbk. I need to copy an unkown range down from ITHD!A6 from wkbk (ITHD Reports.xls) and paste into wkbk2 (ITHD Monthly Calculator for reports V5.xls) into sheet Time!A3 Any assistance will be greatly appreciated. Cheers Paul --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.708 / Virus Database: 464 - Release Date: 18/06/2004 |
Need a macro to copy a range in one workbook and paste into another workbook
Ken,
Thanks for your code. It worked great. One more thing if you wouldn't mind. Once I have my column in place, I need column B to run a vlookup. I can do the worksheet function no problems But I need it to run down the column as far as column A and no more as I run a Countif on col B, and 0 will give me an incorrect result. AS Column A can vary in length each report, i will either have to many rows or not enough. Too many will come up with 0 or errors. Any further assistance will be greatly appreciated, if you have the time. Cheers Paul "Ken Oliver" wrote in message ... Try: Sub ITHDdocID() Dim rng1 as Range Dim rng2 as Range rng1 = Workbooks("ITHD Reports").Sheets("Sheet1").Range(Range("A6"),Range ("A6").End(xlDown)) rng2 = Workbooks("ITHD Monthly Calculator for reports V5").Sheets("Sheet1").Range("B3").Resize(rng1.Rows .Count,1) rng2.Formula=rng1.Formula End Sub You may need to substitute the appropriate sheet names for "Sheet1" above - I can't tell what you've called them from your code fragment. Otherwise we can use Copy & Paste, but it's less efficient and bulletproof: Sub ITHDdocID() Dim NumOfRows as Integer Windows("ITHD Reports.xls").Activate With Range(Range("A6"), Range("A6").End(xlDown)) NumOfRows=.Rows.Count .Copy End With Windows("ITHD Monthly Calculator for reports V5.xls").Activate ActiveSheet.Range("B3").Resize(NumOfRows,1).Paste End Sub We store the number of rows in the region under A6 in the variable NumOfRows, then use it to measure out the range into which we're pasting. - Ken "Paul" wrote in message om... Hi all, I'm experienced in excel, but not very good with Macros. I am trying to write a macro to copy from 1 workbook into another. Using Window 2K SP4, and MS Office 2k SR1 So far I have only this using the Macro Recorder: Sub ITHDdocID() ' ' ITHDdocID Macro ' Macro recorded 25/06/2004 by husky101 ' ' Windows("ITHD Reports.xls").Activate Range("A6").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Windows("ITHD Monthly Calculator for reports V5.xls").Activate ActiveSheet.Paste Range("B3").Select End Sub When I run this macro I receive a 'Subscript out of range error', on line 5 the destination wkbk. I need to copy an unkown range down from ITHD!A6 from wkbk (ITHD Reports.xls) and paste into wkbk2 (ITHD Monthly Calculator for reports V5.xls) into sheet Time!A3 Any assistance will be greatly appreciated. Cheers Paul --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.708 / Virus Database: 464 - Release Date: 18/06/2004 |
Need a macro to copy a range in one workbook and paste into another workbook
Hi.
It may be late, I fixed that macro. This macro does not receive the error. The macro opens the book "ITHD Monthly Calculator for reports V5.xls", and pastes from "ITHD Reports.xls" to the book, and saves the book, and closes the book. Sub ITHDdocID() Dim rng As Range Dim pastedBook As Workbook Windows("ITHD Reports.xls").Activate Worksheets("ITHD").Range("A6").Select 'selected from ITHD!A6 to the end Set rng = Range(Selection, Selection.End(xlDown)) rng.Select rng.Copy 'opened the book "ITHD Monthly Calculator for reports V5.xls" Set pastedBook = Workbooks.Open("ITHD Monthly Calculator for reports V5.xls") 'pasted to Time!A3 pastedBook.Worksheets("Time").Range("A3").PasteSpe cial xlPasteValues 'saved the book "ITHD Monthly Calculator for reports V5.xls" pastedBook.Save 'closed the book "ITHD Monthly Calculator for reports V5.xls" pastedBook.Close Set pastedBook = Nothing End Sub -- Samura "Paul" wrote in message om... Thanks for you help Samura, But I receive an error on running the code. "Subscript out of range" on the line :"Windows("ITHD Monthly Calculator for reports V5.xls").Activate" But anyway I have found a solution. So Once again Thank you very much |
Need a macro to copy a range in one workbook and paste into another workbook
Thanks for your efforts Samura.
I still see errors for some reason, with your code. It cannot find "ITHD Monthly Calculator for reports V5.xls", I overcome this, then it comes up with Compile error on the SAVE. Maybe the differences are due to Excel Versions. I'm on XL 2K SR-1 Thanks again for the trouble. Cheers Paul "Samura" wrote in message ... Hi. It may be late, I fixed that macro. This macro does not receive the error. The macro opens the book "ITHD Monthly Calculator for reports V5.xls", and pastes from "ITHD Reports.xls" to the book, and saves the book, and closes the book. Sub ITHDdocID() Dim rng As Range Dim pastedBook As Workbook Windows("ITHD Reports.xls").Activate Worksheets("ITHD").Range("A6").Select 'selected from ITHD!A6 to the end Set rng = Range(Selection, Selection.End(xlDown)) rng.Select rng.Copy 'opened the book "ITHD Monthly Calculator for reports V5.xls" Set pastedBook = Workbooks.Open("ITHD Monthly Calculator for reports V5.xls") 'pasted to Time!A3 pastedBook.Worksheets("Time").Range("A3").PasteSpe cial xlPasteValues 'saved the book "ITHD Monthly Calculator for reports V5.xls" pastedBook.Save 'closed the book "ITHD Monthly Calculator for reports V5.xls" pastedBook.Close Set pastedBook = Nothing End Sub -- Samura "Paul" wrote in message om... Thanks for you help Samura, But I receive an error on running the code. "Subscript out of range" on the line :"Windows("ITHD Monthly Calculator for reports V5.xls").Activate" But anyway I have found a solution. So Once again Thank you very much |
Need a macro to copy a range in one workbook and paste into another workbook
Hi, Paul.
I could run that macro with no error. Paul Maybe the differences are due to Excel Versions. I'm on XL 2K SR-1 Excel that could run the macro was Excel 2000 (9.0.4402 SR-1) on the Windows 2000 SP4. Is your Excel's version same? Paul It cannot find "ITHD Monthly Calculator for reports V5.xls", Perhaps, that macro does not recognize the path. Then, how about trying to run this fixed macro? Sub ITHDdocID2() Dim rng As Range Dim pastedBook As Workbook Dim d As String Windows("ITHD Reports.xls").Activate Worksheets("ITHD").Range("A6").Select 'selected from ITHD!A6 to the end Set rng = Range(Selection, Selection.End(xlDown)) rng.Select rng.Copy 'fixed point 1 'reading the common path at the book "ITHD Reports.xls" and "ITHD Monthly 'Calculator for reports V5.xls" d = ThisWorkbook.Path 'fixed point 2 'opened the book "ITHD Monthly Calculator for reports V5.xls" 'with the same path Set pastedBook = Workbooks.Open(d & "\ITHD Monthly Calculator for reports V5.xls") 'pasted to Time!A3 pastedBook.Worksheets("Time").Range("A3").PasteSpe cial xlPasteValues 'saved the book "ITHD Monthly Calculator for reports V5.xls" pastedBook.Save 'closed the book "ITHD Monthly Calculator for reports V5.xls" pastedBook.Close Set pastedBook = Nothing End Sub "Paul" wrote in message om... Thanks for your efforts Samura. I still see errors for some reason, with your code. It cannot find "ITHD Monthly Calculator for reports V5.xls", I overcome this, then it comes up with Compile error on the SAVE. Maybe the differences are due to Excel Versions. I'm on XL 2K SR-1 "Samura" wrote in message ... Hi. It may be late, I fixed that macro. This macro does not receive the error. The macro opens the book "ITHD Monthly Calculator for reports V5.xls", and pastes from "ITHD Reports.xls" to the book, and saves the book, and closes the book. Sub ITHDdocID() Dim rng As Range Dim pastedBook As Workbook Windows("ITHD Reports.xls").Activate Worksheets("ITHD").Range("A6").Select 'selected from ITHD!A6 to the end Set rng = Range(Selection, Selection.End(xlDown)) rng.Select rng.Copy 'opened the book "ITHD Monthly Calculator for reports V5.xls" Set pastedBook = Workbooks.Open("ITHD Monthly Calculator for reports V5.xls") 'pasted to Time!A3 pastedBook.Worksheets("Time").Range("A3").PasteSpe cial xlPasteValues 'saved the book "ITHD Monthly Calculator for reports V5.xls" pastedBook.Save 'closed the book "ITHD Monthly Calculator for reports V5.xls" pastedBook.Close Set pastedBook = Nothing End Sub |
Need a macro to copy a range in one workbook and paste into another workbook
Hi, Paul.
I could run that macro with no error. Paul Maybe the differences are due to Excel Versions. I'm on XL 2K SR-1 Excel that could run the macro was Excel 2000 (9.0.4402 SR-1) on the Windows 2000 SP4. Is your Excel's version same? Paul It cannot find "ITHD Monthly Calculator for reports V5.xls", Perhaps, that macro does not recognize the path. Then, how about trying to run this fixed macro? Sub ITHDdocID2() Dim rng As Range Dim pastedBook As Workbook Dim d As String Windows("ITHD Reports.xls").Activate Worksheets("ITHD").Range("A6").Select 'selected from ITHD!A6 to the end Set rng = Range(Selection, Selection.End(xlDown)) rng.Select rng.Copy 'fixed point 1 'reading the common path at the book "ITHD Reports.xls" and "ITHD Monthly 'Calculator for reports V5.xls" d = ThisWorkbook.Path 'fixed point 2 'opened the book "ITHD Monthly Calculator for reports V5.xls" 'with the same path Set pastedBook = Workbooks.Open(d & "\ITHD Monthly Calculator for reports V5.xls") 'pasted to Time!A3 pastedBook.Worksheets("Time").Range("A3").PasteSpe cial xlPasteValues 'saved the book "ITHD Monthly Calculator for reports V5.xls" pastedBook.Save 'closed the book "ITHD Monthly Calculator for reports V5.xls" pastedBook.Close Set pastedBook = Nothing End Sub -- Samura "Paul" wrote in message om... Thanks for your efforts Samura. I still see errors for some reason, with your code. It cannot find "ITHD Monthly Calculator for reports V5.xls", I overcome this, then it comes up with Compile error on the SAVE. Maybe the differences are due to Excel Versions. I'm on XL 2K SR-1 "Samura" wrote in message ... Hi. It may be late, I fixed that macro. This macro does not receive the error. The macro opens the book "ITHD Monthly Calculator for reports V5.xls", and pastes from "ITHD Reports.xls" to the book, and saves the book, and closes the book. Sub ITHDdocID() Dim rng As Range Dim pastedBook As Workbook Windows("ITHD Reports.xls").Activate Worksheets("ITHD").Range("A6").Select 'selected from ITHD!A6 to the end Set rng = Range(Selection, Selection.End(xlDown)) rng.Select rng.Copy 'opened the book "ITHD Monthly Calculator for reports V5.xls" Set pastedBook = Workbooks.Open("ITHD Monthly Calculator for reports V5.xls") 'pasted to Time!A3 pastedBook.Worksheets("Time").Range("A3").PasteSpe cial xlPasteValues 'saved the book "ITHD Monthly Calculator for reports V5.xls" pastedBook.Save 'closed the book "ITHD Monthly Calculator for reports V5.xls" pastedBook.Close Set pastedBook = Nothing End Sub |
| All times are GMT +1. The time now is 10:53 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com