Free Microsoft Excel 2013 Quick Reference

Macro to copy result from one workbook to another

Is it possible to create a macro that would copy the results and only the results from one workbook to another? I dont want to copy all the results just a few cells.

I would like it to copy from sheet service report G cells

C4:C22, C24:39
D4:22, D24:39
E4:22, E24:39
H4:22, H24:39
I4:22, I24:39

and from sheet service report MT cells

C4:40
E4:40
F4:40
H4:40
I4:40

it would be great if it would as for the name of the workbook where it should copy the results and possible for the sheet name.

Now that i´ve written this down i´m starting to think this is impossible. But asking never hurt anyone.


I am trying to write a macro that will copy cells from one workbook to
another. The catch is that the name of the source workbook may have
been changed from when it was originally saved, so the macro can not
refer to the source workbook by name.

I have a work-around macro I run from my source workbook that copies
cells from my source workbook to a temp.xls file, then I copy from
temp.xls to my desitnation workbook (file name is known), but then I
need to be able to delete the temp.xls workbook from my macro that I am
running from my source workbook. What is the macro command to delete a
xls file?

--
mwc0914
------------------------------------------------------------------------
mwc0914's Profile: http://www.excelforum.com/member.php...o&userid=24130
View this thread: http://www.excelforum.com/showthread...hreadid=486912

Hi,

I'm trying to create a macro to copy text from one cell into another one. The only way I've found so far is with the simple formula ="cell of origin", but if I do this I'm then unable to modify the copied text in the receiving cell, if I try I lose the copied cell. Any idea?

I am trying to copy formulas from one workbook into another. I highlited the
desired cells, selected ctrl c, opened the new workbook and selected ctrl v.
It copied the values in the first workbooks cells, but not the formulas. Any
idea what I am doing wrong?

Hi

I have two workbook having 8 worksheet each. Every day I need to copy data from previous day worksheet and paste it on new workbook with same sheet name.

For example :-
Workbook1 is having sheet named aa, gg, tk .....
Workbook2 is having sheet named as same as Workbook 1

Now, I want macro to copy data from Workbook1, sheetname 'aa' and paste the same in workbook2 in sheet name 'aa' and likewise

Hope I am clear in explaining my problem

Any help please.

Thanks

I am trying to copy and paste a block of cells from one workbook to
another workbook. The original file is formulas and the destination file
cells need to have the same same formula.

any suggestions on how to add this into a macro?

hi,

can anyone help me how to create a macro in copying data from one workbook to another? I know how to link the workbook so that it can update the other workbook but I would like to know if it is possible to simply automatically copy one work book to another...I attached a sample workbooks. Book1 contains the data to be copied on Book2. please help me in copying the start and end time on book 1 going to book 2 once book2 is opened (assuming that book1 is opened as well). Appreciate any response..

Regards,

Stoey

Hi all,

Sorry for my first post to be asking for help. I don't have any experience with macros, but I'm trying to create one which will allow me to copy rows from one workbook to another based on whether a row with a unique ID already exists in the destination file.

I have two files, a master file called "master transfer file.xls" and then will get report files called "new data.xls." Both files have the same columns (but the master file has some additional manually entered columns at the far right. The new data files will have some records that are already in the master file and I want to ignore those (if column E in the row in the new data file equals any row column E in the master file. If the the value for column E doesn't already exist in the master file, I want to copy that row.

Can anyone provide a suggestion or point me in the right direction?

Thanks!

Hello!

I have to copy data from one worksheet (which regenerates data every week) to another worksheet, with the following conditions

1. Column A should have today's date
2. Source sheet has columns A-W. Target sheet should have this same data from B-X
3. New data from the next week should be copied below the existing data

I am very new to excel macros and would appreciate any help with the code.

Thanks in advance,

Mihir

Hi,

I am trying to copy values from one workbook to another. I will be copying
many ranges.

The code below is my attempt at a simplified version. I got to the last
line where the vba code died.

Can someone please point out where I went wrong? Thank you.

Best regards,
Kevin

Sub GetData()

Dim wkbkSource As Workbook
Dim oSourceSheet As Object

Dim sSourceBook As String
Dim sSourceSheet As String

Dim oCurrentSheet As Object 'sets activesheet as oCurrentSheet

Set oCurrentSheet = ActiveSheet

sSourceBook = oCurrentSheet.Range("xsSWkBkName")
' "Source.xls" was successfully transferred to sSourceBook

sSourceSheet = oCurrentSheet.Range("xsSWkShtName")
' "SourceSheet" was successfully transferred to sSourceSheet

On Error Resume Next
Set wkbkSource = Workbooks(sSourceBook)
On Error GoTo 0
If wkbkSource Is Nothing Then
Set wkbkSource = Workbooks.Open(sSourceBook)
End If

Set oSourceSheet = wkbkSource.Worksheets(sSourceSheet)

' Step below fails
' I am trying to copy the values from the Source Sheet in the Source
Workbook
' to the Current Sheet.
' Run time error 438, Object doesn't support this property or method.

oCurrentSheet.Range("DestRange").Values =
oSourceSheet.Range("MySourceRange").Values

End Sub

Good Morning

With the help of others I have managed to complete a Working Macro to move Rows from One Workbook to Another and place it in the first open Row of the secord workbook. The Macro then leaves the inserted row with Cell L copied to the clipboard.

Here is code that it working for me adequatly to get that job done

	VB:
	
 OpentoSold() 
     '
     ' OpentoSold Macro
     ' Macro recorded 2/1/2008 by Mike
     '
     ' Keyboard Shortcut: Ctrl+q
     '
    Dim objLastRow As Range 
    Dim lastRow As Integer 
     
    Selection.Cut 
    Windows("Amazon Open.xls").Activate 
     
    Windows("Amazon Sold.xls").Activate '
     
     
    Set objLastRow = ActiveSheet.Cells.SpecialCells(xlLastCell) 
     
    lastRow = objLastRow.Row + 1 
     
    Rows(lastRow).Select 
    ActiveSheet.Paste 
     
    Range("L" & lastRow).Select 
     
    Selection.Copy 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Can I add code to this Macro to paste that cell's text into an ALREADY OPEN word document named AmazonSale.Doc , AT THE PLACE WHERE THE CURSOR WAS LEFT when that word doc was last on screen ? When I do this manually and I reactivate the Word Doc to Ctrl+V the text, the cursor does still remain active where it was last left.

Thanks For Reading

I have 3 workbooks I want to copy data from 2 workbooks to the 3rd. when the user opens the 3rd workbook. I know i can use links. But I wanted to do it via VBA code.

Thanks for the help guys.

Hi everyone again. I am here looking for your help on further develop this macro to copy data from one workbook to another. This macro, from the help of arlu1201, can copy data from one workbook to another. However, I have tried for many days to make this macro to copy values and not formulas or links. All the data on “Stats” sheet should be copied as values to workbook report2012.xls. I have used .PasteSpecial or Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
This is the macro the I am currently using:
Option Explicit
Dim MFile As String
Dim lastrow As Long
Dim frow As Long
Dim lrow As Long

Sub update_master()

MFile = "C:KBD projectreport2012.xls"


If MFile = "" Then
    MsgBox "Please open the KBD Shop Floor Reports 2012", vbCritical
End If
    
Workbooks.Open MFile
MFile = ActiveWorkbook.Name

lastrow = ThisWorkbook.Worksheets("Stats").Range("A" & Rows.Count).End(xlUp).Row
ThisWorkbook.Worksheets("Stats").Range("A2:V" & lastrow).Copy _
Workbooks(MFile).Worksheets("Libros").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    
    Workbooks("report2012.xls").Close SaveChanges:=True
End Sub
Could someone explain to me what I am doing wrong? I am very new to VBA, but I am willing to work hard and learn. See attachment for detail.s
Again, thank you everyone, specially my friends for your time and knowledge.

My original macro looked like this:

Sheets("Inv_Load to Lawson").Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks.Open Filename:= _
"X:Legal to Accounting Check RequestsSales TaxslsTax dbapcvi.csv"

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Then someone suggested I try this one - but neither of them work......I am
trying to copy a range of data from one file to another...should be simple
but I can't get it to work!?!?

Sheets("Inv_Load to
Lawson").Select.Range("A1").End(xlToRight).End(xlD own).Copy
Workbooks.Open Filename:= _
"X:Legal to Accounting Check RequestsSales TaxslsTax dbapcvi.csv"

ActiveSheet.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

I have two workbooks. I want to create a macro that copies information from
one workbook, which will be closed, to another workbook, which will be open.
I would also prefer if this macro could run everytime the second workbook is
opened. Is this possible?

I would appreciate any help,

Adam Bush

I am trying to develop a macro that copies data from one workbook to another,
however looks up the date in wb1, then finds the same date in wb2, and copies
values from wb1 beside the matching date in wb2

e.g. wb1 date 07/07/2007 ,wb2 (monthly report, dates from 1/07 to 31/07/2007
in a column) When macro runs, looks up date in wb1 and then copies data from
wb1 to wb2 if if finds same dte in wb2, otherwise says no data to update,

can anyone help???
Thanks Tiger

I'm using a macro to copy from one workbook to another in the same instance
of Excel. The macro successfully copies the data to the clipboard but does
not proceed to paste it in the new workbook. No error screen pops up it just
doesn't paste. Your help is appreciated. Here is my current macro code:

Sub CopyPasteToNCTthree()
'
' CopyPasteToNCTthree Macro
' Macro recorded 4/20/2009 by _
'
' Keyboard Shortcut: Ctrl+Shift+M
'
Sheets("Installer").Select
Range("O2:O8").Select
Selection.Copy
Workbooks.Open Filename:="C:Zip DocumentsCustomersNew Customer
Test.xls"
Range("O2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("O2:O8").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 1
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("O8").Select
End Sub
--
David P.

I need help in creating a macro to Copy rows from one sheet to another sheet in another workbook with all the groups and outline levels.
Thanks in advance.

Hello, I need a macro that will copy data from one workbook and paste it into another. The purpose of this macro is to copy data from a user-friendly sheet to an intermediate pivot table friendly sheet, so it can then be processed into a pivot table.

Also, it would be great if this could be made into a button on the first sheet mentioned above (the user-friendly one that compiles into the pivot table list), so that when it is pressed the macro activates.

I'm new to macros, so please tolerate any questions I may ask. Thanks.

Hi All,

I have defects data exported from Quality center in one workbook(MasterData.xls). However, the exported data does not have header. Hence, I have created another workbook with only the headers(Header.xls).

Now, I need to copy all the data from MasterData workbook and paste it to Header workbook from 2nd row.(First row has header). Could anyone please help me with same? I have attached the two workbooks. Any help would be appreciated.

Thanks,
Ganga

Hi,

I am currently doing multiple copy paste tasks from one workbook to another. I would like to make sure that the way I do it is the most efficient.

My source workbook ( where the information from another workbook will be pasted) is subject to changes (the columns might move but will keep the same header). So the first thing I am doing, is creating a module with a sub that search for the source workbook columns headers and the first empty row.


	VB:
	
 
Public sr_name1 As Variant 
Public sr_name2 As Variant 
Public sr_name3 As Variant 
Public sr_first_row As Variant 
 
Public wbA As Workbook, wbB As Workbook 
Public ws1 As Worksheet, ws2 As Worksheet 
 
 
 
Sub define_column_source() 
    Set wbA = ThisWorkbook 
    Set ws1 = wbA.Sheets("Merge") 
    sr_name1 = ws1.Range("a1:cz3").Find(What:="Name 1", LookIn:=xlValues, LookAt:= _ 
    xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False _ 
    , SearchFormat:=False).Column 
    sr_name2= ws1.Range("a1:cz3").Find(What:="Name 2", LookIn:=xlValues, LookAt:= _ 
    xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False _ 
    , SearchFormat:=False).Column 
    sr_name3 = ws1.Range("a1:cz3").Find(What:="Name 3", LookIn:=xlValues, LookAt:= _ 
    xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False _ 
    , SearchFormat:=False).Column 
     
    sr_first_row = ws1.Range("a1:cz3").Find(What:="Name 1", LookIn:=xlValues, LookAt:= _ 
    xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False _ 
    , SearchFormat:=False).Row + 1 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Then I write another sub in another module where I call the define_column_source sub, open the needed workbook and I copy and paste the information needed to the correct source workbook columns. The other workbook will always have the information starting on the 2nd row.


	VB:
	
 mergefile() 
     
    define_column_source 
     
    Set wbB = Workbooks.Open(ThisWorkbook.Path & "" & "*Workbook1*.xl*", True, True) 
    Set ws2 = wbB.Sheets(1) 
     
    Application.ScreenUpdating = False 
    Application.StatusBar = ("Processing Workbook1...") 
     
    lastrow = ws2.Cells(Rows.Count, "A").End(xlUp).Row 
     
    col_name1 = ws2.Range("a1:cz3").Find(What:="First Name", LookIn:=xlValues, LookAt:= _ 
    xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False _ 
    , SearchFormat:=False).Column 
     
    ws2.Range(Cells(2, col_name1), Cells(lastrow, col_name1)).Copy ws1.Cells(sr_first_row, sr_name1) 
     
    col_name2 = ws2.Range("a1:cz3").Find(What:="Middle Name", LookIn:=xlValues, LookAt:= _ 
    xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False _ 
    , SearchFormat:=False).Column 
     
    ws2.Range(Cells(2, col_name2), Cells(lastrow, col_name2)).Copy ws1.Cells(sr_first_row, sr_name2) 
     
    col_name3 = ws2.Range("a1:cz3").Find(What:="Last Name", LookIn:=xlValues, LookAt:= _ 
    xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False _ 
    , SearchFormat:=False).Column 
     
    ws2.Range(Cells(2, col_name3), Cells(lastrow, col_name3)).Copy ws1.Cells(sr_first_row, sr_name3) 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Is there a more efficient way to do this?

Thank you for your help!

I'm looking for an excel macro that can copy rows from one sheet to another within excel based upon having a specific word in the cell. I have one sheet called master and sheet two called feedback. Here is an example of the data:

A B C D mary bob 56 reply soon sam john 26 keep in touch sylvia pan 15 response needed

I'd like to copy the rows sheet "master" to sheet "feedback" if the data in column D contains either the word reply or the word response somewhere within the paragraph. Additionally I would like to be able to fill in columns E and F in sheet "feedback" once the data has been copied.

Thank you in advance for your help!
hrhr

Hi,

I want to have a macro to retrieve records from one sheet based on criteria from another sheet and then paste the data in a new sheet for every criteria mentioned.

I've attached a workbook with sample data for reference.

In actual, the sheet has huge amount of data, so it's not possible to filter the data and then copy paste it.

I would really appreciate if someone can help me out in this.

Thanks in advance!

Hi Guys,

I am trying to copy text from one workbook to another with a formula, I have tried formatting the cells as text and general but no luck. I tried Concatenate and got the formula displayed but not the result.

Can anybody help

Thanks

Shaun1007

Hi there,

A while ago I created a very simple excel workbook ("Claim Injector") that opened up another more complicated excel workbook (master document) un-protected the master document and "un-hid" specific sheets and then allowed the user to copy inputted data over to the other workbook. It would then allow the user at the touch of a button to re-protect the document and hide all the specific sheets etc.

Basically it allowed members of my team to input data into a protected master document without the risk of them screwing the master documents code up. Shhh don't tell them I said that!

The macros behind all that is below:

Code:
Sub START_INJECTOR()
'
' START_INJECTOR Macro opens and unprotects claim

'
 Dim wb As Workbook
    Application.EnableEvents = False
    Set wb = Workbooks.Open("L:e-Claim FormsClaim_v3.xls")
  
        
    Windows("Claim_v3.xls").Activate
    ActiveWorkbook.Unprotect Password:="******"
    Sheets("Project Information").Visible = True
    
    Windows("Claim injector.xls").Activate
End Sub
Code:
Sub INJECT_PROJ()
'
' INJECT Macro injects project details into claim

'
    Range("C10:C13").Copy
    Windows("Claim_v3.xls").Activate
    Sheets("Project Information").Select
    Range("N7:N10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Windows("Claim injector.xls").Activate
End Sub
Code:
Sub INJECT_MEASURE()

' INJECT MEASURE injects measure details into claim

Range("G13").Copy
    Windows("Claim_V3.xls").Activate
    Sheets("Project Information").Select
    Range("K20").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Windows("Claim injector.xls").Activate

End Sub
Code:
Sub INJECT_CAP()
'
' INJECT Macro Injects Capital details into claim

'
    Range("B17:C31").Copy
    Windows("Claim_v3.xls").Activate
    Sheets("Project Information").Select
    Range("M14:N28").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Claim injector.xls").Activate
End Sub
Code:
Sub INJECT_REV()
'
' INJECT Macro Injects Revenue details into claim



'
    Range("B34:C48").Copy
    Windows("Claim_v3.xls").Activate
    Sheets("Project Information").Select
    Range("M31:N45").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Claim injector.xls").Activate
End Sub
Code:
Sub FINISH_INJECTOR()
'
' FINISH_INJECTOR Macro reprotects claim


'

    Windows("Claim_v3.xls").Activate
    
    ActiveWindow.SelectedSheets.Visible = False
    Application.CutCopyMode = False
    
    ActiveWorkbook.Protect Password:="******", Structure:=True, Windows:=False
    
    Exit Sub
    
    Application.EnableEvents = True
   
    
End Sub
The "INJECT" macros are assigned to buttons on my "Claim Injector". The user fills in the data and then presses the "Inject" button next to what they have filled in to copy and paste into the master document. So after each "Injection" the "Claim Injector" is re-activated so data input can continue.

On the "Claim Injector" there is a "Start" and "Finish" button at the top and bottom respectively. The Start button runs the macro "START_INJECTOR" and the Finish button runs the "FINISH_INJECT" macro.

Now all that works fine....up until now! The above is all well and good as the document I am copying data into is a constant "Claim_v3.xls" and in a constant location. I now need to be able to select the document I wish to "Inject" into as a variable.

So....the "START INJECTOR" Macro needs to allow me to Open up any workbook using Windows explorer and browse and select the specific file. The password of the any of the workbooks that will be opened by the "Claim Injector" will be all the same as it currently is, aswell as the cell ranges that are being pasted to. Just the name of opened workbook will be different.

I will need the following code somewhere in the "START INJECTOR" macro:

Code:
Sub RetrieveFileName()
Dim sFileName As String

	'Show the open dialog and pass the selected _
	file name to the String variable "sFileName"
	
	sFileName = Application.GetOpenFilename
	'They have cancelled.
	If sFileName = "False" Then Exit Sub
	MsgBox sFileName
End Sub
But I don't know where to put it for a start and then how do make every referal in my current code to Code:
 actually refer to the workbook that has been opened.

I think I understand the principle of what I need to do and that is to define the variable (that is the name of the opened workbook) as a constant string within my code and then simply replace all occurances of Code:
 with the name of my defined variable....I just haven't got a clue how to actually do that.

Hopefully that makes sense. Thankyou in advance.

Regards
Geff