Macro to copy values to Workbook

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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
 
Replace:
RngToCopy.EntireRow.Resize(15, 27).Copy _
Destination:=DestCell
with
RngToCopy.EntireRow.Resize(15, 27).Copy
destcell.pastespecial paste:=xlpastevalues


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
Click to expand...
 
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?
 
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.)
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?
Click to expand...
 
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
 
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?
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 said:
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.)
Click to expand...
Click to expand...
 
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 said:
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?
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
Click to expand...
Click to expand...
 
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


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.
Click to expand...
 
Hi there Dave,

I tested it and encountered some errors. Not a problem. The macro is
generally working fine.

Thanx once again
 
Back
Top