Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro copy values to a different workbook
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:=Find Data, _ 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro copy values to a different workbook
On Aug 5, 9:59*am, temil wrote:
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:=Find Data, _ * * * * *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 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro copy values to a different workbook
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro copy values to a different workbook
(1) to test whether a cell has a formula use eg. If Range("A1").HasFormula Then * * * * 'do something End 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? 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. 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) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro copy values to a different workbook
On Aug 5, 9:45*pm, temil wrote:
(1) to test whether a cell has a formula use eg. If Range("A1").HasFormula Then * * * * 'do something End 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? 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. 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) 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA Copy values only onto new workbook | Excel Discussion (Misc queries) | |||
Macro to copy values to Workbook | Excel Discussion (Misc queries) | |||
Copy Values From A Non-Open Workbook? | Excel Programming | |||
Macro to open workbook and copy and paste values in to orig workbo | Excel Worksheet Functions | |||
copy values from one workbook to another | Excel Programming |