Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
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 |