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

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

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

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

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

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