Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to copy values to Workbook
Hello friends,
Please help me with a macro! I have my data in Workbook A. I want to select any range of values (15 rows) from Workbook A, to copy them to Workbook B in the range A1:AA15. Thanx. -- KCG |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to copy values to Workbook
One way:
Option Explicit Sub testme01() Dim WkbkBWks As Worksheet Dim RngToCopy As Range Dim DestCell As Range 'change this as required--workbookB has to be open! Set WkbkBWks = Workbooks("book2.xls").Worksheets("Sheet1") Set DestCell = WkbkBWks.Range("a1") Set RngToCopy = Nothing On Error Resume Next Set RngToCopy = Application.InputBox(Prompt:="select a cell", _ Type:=8).Cells(1) On Error GoTo 0 If RngToCopy Is Nothing Then Exit Sub 'user hit cancel End If RngToCopy.EntireRow.Resize(15, 27).Copy _ Destination:=DestCell End Sub KCG wrote: Hello friends, Please help me with a macro! I have my data in Workbook A. I want to select any range of values (15 rows) from Workbook A, to copy them to Workbook B in the range A1:AA15. Thanx. -- KCG -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to copy values to Workbook
Hello Dave,
I have tried the macro and it works wonderfully, except that it copies the formulae from WorkBook A to WorkBook B. Is there a line of code to be included which will allow the macro to copy values only, and not copy formulae? Regards -- KCG "Dave Peterson" wrote: One way: Option Explicit Sub testme01() Dim WkbkBWks As Worksheet Dim RngToCopy As Range Dim DestCell As Range 'change this as required--workbookB has to be open! Set WkbkBWks = Workbooks("book2.xls").Worksheets("Sheet1") Set DestCell = WkbkBWks.Range("a1") Set RngToCopy = Nothing On Error Resume Next Set RngToCopy = Application.InputBox(Prompt:="select a cell", _ Type:=8).Cells(1) On Error GoTo 0 If RngToCopy Is Nothing Then Exit Sub 'user hit cancel End If RngToCopy.EntireRow.Resize(15, 27).Copy _ Destination:=DestCell End Sub KCG wrote: Hello friends, Please help me with a macro! I have my data in Workbook A. I want to select any range of values (15 rows) from Workbook A, to copy them to Workbook B in the range A1:AA15. Thanx. -- KCG -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to copy values to Workbook
Replace:
RngToCopy.EntireRow.Resize(15, 27).Copy _ Destination:=DestCell with RngToCopy.EntireRow.Resize(15, 27).Copy destcell.pastespecial paste:=xlpastevalues KCG wrote: Hello Dave, I have tried the macro and it works wonderfully, except that it copies the formulae from WorkBook A to WorkBook B. Is there a line of code to be included which will allow the macro to copy values only, and not copy formulae? Regards -- KCG "Dave Peterson" wrote: One way: Option Explicit Sub testme01() Dim WkbkBWks As Worksheet Dim RngToCopy As Range Dim DestCell As Range 'change this as required--workbookB has to be open! Set WkbkBWks = Workbooks("book2.xls").Worksheets("Sheet1") Set DestCell = WkbkBWks.Range("a1") Set RngToCopy = Nothing On Error Resume Next Set RngToCopy = Application.InputBox(Prompt:="select a cell", _ Type:=8).Cells(1) On Error GoTo 0 If RngToCopy Is Nothing Then Exit Sub 'user hit cancel End If RngToCopy.EntireRow.Resize(15, 27).Copy _ Destination:=DestCell End Sub KCG wrote: Hello friends, Please help me with a macro! I have my data in Workbook A. I want to select any range of values (15 rows) from Workbook A, to copy them to Workbook B in the range A1:AA15. Thanx. -- KCG -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to copy values to Workbook
Hello again Dave,
It tried your suggestion and it worked, but this time, the macro underlines some values in row 15 in the destination workbook. Is there a way to rectify this? -- KCG "Dave Peterson" wrote: Replace: RngToCopy.EntireRow.Resize(15, 27).Copy _ Destination:=DestCell with RngToCopy.EntireRow.Resize(15, 27).Copy destcell.pastespecial paste:=xlpastevalues KCG wrote: Hello Dave, I have tried the macro and it works wonderfully, except that it copies the formulae from WorkBook A to WorkBook B. Is there a line of code to be included which will allow the macro to copy values only, and not copy formulae? Regards -- KCG "Dave Peterson" wrote: One way: Option Explicit Sub testme01() Dim WkbkBWks As Worksheet Dim RngToCopy As Range Dim DestCell As Range 'change this as required--workbookB has to be open! Set WkbkBWks = Workbooks("book2.xls").Worksheets("Sheet1") Set DestCell = WkbkBWks.Range("a1") Set RngToCopy = Nothing On Error Resume Next Set RngToCopy = Application.InputBox(Prompt:="select a cell", _ Type:=8).Cells(1) On Error GoTo 0 If RngToCopy Is Nothing Then Exit Sub 'user hit cancel End If RngToCopy.EntireRow.Resize(15, 27).Copy _ Destination:=DestCell End Sub KCG wrote: Hello friends, Please help me with a macro! I have my data in Workbook A. I want to select any range of values (15 rows) from Workbook A, to copy them to Workbook B in the range A1:AA15. Thanx. -- KCG -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to copy values to Workbook
There's nothing in the code that does that underlining.
Maybe you've formatted that range previously and now are just noticing it???? (Clear the formats and try again.) KCG wrote: Hello again Dave, It tried your suggestion and it worked, but this time, the macro underlines some values in row 15 in the destination workbook. Is there a way to rectify this? -- KCG "Dave Peterson" wrote: Replace: RngToCopy.EntireRow.Resize(15, 27).Copy _ Destination:=DestCell with RngToCopy.EntireRow.Resize(15, 27).Copy destcell.pastespecial paste:=xlpastevalues KCG wrote: Hello Dave, I have tried the macro and it works wonderfully, except that it copies the formulae from WorkBook A to WorkBook B. Is there a line of code to be included which will allow the macro to copy values only, and not copy formulae? Regards -- KCG "Dave Peterson" wrote: One way: Option Explicit Sub testme01() Dim WkbkBWks As Worksheet Dim RngToCopy As Range Dim DestCell As Range 'change this as required--workbookB has to be open! Set WkbkBWks = Workbooks("book2.xls").Worksheets("Sheet1") Set DestCell = WkbkBWks.Range("a1") Set RngToCopy = Nothing On Error Resume Next Set RngToCopy = Application.InputBox(Prompt:="select a cell", _ Type:=8).Cells(1) On Error GoTo 0 If RngToCopy Is Nothing Then Exit Sub 'user hit cancel End If RngToCopy.EntireRow.Resize(15, 27).Copy _ Destination:=DestCell End Sub KCG wrote: Hello friends, Please help me with a macro! I have my data in Workbook A. I want to select any range of values (15 rows) from Workbook A, to copy them to Workbook B in the range A1:AA15. Thanx. -- KCG -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to copy values to Workbook
Hello again Dave,
Thanks for your advice. You're a genius! One last question: Is is possible for the macro to exclude the value "N/A" when it copies to the target worksheet? Thanks -- KCG "Dave Peterson" wrote: There's nothing in the code that does that underlining. Maybe you've formatted that range previously and now are just noticing it???? (Clear the formats and try again.) KCG wrote: Hello again Dave, It tried your suggestion and it worked, but this time, the macro underlines some values in row 15 in the destination workbook. Is there a way to rectify this? -- KCG "Dave Peterson" wrote: Replace: RngToCopy.EntireRow.Resize(15, 27).Copy _ Destination:=DestCell with RngToCopy.EntireRow.Resize(15, 27).Copy destcell.pastespecial paste:=xlpastevalues KCG wrote: Hello Dave, I have tried the macro and it works wonderfully, except that it copies the formulae from WorkBook A to WorkBook B. Is there a line of code to be included which will allow the macro to copy values only, and not copy formulae? Regards -- KCG "Dave Peterson" wrote: One way: Option Explicit Sub testme01() Dim WkbkBWks As Worksheet Dim RngToCopy As Range Dim DestCell As Range 'change this as required--workbookB has to be open! Set WkbkBWks = Workbooks("book2.xls").Worksheets("Sheet1") Set DestCell = WkbkBWks.Range("a1") Set RngToCopy = Nothing On Error Resume Next Set RngToCopy = Application.InputBox(Prompt:="select a cell", _ Type:=8).Cells(1) On Error GoTo 0 If RngToCopy Is Nothing Then Exit Sub 'user hit cancel End If RngToCopy.EntireRow.Resize(15, 27).Copy _ Destination:=DestCell End Sub KCG wrote: Hello friends, Please help me with a macro! I have my data in Workbook A. I want to select any range of values (15 rows) from Workbook A, to copy them to Workbook B in the range A1:AA15. Thanx. -- KCG -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to copy values to Workbook
Right now, your code copies a block of data.
You have a couple of choices. One is to loop through the input range and copy row by row if it matches your criteria. Another would be to loop through the pasted range and delete the rows that match your criteria. I would guess that it would be better to use the first--then you wouldn't have to worry about moving rows into the output range. But the range that is copied is pretty large (15 rows x 27 columns). Do you avoid all the rows where any of the columns contain "N/A"--and is "N/A" something you typed or is it really the #N/A error? KCG wrote: Hello again Dave, Thanks for your advice. You're a genius! One last question: Is is possible for the macro to exclude the value "N/A" when it copies to the target worksheet? Thanks -- KCG "Dave Peterson" wrote: There's nothing in the code that does that underlining. Maybe you've formatted that range previously and now are just noticing it???? (Clear the formats and try again.) KCG wrote: Hello again Dave, It tried your suggestion and it worked, but this time, the macro underlines some values in row 15 in the destination workbook. Is there a way to rectify this? -- KCG "Dave Peterson" wrote: Replace: RngToCopy.EntireRow.Resize(15, 27).Copy _ Destination:=DestCell with RngToCopy.EntireRow.Resize(15, 27).Copy destcell.pastespecial paste:=xlpastevalues KCG wrote: Hello Dave, I have tried the macro and it works wonderfully, except that it copies the formulae from WorkBook A to WorkBook B. Is there a line of code to be included which will allow the macro to copy values only, and not copy formulae? Regards -- KCG "Dave Peterson" wrote: One way: Option Explicit Sub testme01() Dim WkbkBWks As Worksheet Dim RngToCopy As Range Dim DestCell As Range 'change this as required--workbookB has to be open! Set WkbkBWks = Workbooks("book2.xls").Worksheets("Sheet1") Set DestCell = WkbkBWks.Range("a1") Set RngToCopy = Nothing On Error Resume Next Set RngToCopy = Application.InputBox(Prompt:="select a cell", _ Type:=8).Cells(1) On Error GoTo 0 If RngToCopy Is Nothing Then Exit Sub 'user hit cancel End If RngToCopy.EntireRow.Resize(15, 27).Copy _ Destination:=DestCell End Sub KCG wrote: Hello friends, Please help me with a macro! I have my data in Workbook A. I want to select any range of values (15 rows) from Workbook A, to copy them to Workbook B in the range A1:AA15. Thanx. -- KCG -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to copy values to Workbook
Hi there Dave,
Sorry for the delay in replying to you. Yes, the first option would be better. I want to avoid the cells which contain "N/A" in the range. ('N/A' is really an error which reads a different table). Thanx once again. -- KCG "Dave Peterson" wrote: Right now, your code copies a block of data. You have a couple of choices. One is to loop through the input range and copy row by row if it matches your criteria. Another would be to loop through the pasted range and delete the rows that match your criteria. I would guess that it would be better to use the first--then you wouldn't have to worry about moving rows into the output range. But the range that is copied is pretty large (15 rows x 27 columns). Do you avoid all the rows where any of the columns contain "N/A"--and is "N/A" something you typed or is it really the #N/A error? KCG wrote: Hello again Dave, Thanks for your advice. You're a genius! One last question: Is is possible for the macro to exclude the value "N/A" when it copies to the target worksheet? Thanks -- KCG "Dave Peterson" wrote: There's nothing in the code that does that underlining. Maybe you've formatted that range previously and now are just noticing it???? (Clear the formats and try again.) KCG wrote: Hello again Dave, It tried your suggestion and it worked, but this time, the macro underlines some values in row 15 in the destination workbook. Is there a way to rectify this? -- KCG "Dave Peterson" wrote: Replace: RngToCopy.EntireRow.Resize(15, 27).Copy _ Destination:=DestCell with RngToCopy.EntireRow.Resize(15, 27).Copy destcell.pastespecial paste:=xlpastevalues KCG wrote: Hello Dave, I have tried the macro and it works wonderfully, except that it copies the formulae from WorkBook A to WorkBook B. Is there a line of code to be included which will allow the macro to copy values only, and not copy formulae? Regards -- KCG "Dave Peterson" wrote: One way: Option Explicit Sub testme01() Dim WkbkBWks As Worksheet Dim RngToCopy As Range Dim DestCell As Range 'change this as required--workbookB has to be open! Set WkbkBWks = Workbooks("book2.xls").Worksheets("Sheet1") Set DestCell = WkbkBWks.Range("a1") Set RngToCopy = Nothing On Error Resume Next Set RngToCopy = Application.InputBox(Prompt:="select a cell", _ Type:=8).Cells(1) On Error GoTo 0 If RngToCopy Is Nothing Then Exit Sub 'user hit cancel End If RngToCopy.EntireRow.Resize(15, 27).Copy _ Destination:=DestCell End Sub KCG wrote: Hello friends, Please help me with a macro! I have my data in Workbook A. I want to select any range of values (15 rows) from Workbook A, to copy them to Workbook B in the range A1:AA15. Thanx. -- KCG -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to copy values to Workbook
Untested, but it did compile:
Option Explicit Sub testme02() Dim WkbkBWks As Worksheet Dim RngToCopy As Range Dim DestCell As Range Dim myRow As Range 'change this as required--workbookB has to be open! Set WkbkBWks = Workbooks("book2.xls").Worksheets("Sheet1") Set DestCell = WkbkBWks.Range("a1") Set RngToCopy = Nothing On Error Resume Next Set RngToCopy = Application.InputBox(Prompt:="select a cell", _ Type:=8).Cells(1) On Error GoTo 0 If RngToCopy Is Nothing Then Exit Sub 'user hit cancel End If Set RngToCopy = RngToCopy.Resize(15, 27) For Each myRow In RngToCopy.Rows If Application.CountIf(myRow, "#N/A") 0 Then 'skip it Else myRow.Copy _ Destination:=DestCell 'get ready for next one Set DestCell = DestCell.Offset(1, 0) End If Next myRow End Sub KCG wrote: Hi there Dave, Sorry for the delay in replying to you. Yes, the first option would be better. I want to avoid the cells which contain "N/A" in the range. ('N/A' is really an error which reads a different table). Thanx once again. -- KCG "Dave Peterson" wrote: Right now, your code copies a block of data. You have a couple of choices. One is to loop through the input range and copy row by row if it matches your criteria. Another would be to loop through the pasted range and delete the rows that match your criteria. I would guess that it would be better to use the first--then you wouldn't have to worry about moving rows into the output range. But the range that is copied is pretty large (15 rows x 27 columns). Do you avoid all the rows where any of the columns contain "N/A"--and is "N/A" something you typed or is it really the #N/A error? KCG wrote: Hello again Dave, Thanks for your advice. You're a genius! One last question: Is is possible for the macro to exclude the value "N/A" when it copies to the target worksheet? Thanks -- KCG "Dave Peterson" wrote: There's nothing in the code that does that underlining. Maybe you've formatted that range previously and now are just noticing it???? (Clear the formats and try again.) KCG wrote: Hello again Dave, It tried your suggestion and it worked, but this time, the macro underlines some values in row 15 in the destination workbook. Is there a way to rectify this? -- KCG "Dave Peterson" wrote: Replace: RngToCopy.EntireRow.Resize(15, 27).Copy _ Destination:=DestCell with RngToCopy.EntireRow.Resize(15, 27).Copy destcell.pastespecial paste:=xlpastevalues KCG wrote: Hello Dave, I have tried the macro and it works wonderfully, except that it copies the formulae from WorkBook A to WorkBook B. Is there a line of code to be included which will allow the macro to copy values only, and not copy formulae? Regards -- KCG "Dave Peterson" wrote: One way: Option Explicit Sub testme01() Dim WkbkBWks As Worksheet Dim RngToCopy As Range Dim DestCell As Range 'change this as required--workbookB has to be open! Set WkbkBWks = Workbooks("book2.xls").Worksheets("Sheet1") Set DestCell = WkbkBWks.Range("a1") Set RngToCopy = Nothing On Error Resume Next Set RngToCopy = Application.InputBox(Prompt:="select a cell", _ Type:=8).Cells(1) On Error GoTo 0 If RngToCopy Is Nothing Then Exit Sub 'user hit cancel End If RngToCopy.EntireRow.Resize(15, 27).Copy _ Destination:=DestCell End Sub KCG wrote: Hello friends, Please help me with a macro! I have my data in Workbook A. I want to select any range of values (15 rows) from Workbook A, to copy them to Workbook B in the range A1:AA15. Thanx. -- KCG -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to copy values to Workbook
Hi there Dave,
I tested it and encountered some errors. Not a problem. The macro is generally working fine. Thanx once again -- KCG "Dave Peterson" wrote: Untested, but it did compile: Option Explicit Sub testme02() Dim WkbkBWks As Worksheet Dim RngToCopy As Range Dim DestCell As Range Dim myRow As Range 'change this as required--workbookB has to be open! Set WkbkBWks = Workbooks("book2.xls").Worksheets("Sheet1") Set DestCell = WkbkBWks.Range("a1") Set RngToCopy = Nothing On Error Resume Next Set RngToCopy = Application.InputBox(Prompt:="select a cell", _ Type:=8).Cells(1) On Error GoTo 0 If RngToCopy Is Nothing Then Exit Sub 'user hit cancel End If Set RngToCopy = RngToCopy.Resize(15, 27) For Each myRow In RngToCopy.Rows If Application.CountIf(myRow, "#N/A") 0 Then 'skip it Else myRow.Copy _ Destination:=DestCell 'get ready for next one Set DestCell = DestCell.Offset(1, 0) End If Next myRow End Sub KCG wrote: Hi there Dave, Sorry for the delay in replying to you. Yes, the first option would be better. I want to avoid the cells which contain "N/A" in the range. ('N/A' is really an error which reads a different table). Thanx once again. -- KCG "Dave Peterson" wrote: Right now, your code copies a block of data. You have a couple of choices. One is to loop through the input range and copy row by row if it matches your criteria. Another would be to loop through the pasted range and delete the rows that match your criteria. I would guess that it would be better to use the first--then you wouldn't have to worry about moving rows into the output range. But the range that is copied is pretty large (15 rows x 27 columns). Do you avoid all the rows where any of the columns contain "N/A"--and is "N/A" something you typed or is it really the #N/A error? KCG wrote: Hello again Dave, Thanks for your advice. You're a genius! One last question: Is is possible for the macro to exclude the value "N/A" when it copies to the target worksheet? Thanks -- KCG "Dave Peterson" wrote: There's nothing in the code that does that underlining. Maybe you've formatted that range previously and now are just noticing it???? (Clear the formats and try again.) KCG wrote: Hello again Dave, It tried your suggestion and it worked, but this time, the macro underlines some values in row 15 in the destination workbook. Is there a way to rectify this? -- KCG "Dave Peterson" wrote: Replace: RngToCopy.EntireRow.Resize(15, 27).Copy _ Destination:=DestCell with RngToCopy.EntireRow.Resize(15, 27).Copy destcell.pastespecial paste:=xlpastevalues KCG wrote: Hello Dave, I have tried the macro and it works wonderfully, except that it copies the formulae from WorkBook A to WorkBook B. Is there a line of code to be included which will allow the macro to copy values only, and not copy formulae? Regards -- KCG "Dave Peterson" wrote: One way: Option Explicit Sub testme01() Dim WkbkBWks As Worksheet Dim RngToCopy As Range Dim DestCell As Range 'change this as required--workbookB has to be open! Set WkbkBWks = Workbooks("book2.xls").Worksheets("Sheet1") Set DestCell = WkbkBWks.Range("a1") Set RngToCopy = Nothing On Error Resume Next Set RngToCopy = Application.InputBox(Prompt:="select a cell", _ Type:=8).Cells(1) On Error GoTo 0 If RngToCopy Is Nothing Then Exit Sub 'user hit cancel End If RngToCopy.EntireRow.Resize(15, 27).Copy _ Destination:=DestCell End Sub KCG wrote: Hello friends, Please help me with a macro! I have my data in Workbook A. I want to select any range of values (15 rows) from Workbook A, to copy them to Workbook B in the range A1:AA15. Thanx. -- KCG -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to Copy From One Workbook To Another | Excel Discussion (Misc queries) | |||
copy all and paste values for all sheets in a workbook | Excel Worksheet Functions | |||
how to copy only values and formats of worksheets to new workbook | Excel Worksheet Functions | |||
How do I copy text and values from the same workbook onto a different sheet?? | Excel Discussion (Misc queries) | |||
Macro to open workbook and copy and paste values in to orig workbo | Excel Worksheet Functions |