Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
KCG KCG is offline
external usenet poster
 
Posts: 20
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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   Report Post  
Posted to microsoft.public.excel.misc
KCG KCG is offline
external usenet poster
 
Posts: 20
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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   Report Post  
Posted to microsoft.public.excel.misc
KCG KCG is offline
external usenet poster
 
Posts: 20
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro to Copy From One Workbook To Another [email protected] Excel Discussion (Misc queries) 3 June 25th 07 02:48 AM
copy all and paste values for all sheets in a workbook cass calculator Excel Worksheet Functions 6 June 1st 07 02:58 PM
how to copy only values and formats of worksheets to new workbook rvd Excel Worksheet Functions 3 January 31st 07 12:43 PM
How do I copy text and values from the same workbook onto a different sheet?? kepcopower Excel Discussion (Misc queries) 2 March 23rd 06 09:10 PM
Macro to open workbook and copy and paste values in to orig workbo Dena X Excel Worksheet Functions 1 December 15th 05 11:13 PM


All times are GMT +1. The time now is 07:33 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"