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

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

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

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

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

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

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

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

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

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

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