-
Excel help, copying data to another workbook.
Hi,
I currently have an excel document that when I press a button it runs a macro and saves a line of data to the end of a list in another sheet called DATACOLLECT. The data runs from B2 to F2, then after each button press the data is stored on the next line down.(B3 to F3 etc). What I want is another macro to call from the initial button press that will also save that last line to another workbook say called HISTORY and add it to the end of another list on say SHEET1 from cells P to T this time. Assume both files are in the same folder.
Can this be done?
Also if the History workbook is opened on another (networked) Pc will it cause an error as the files are stored on a network drive and usually cant be changed if they are open on another Pc?
Hope this makes sense.
-
Re: Excel help, copying data to another workbook.
Yep can be done ;-) and Yep if History is open by someone else then it wont be able to save it down :-(
-
Re: Excel help, copying data to another workbook.
Yep its very doable mate, but if someone else has the document open then it will most likely throw up an error when it tries to save, the code for adding it into the new file will be very similar to the current one, just pointing it at a different book and location on the sheet
-
Re: Excel help, copying data to another workbook.
Does anyone have an example macro that does this where I can rename the ranges/sheets and doc names?
Im not that good with Excel and everything I've done so for is from Googling, just can find the answer to this one.
Also will it be possible to add something so if it does error it just ignores it?
-
1 Attachment(s)
Re: Excel help, copying data to another workbook.
Here is a basic example mate,
Haven't had time to do you a more in-depth one but it does copy a range of values from the source workbook into a destination workbook and I have kept the code really straight forward (i hope!)
I have also attached a working example (well it worked for me but this is excel)
Extract the folder to "c:\"
source file will be: C:\excel\source.xlsm
destination file will be C:\excel\destination.xlsx
Code:
Sub Copy()
Dim InputFile As Workbook
Dim OutputFile As Workbook
Set InputFile = ActiveWorkbook
Set OutputFile = Workbooks.Open("c:\excel\destination.xlsx")
'Now, copy what you want from InputFile:
InputFile.Sheets("Sheet1").Activate
InputFile.Sheets("Sheet1").Range("A1:B10").Copy
OutputFile.Sheets("Sheet1").Activate
OutputFile.Sheets("Sheet1").Range("A1").PasteSpecial
OutputFile.Close savechanges:=True
End Sub
happy to help more but would have to be later in the week
Attachment 30741
-
Re: Excel help, copying data to another workbook.
Quote:
Originally Posted by
Mystical_2K
Here is a basic example mate,
Haven't had time to do you a more in-depth one but it does copy a range of values from the source workbook into a destination workbook and I have kept the code really straight forward (i hope!)
I have also attached a working example (well it worked for me but this is excel)
Extract the folder to "c:\"
source file will be:
C:\excel\source.xlsm
destination file will be
C:\excel\destination.xlsx
Code:
Sub Copy()
Dim InputFile As Workbook
Dim OutputFile As Workbook
Set InputFile = ActiveWorkbook
Set OutputFile = Workbooks.Open("c:\excel\destination.xlsx")
'Now, copy what you want from InputFile:
InputFile.Sheets("Sheet1").Activate
InputFile.Sheets("Sheet1").Range("A1:B10").Copy
OutputFile.Sheets("Sheet1").Activate
OutputFile.Sheets("Sheet1").Range("A1").PasteSpecial
OutputFile.Close savechanges:=True
End Sub
happy to help more but would have to be later in the week
Attachment 30741
Thank you for your time, if I'm correct does this copy range A1 to B10 from the source and pastes it to A1 in the destination?
However what I am after is copying the end row of a list (constantly being added to) from B to F and pasting it in another document into the end row of that likely longer list (i.e not the same row)
-
Re: Excel help, copying data to another workbook.
Quote:
Originally Posted by
Soulassassin
Thank you for your time, if I'm correct does this copy range A1 to B10 from the source and pastes it to A1 in the destination?
However what I am after is copying the end row of a list (constantly being added to) from B to F and pasting it in another document into the end row of that likely longer list (i.e not the same row)
Yes mate, thats what it does, I will take another look and see what I can put together
-
Re: Excel help, copying data to another workbook.
oh and just to confirm when you say a list do you mean cells or a drop down list?
-
Re: Excel help, copying data to another workbook.
You want to find the last row there are a few techniques for this but I wrote myself a little function to do it
Function LastRow(iColumn, iStart, iWorksheet) As Integer
Dim lrn
lrn = iStart
Do Until Worksheets(iWorksheet).Range(iColumn & Trim(str(lrn))) = ""
lrn = lrn + 1
Loop
LastRow = lrn - 1
End Function
So to find the lastrow you can do something like this
Dim iRow as Integer
iRow = LastRowNumber("A", 8, "Sheet1")
InputFile.Sheets("Sheet1").Range("A" & CSTR(iRow) & ":B" CSTR(iRow)).Copy
You could then do the LastRowNumber Function on the Log sheet to find the last row and do
OutputFile.Sheets("Sheet1").Activate
OutputFile.Sheets("Sheet1").Range("A"&CSTR(iRow+1)).Select
Selection.PasteSpecial
-
Re: Excel help, copying data to another workbook.
Thank you for the help peeps, I've found this bit code that does almost exactly what I want. The only slight problem is that it doesn't let me paste a specific range, it just copies the entire row. However I overcame this by just saving the souce data in the same columns.
Code:
Private Sub LastRowToExport()
Dim lastS1Row As Long 'Last Source Row
Dim nextS2Row As Long 'Next Target Row
Dim lastCol As Long 'Last Column on Source Sheet
Dim s1Sheet As Worksheet, s2Sheet As Worksheet
Dim source As String 'Source worksheet name
Dim target As String 'Target worksheet name
Dim path As String
'---SET SHEET NAMES HERE---
source = "Product" 'Source Worksheet Name
path = "C:\data.xlsx" 'Target File Path including file name and extension
target = "exportsheet" 'Target Worksheet Name
'WARNING - THIS LINE WILL DISABLE NORMAL BREAKING - IN CASE OF LOOP
Application.EnableCancelKey = xlDisabled 'Disables breaking when opening new book
'Define worksheets
Set s1Sheet = ThisWorkbook.Sheets(source) 'Source Sheet
Set s2Sheet = Workbooks.Open(path).Sheets(target) 'Target Sheet
'Get the last row on each sheet and set the NEXT Row on the target. Also total columns.
lastS1Row = s1Sheet.Range("A" & Rows.count).End(xlUp).row
nextS2Row = s2Sheet.Range("A" & Rows.count).End(xlUp).row + 1
lastCol = s1Sheet.Cells(1, Columns.count).End(xlToLeft).column 'Headers in Row 1
'---COPY ENTIRE ROW--- Loop through Cells by column
For lCol = 1 To lastCol
s2Sheet.Cells(nextS2Row, lCol) = s1Sheet.Cells(lastS1Row, lCol)
Next lCol
'WRAP UP, SAVE EXPORTED SHEET, REACTIVATE SOURCE SHEET
s2Sheet.Activate
ActiveWorkbook.Close SaveChanges:=True
s1Sheet.Activate
End Sub
-
Re: Excel help, copying data to another workbook.
Okay, new problem. This is part of another macro that copies from 'DATAINPUT3' cell 'E33' and pastes it to the end of column P in sheet 'DATACOLLECT'.
How do I make it paste the value only? It currently pastes everything.
Application.ScreenUpdating = False
Dim Lastrow As Long
Lastrow = Sheets("DATACOLLECT").Cells(Rows.Count, "P").End(xlUp).Row + 1
'Op into DATACOLLECT
Sheets("DATAINPUT3").Range("E33").Copy Destination:=Sheets("DATACOLLECT").Range("P" & Lastrow)
-
Re: Excel help, copying data to another workbook.
Excel is really easy for beginner coding as you can record your actions and it will script it for you ;-)
If you press record a macro and then
copy a cell and then paste special values only
then stop the macro recording
if you then go into VBA probably under Module 2 (it records macros to new modules) you will see the code you need ;-)
But here is the code
.PasteSpecial Paste:=xlPasteValues
-
Re: Excel help, copying data to another workbook.
-
Re: Excel help, copying data to another workbook.
When I record a macro it just copies and pastes (special) to that one cell. What this does is copies and pastes to the end of a row which for some reason I can't get the macro to record.
Where in the line of code (post #11) do I add the '.PasteSpecial Paste:=xlPasteValues' ?
-
Re: Excel help, copying data to another workbook.
Yeah record a macro to get the command needed and apply it to where needed
The problem with your script you found is you are not copying and pasting it is making a reference to the cells
s2Sheet.Cells(nextS2Row, lCol) = s1Sheet.Cells(lastS1Row, lCol)
PM me the file dude this will take seconds to do and I will annotate the VBA so you can learn from it ;-)
-
Re: Excel help, copying data to another workbook.
Can't find a way to send a file through PM, unless I'm missing something obvious.
Attached it here anyhow, excuse the file but its something I'm just modifying. The layout etc is not me.
Right here goes,
Go to DATAINPUT3 tab and click 31 Next,
This saves various bits of data to DATACOLLECT
Two columns, S and T both get there data from the DATAINPUT tab which has two drop down lists (date and shift)
When pasted to the DATACOLLECT the cell it's pasted to becomes a list formulated cell and also copies font size etc
This is what I need changing to value only.
Thank you
-
Re: Excel help, copying data to another workbook.
So I have redone your code for the Datacollect
From
Application.ScreenUpdating = False
Dim Lastrow As Long
Lastrow = Sheets("DATACOLLECT").Cells(Rows.Count, "P").End(xlUp).Row + 1
'Op into DATACOLLECT
Sheets("DATAINPUT3").Range("E33").Copy Destination:=Sheets("DATACOLLECT").Range("P" & Lastrow)
'Weld into DATACOLLECT
Sheets("DATAINPUT3").Range("E34").Copy Destination:=Sheets("DATACOLLECT").Range("Q" & Lastrow)
'Concern to DATACOLLECT
Sheets("DATAINPUT3").Range("E35").Copy Destination:=Sheets("DATACOLLECT").Range("R" & Lastrow)
'Date from DATAINPUT to DATACOLLECT
Sheets("DATAINPUT").Range("F2").Copy Destination:=Sheets("DATACOLLECT").Range("S" & Lastrow)
'Shift from DATAINPUT to DATACOLLECT
Sheets("DATAINPUT").Range("G2").Copy Destination:=Sheets("DATACOLLECT").Range("T" & Lastrow)
To
Sheets("DATAINPUT3").Range("E33:E35").Copy
'TransPose the results to do it as 1 range
Sheets("DATACOLLECT").Activate
Sheets("DATACOLLECT").Range("P" & Lastrow).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Sheets("DATAINPUT").Range("F2:G2").Copy
Sheets("DATACOLLECT").Activate
Sheets("DATACOLLECT").Range("S" & Lastrow).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False
-
Re: Excel help, copying data to another workbook.
Theres a link to this file which you didn't supply
C:\Users\XXXX\Desktop\Line 5 P32S FM Touch Up Daily - Apr to June 17.xlsm
But you should be able get a grasp of what I have done
Also this is not a great idea as tis is date related you will have to change your VBA code every couple of months i.e. July to Sept 17.xlsm
-
Re: Excel help, copying data to another workbook.
What is it you are manufacturing?
-
Re: Excel help, copying data to another workbook.
That works except I only replaced the code from Date and shift as the other Data was being missed, but that was probably just my wording. Thank you very much.
The link to the file I remembered after I'd left for work that that would cause an error, is there a way 'skip on error' by any chance as this would also happen if somebody opened that file on the network?
The name of the file was just the one I was given for an example and I would have to change it to the location on the works computer anyway so that's just local for me atm, but thanks for the heads up.
The file is for the front member of a car we manufacture, that is the part under the engine that all the wheel linkage is all bolted to.