Macro copy values to a different workbook

  • Thread starter Thread starter temil
  • Start date Start date
T

temil

First let me say, I'm a complete vb noob. I know excel very well, but
vb is completely new to me, so please be gentle with me =) .. hehe.

I found this script on here posted back in mid 2008, and for some
reason I couldn't reply to it, so I'm posting a new topic on it to see
if anyone can help me out.

The following code basically takes a specified Range and checks the
first Column of that range, and finds all data to the left without a
set number of cells. It then copies that data to a different workbook,
that part for me works fine, but I'm curious on a few things.

1. is there anyway to copy only values, and not formulas as it's doing
now. I tried adding in Paste Specials but that didn't work.
2. I would like to modify this, to where i can have it copy multiple
cell ranges (example: A1:C10, A3:C10, D1:C12) and make a separate
copied selection area into the new workbook. Is that as simple as
copying the section of code that is referring to the finding of next
blank cell in that row?
3.Is there any simply way to make a macro to work in reverse as well?
Basically, so that i have 1 macro key to backup/save the data. Then
another that will restore the data into the correct field, NOT filling
in blanks though. Only restoring the values IF the 2nd workbook has
that info, if not, then skip it.

Would it help if i posted the document so you can see exactly what i'm
trying to do?

Here is the working code i found on here previously, but doesn't fully
do what i need it to currently...
Thanks again for any help!

Sub Test()
Dim bk As Workbook
Dim bSave As Boolean
Dim lRow As Long


' test to see if Destination.xls is already open


On Error Resume Next
Set bk = Workbooks("Destination.xls")
On Error GoTo 0
If bk Is Nothing Then
bSave = True
Set bk = Workbooks.Open("C:\Destination.xls")
End If


'find first empty row in database
lRow = bk.Worksheets("Test").Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
With ThisWorkbook.Sheets("Data4")
RowCount = 1
Do While .Range("A" & RowCount) <> ""
FindData = .Range("A" & RowCount)
Set c = bk.Worksheets("Test").Columns("A").Find(what:=FindData, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
.Range("A" & RowCount & ":C" & RowCount).Copy _
Destination:=bk.Worksheets("Test").Range("A" & lRow)
lRow = lRow + 1
Else
.Range("A" & RowCount & ":C" & RowCount).Copy _
Destination:=bk.Worksheets("Test").Range("A" & c.row)
End If
RowCount = RowCount + 1
Loop


End With


' if destination was originally closed, then save and close it
If bSave Then bk.Close Savechanges:=True


End Sub
 
First let me say, I'm a complete vb noob. I know excel very well, but
vb is completely new to me, so please be gentle with me =) .. hehe.

I found this script on here posted back in mid 2008, and for some
reason I couldn't reply to it, so I'm posting a new topic on it to see
if anyone can help me out.

The following code basically takes a specified Range and checks the
first Column of that range, and finds all data to the left without a
set number of cells. It then copies that data to a different workbook,
that part for me works fine, but I'm curious on a few things.

1. is there anyway to copy only values, and not formulas as it's doing
now. I tried adding in Paste Specials but that didn't work.
2. I would like to modify this, to where i can have it copy multiple
cell ranges (example: A1:C10, A3:C10, D1:C12) and make a separate
copied selection area into the new workbook. Is that as simple as
copying the section of code that is referring to the finding of next
blank cell in that row?
3.Is there any simply way to make a macro to work in reverse as well?
Basically, so that i have 1 macro key to backup/save the data. Then
another that will restore the data into the correct field, NOT filling
in blanks though. Only restoring the values IF the 2nd workbook has
that info, if not, then skip it.

Would it help if i posted the document so you can see exactly what i'm
trying to do?

Here is the working code i found on here previously, but doesn't fully
do what i need it to currently...
Thanks again for any help!

Sub Test()
Dim bk As Workbook
Dim bSave As Boolean
Dim lRow As Long

' test to see if Destination.xls is already open

On Error Resume Next
  Set bk = Workbooks("Destination.xls")
On Error GoTo 0
If bk Is Nothing Then
  bSave = True
  Set bk = Workbooks.Open("C:\Destination.xls")
End If

'find  first empty row in database
lRow = bk.Worksheets("Test").Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row
With ThisWorkbook.Sheets("Data4")
   RowCount = 1
   Do While .Range("A" & RowCount) <> ""
      FindData = .Range("A" & RowCount)
Set c = bk.Worksheets("Test").Columns("A").Find(what:=FindData, _
         LookIn:=xlValues, lookat:=xlWhole)
      If c Is Nothing Then
         .Range("A" & RowCount & ":C" & RowCount).Copy _
            Destination:=bk.Worksheets("Test").Range("A" & lRow)
         lRow = lRow + 1
      Else
         .Range("A" & RowCount & ":C" & RowCount).Copy _
            Destination:=bk.Worksheets("Test").Range("A" & c.row)
      End If
      RowCount = RowCount + 1
   Loop

End With

' if destination was originally closed, then save and close it
If bSave Then bk.Close Savechanges:=True

End Sub
Click to expand...

a little more info, I have a spreadsheet that i'm making for me and my
friends, and are trying to use the macros as a way to save the data,
so that when I update the spreadsheet, the custom values they have
added can be backed up, and restored back into the updated copy of the
spreadsheet. That being said, maybe there is also another way if
doing this without macros that is more simple?? I'm up for any ideas.
 
temil,

(1) to test whether a cell has a formula use
eg.
If Range("A1").HasFormula Then
'do something
End

(2) can you record a macro and look at the VBA code written?

(3) yes.

Would it be possible to simplify the process? It sounds like a lot of
moving parts. Think of avoiding copying across to several other
workbooks, copying from/into different ranges.

Hiran
 
(1) to test whether a cell has a formula use
eg.
If Range("A1").HasFormula Then
        'do something
End
Click to expand...

um, maybe I didn't explain it correctly. I know how to check if a cell
has value, as it was allready doing that, and copying the formulas,
but not the values which is what i would really need copied.
(2) can you record a macro and look at the VBA code written?
Click to expand...
allready tried that, but that copies true values/formulas/etc and
really dumbs it down, so that if i add more subjects down the left
side, it makes it very hard to copy/paste back into the live document
and causes problems.
(3) yes.

Would it be possible to simplify the process? It sounds like a lot of
moving parts. Think of avoiding copying across to several other
workbooks, copying from/into different ranges.
Click to expand...
yea, i could do copies to different ranges, but when I give an updated
document to friends with the added subjects/etc, I want to have one
macro they can run to backup there custom values, then they update the
document and run another macro to move in all there backed up data
into the correct fields, based on the first Column and fills in values
into other cells to the right.

I have been playing with it today, and got most all of it working,
it's not a pretty code, but it semi-works. Main thing right now is how
to use the follow line, and ONLY copy over the values, and not the
full formula.

btw, thanks for the reply!

I'm assuming something in the following line has to be changed, or
added to only copy the values, and not the formulas but I could be
wrong.. and ideas??

.Range("A" & RowCount & ":C" & RowCount).Copy _
Destination:=bk.Worksheets("Test").Range("A" & lRow)
 
um, maybe I didn't explain it correctly. I know how to check if a cell
has value, as it was allready doing that, and copying the formulas,
but not the values which is what i would really need copied.




allready tried that, but that copies true values/formulas/etc and
really dumbs it down, so that if i add more subjects down the left
side, it makes it very hard to copy/paste back into the live document
and causes problems.



yea, i could do copies to different ranges, but when I give an updated
document to friends with the added subjects/etc, I want to have one
macro they can run to backup there custom values, then they update the
document and run another macro to move in all there backed up data
into the correct fields, based on the first Column and fills in values
into other cells to the right.

I have been playing with it today, and got most all of it working,
it's not a pretty code, but it semi-works. Main thing right now is how
to use the follow line, and ONLY copy over the values, and not the
full formula.

btw, thanks for the reply!

I'm assuming something in the following line has to be changed, or
added to only copy the values, and not the formulas but I could be
wrong.. and ideas??

 .Range("A" & RowCount & ":C" & RowCount).Copy _
            Destination:=bk.Worksheets("Test").Range("A" & lRow)
Click to expand...

acualy, i figured out part of it finaly =) ..

replacing

.Range("A" & RowCount & ":C" & RowCount).Copy _
Destination:=bk.Worksheets("Test").Range("A" & lRow

with

.Range("A" & RowCount & ":C" & RowCount).Copy
bk.Worksheets("Test").Range("A" & lRow).PasteSpecial
xlPasteValues
 
Back
Top