[RESOLVED] Copy From One WorkBook To Another: Excel VBA

I have to string variables:

Example: strFirstFile and strSecondFile

The variables are populated as follows:

strFirstFile = "c:\hack.xls"
strSecondFile = "c:\vbf.xls"

Now, in Excel VBA code, I need to copy the contents of G:14, which is on a Worksheet called "Data" in strFirstFile to E:16, which is on a Worksheet called "MyDate" on strSecondFile

How would I do that?

Hi Hack

I am assuming that both excel files are closed and you are doing what you want to do from a 3rd file...

vb Code:

Sub COPYCELL()
Dim wbk As Workbook
 
strFirstFile = "c:\hack.xls"
strSecondFile = "c:\vbf.xls"
 
Set wbk = Workbooks.Open(strFirstFile)
With wbk.Sheets("Data")
    Range("G14").Copy
End With
 
Set wbk = Workbooks.Open(strSecondFile)
With wbk.Sheets("MyDate")
    Range("E16").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
End With
 
End Sub

Hope this helps....

Actually, both files are open at the time (that part I've got)

I think lines 12 though 16 of what you posted is what I was looking for.

Curiousity question: Why PasteSpecial as opposed to simply Paste?

I will give this a shot.

you can use paste as well. Paste is the same as Paste:=xlPasteAll

it's just that at a later stage if i want only values to be pasted then all I have to do is to change

Paste:=xlPasteAll

to

Paste:=xlPasteValues
then

Does PasteSpecial do something that xlPasteAll doesn't?




Signed,

VBA noob :D

Quote:

---

Originally Posted by Hack

Does PasteSpecial do something that xlPasteAll doesn't?

---

Yes it does

It gives you the flexibility to choose the way you want your data to be pasted

the different options are

Paste:=xlPasteAll => Does the same function as paste
Paste:=xlPasteFormulas => Pastes only the formulas
Paste:=xlPasteValues => Pastes only the values
Paste:=xlPasteFormats => Pastes only the formats
Paste:=xlPasteComments => Pastes only the comments
Paste:=xlPasteValidation => Pastes only the validation
Paste:=xlPasteAllExceptBorders => Pastes everything except the borders in the cell

and so on...

So, would

Range("E16").PasteSpecial Paste:=xlPasteAll

be the same as saying

Range("E16").Paste:=xlPasteAll

Quote:

---

Originally Posted by Hack

So, would

Range("E16").PasteSpecial Paste:=xlPasteAll

be the same as saying

Range("E16").Paste:=xlPasteAll

---

No it would be the same as

Range("E16").Paste

Quote:

---

Originally Posted by koolsid

Hi Hack

I am assuming that both excel files are closed and you are doing what you want to do from a 3rd file...

vb Code:

Sub COPYCELL()
Dim wbk As Workbook
 
strFirstFile = "c:\hack.xls"
strSecondFile = "c:\vbf.xls"
 
Set wbk = Workbooks.Open(strFirstFile)
With wbk.Sheets("Data")
    Range("G14").Copy
End With
 
Set wbk = Workbooks.Open(strSecondFile)
With wbk.Sheets("MyDate")
    Range("E16").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
End With
 
End Sub

Hope this helps....

---

This seems to be working *crosses fingers and toes*

Quote:

---

This seems to be working *crosses fingers and toes*

---

I am sorry, I am not familiar with this phrase :blush:

What does it mean?

It brings good luck - it is an American Idiom. :D

Got an oddity here that perhaps you can explain. Actually, this didn't work

Code:

---

With wbk.Sheets("Data")
    Range("G14").Copy
End With

Set wbk = Workbooks.Open(strSecondFile)
With wbk.Sheets("MyDate")
    Range("E16").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
End With

---

but, this did

Code:

---

wbk.Sheets("Data").Range("G14").Copy

wbk.Sheets("MyDate").Range("E16").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False

---

Any idea why removing the code from the With/End With block would make a difference?

Quote:

---

Originally Posted by Hack

It brings good luck - it is an American Idiom. :D

Got an oddity here that perhaps you can explain. Actually, this didn't work

Code:

---

With wbk.Sheets("Data")
    Range("G14").Copy
End With

Set wbk = Workbooks.Open(strSecondFile)
With wbk.Sheets("MyDate")
    Range("E16").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
End With

---

but, this did

Code:

---

wbk.Sheets("Data").Range("G14").Copy

wbk.Sheets("MyDate").Range("E16").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False

---

Any idea why removing the code from the With/End With block would make a difference?

---

I just tried the code that I posted and it worked :confused:

The code that you have posted is also correct :afrog:

In fact they both are same though they have been written differently....

I think I know what the problem is...

Try this 3rd piece of code which is very similar to the first piece of code.

vb Code:

Sub COPYCELL()
Dim wbk As Workbook
 
strFirstFile = "c:\hack.xls"
strSecondFile = "c:\vbf.xls"
 
Set wbk = Workbooks.Open(strFirstFile)
With wbk.Sheets("Data")
    .Range("G14").Copy
End With
 
Set wbk = Workbooks.Open(strSecondFile)
With wbk.Sheets("MyDate")
    .Range("E16").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
End With
 
End Sub

If you will notice there is a dot(.) before the Range between the With - End With. I think this will work in your system. Give it a try...

Quote:

---

Originally Posted by koolsid

If you will notice there is a dot(.) before the Range between the With - End With. I think this will work in your system. Give it a try...

---

Yes, your are right, and when I put them all on the same line without the With/End With I had to add the . before Range to connect the two lines.

That makes sense.

Although, it works better all on one line as I have about 20 different numbers to copy from one worksheet to another so doing one copy then a paste then another copy/paste etc seems to be just the ticket.

I wonder why, without the . before Range in the With/End With structure, it didn't give me an error? :confused:

Quote:

---

Originally Posted by Hack

I wonder why, without the . before Range in the With/End With structure, it didn't give me an error? :confused:

---

exactly my thoughts, because all three codes run absolutely fine with me....

let me see if i can replicate the same in excel 97. i am using 2003 at the moment.

I'm also using 2003, but a syntax error should be a syntax error no matter what it would seem to me.

Quote:

---

Originally Posted by Hack

I'm also using 2003, but a syntax error should be a syntax error no matter what it would seem to me.

---

Yes you are right but since all the three codes are working for me right now... I will leave "delving" into this microsoft's mystery(:rolleyes:) to the much braver. :lol:

Just a note; you don't need to qualify the Range object; Excel automatically assumes that you mean the range on the worksheet that you are in unless you tell it differently. So as far as Excel was concerned, I suspect it wasn't an error at all, it just wasn't using the range you were expecting.

If you use Range in a module and then call it from a button, Excel will figure out which sheet you called the sub from and apply your code to that.

Quote:

---

Originally Posted by zaza

If you use Range in a module and then call it from a button, Excel will figure out which sheet you called the sub from and apply your code to that.

---

Thanks zaza....this is good to know. :thumb:

code is working...but plas let me know if we want to copy data from more than one file and paste in different sheets of the one master workbook.

Would appeciate if u could answer this asap..

Thnks in advance..

strFirstFile and strSecondFile what you dim as ?

Quote:

---

strFirstFile and strSecondFile what you dim as ?

---

strings

Hi Everyone,

I cant seem to figure out how to copy complete column. I need to copy column A,C and D from one file to another. Not only one cell but complete 3 columns from one excel file to another.

Please help.

Thanks,
Ahtasham

try

vb Code:

workbooks("book1").sheets(1).range("b:e").value = workbooks("somebook.xls").sheets("sheet3").range("b:e")

change workbook and sheet names /indexes to suit

I didnt understand your post....Where can I give the whole column. As I said before I have my excel file with many columns and to much data in it....I only need three column from that huge excel file. So I have created a button in that excel file and am now trying to somehow put some code together so when I click on that button a new file is created and the columns A,C and D with the contents are copied to the new created excel file.

So where can I give this column names and other stuff.


Thanks,

i must have misread somewhere
as the columns are non-contiguous, you can either build a range using union or just copy the 3 columns one after the other
range("a:a")
to copy all column A, same for other columns

if you only want to copy to the end of the data you can specify like
range("a1:a" & range("a" & rows.count).end(xlup).row)

can you please tell me how I can reference the current sheet. I mean I have the current sheet "Data". and I have a button on that sheet on that button's onClick, I want to copy the column A,C and D from the same sheet (Data) to another files sheet1.

So how can I reference the current sheet and so I can select its column A and then copy that to the external file.

Thanks,

to add little more...

strFirstFile = "E:\bvl221.xls"
strSecondFile = "E:\bvl221-new.xls"

Set wbk = Workbooks.Open(strFirstFile)

With wbk.Sheets("Data")
.Range("a:a").Copy
End With

as you can see the first file is the current file. so currently the code is trying to open the file and then copy. but the file is already open because I am working in that file and the click is called in that file. So how can I copy the column "a" from the current sheet without creating new instance of that file.

Looking forward to your quick response.

Thanks,

vb Code:

set wbk= workbooks(strfirstfile)
With wbk.Sheets("Data")
.Range("a:a").Copy
End With
set secondfile = workbooks.add  ' or if existing use open
' copy the data
secondfile.saveas strsecondfile

Lots of thanks of your quick replies. Just one last quick question.

here is my code....

Dim wbk As Workbook
Dim strFirstFile As String
Dim strSecondFile As String

strFirstFile = "bvl221.xls"
strSecondFile = "E:\bvl221-new.xls"

Set wbk = Workbooks(strFirstFile)
With wbk.Sheets("Data")
.Range("a:d").Copy
End With
Set wbk = Workbooks.Open(strSecondFile)
With wbk.Sheets("Sheet1")
.Range("a:d").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With
wbk.SaveAs strSecondFile
wbk.Close


as you can see I am closing the new file at the end. but it asks me that the file already exists. Do you want to overwrite. Can I somehow jump this message. I mean when saving the file passing some argument that if it exists, overwrite the file and force save.

Let me know please. else all is done.

Thanks,

as the file already exists,
you should just use wbk.save instead of saveAs

alternatively you could use application.displayalerts = false, before that line to prevent the overwrite dialog from showing, remember to set displayalerts back to true after

Thanks for all the efforts...all solved...


Ahtasham

Hello, I'm reading this post and i find that is close to what i need, but the code does not seem to work, keeps giving me "run-time error 9"

I'm sending the file that i'm using, please verify the code and let me know.

Thanks in advance

Dear all,
I have a similar problem. I have a lot of files with slightly different order and column names. Is there a way to make a user input form that your say:
column 1 file 1 = (user enters the name)
corrensponds to column (user enters the name) in file 2
and so on for e.g. 10 columns

Thank you in advance!
Best regards

sorry forgot to mention that I want to copy the columns from file 2 to file 1 but to the right place (corresponding column)

hello laguerriere:
I don't undestan completly what is your need. please explain a better example, and if you can upload files to show your example

Thanks

Ok sorry for not being clear. I upload two files - this is a simplified version of what I have. File A: columns order is the following:

Invoice date Tax code Tax basis Tax amount Invoice number

now , I would like to merge file a and file b , i.e. copy data from file b to file a. But, the order in file b is not the same:

Tax amount Tax basis Tax code Invoice number Invoice date

Of cource, this simple example, I would do it manually. But imagine 20 files and 20 columns. The name of the columns is always the same, and I would like the macro to find the right column to copy it to. For example, if I want to copy invoice dates:
1) go to file b, search for "invoice dates" --> col 5
2) copy all data from a5 to "last row" in the col 5
3) go back to file a, col 1 (invoice dates)
4) search for last row + 1 (first empty cell after data), paste data from file b
5) etc. for all columns.

Is this possible?
Thank you.

Hello Laguerriere....

These is the code.

vb Code:

Sub Copy()
     
    Dim i As Integer
    Dim Source As Workbook
    Dim LastRow As Long, LastRow1 As Long
    Dim LastRow2 As Long, LastRow3 As Long
    Dim LastRow4 As Long
    Dim OriginalWorkBook As Workbook
    Set OriginalWorkBook = ThisWorkbook
    
    Const MyDir As String = "C:\Users\Shadow\Desktop\file a&b\"
    Set Source = Workbooks.Open(MyDir & "file b.xlsx")
    Source.Sheets("Tabelle1").Activate
    
    Application.ScreenUpdating = False
      LastRow = Source.Worksheets("Tabelle1").Range("a" & Rows.Count).End(xlUp).Row
  
    Source.Worksheets("Tabelle1").Range(Cells(2, 1), Cells(LastRow, 1)).Select
    Selection.Copy
    OriginalWorkBook.Activate
    i = OriginalWorkBook.Worksheets("Tabelle1").Cells(Rows.Count, 4).End(xlUp).Row
OriginalWorkBook.Worksheets("Tabelle1").Cells(i + 1, 4).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
   
Source.Sheets("Tabelle1").Activate
 LastRow1 = Source.Worksheets("Tabelle1").Range("b" & Rows.Count).End(xlUp).Row
  Source.Worksheets("Tabelle1").Range(Cells(2, 2), Cells(LastRow1, 2)).Select
    Selection.Copy
OriginalWorkBook.Activate
    i = OriginalWorkBook.Worksheets("Tabelle1").Cells(Rows.Count, 2).End(xlUp).Row
OriginalWorkBook.Worksheets("Tabelle1").Cells(i + 1, 2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
 
Source.Sheets("Tabelle1").Activate
 LastRow2 = Source.Worksheets("Tabelle1").Range("c" & Rows.Count).End(xlUp).Row
  Source.Worksheets("Tabelle1").Range(Cells(2, 3), Cells(LastRow2, 3)).Select
    Selection.Copy
OriginalWorkBook.Activate
    i = OriginalWorkBook.Worksheets("Tabelle1").Cells(Rows.Count, 3).End(xlUp).Row
OriginalWorkBook.Worksheets("Tabelle1").Cells(i + 1, 3).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        
Source.Sheets("Tabelle1").Activate
 LastRow3 = Source.Worksheets("Tabelle1").Range("d" & Rows.Count).End(xlUp).Row
  Source.Worksheets("Tabelle1").Range(Cells(2, 4), Cells(LastRow3, 4)).Select
    Selection.Copy
OriginalWorkBook.Activate
    i = OriginalWorkBook.Worksheets("Tabelle1").Cells(Rows.Count, 5).End(xlUp).Row
OriginalWorkBook.Worksheets("Tabelle1").Cells(i + 1, 5).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
 
Source.Sheets("Tabelle1").Activate
 LastRow4 = Source.Worksheets("Tabelle1").Range("d" & Rows.Count).End(xlUp).Row
  Source.Worksheets("Tabelle1").Range(Cells(2, 5), Cells(LastRow4, 5)).Select
    Selection.Copy
OriginalWorkBook.Activate
    i = OriginalWorkBook.Worksheets("Tabelle1").Cells(Rows.Count, 1).End(xlUp).Row
OriginalWorkBook.Worksheets("Tabelle1").Cells(i + 1, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        
Application.ScreenUpdating = True
     End Sub

and i will upload the files with the macro for your testing. The code is not the best, you could make it much sorter if you use a loop. i found that they are 2 drawback from the way i understood
.... the columns in both files are always located where you have them in the example... and the file where you are coping the data is always called file b.xlsx....
but these could give you a pretty good idea of the solution, you can make the necessary changes.

thank you very much for your time!

my problem was exactly this: ideally, the macro would not use the "place" of columns but the columns titles, because this is what does not vary from file to file... If it was just the place, I would just record a macro...

Thank you so much again and maybe someone else could help.

Ok this is the code to make it loop and search for the correct column name

vb Code:

Sub Copy()
     
    Dim i As Integer, x As Long, z As Long
    Dim Source As Workbook
    Dim LastRow As Long
    Dim OriginalWorkBook As Workbook
    Set OriginalWorkBook = ThisWorkbook
    
    Const MyDir As String = "C:\Users\Shadow\Desktop\file a&b\"
    Set Source = Workbooks.Open(MyDir & "file b.xlsx")
    
    
    Application.ScreenUpdating = False
    For x = 1 To 5
     Source.Sheets("Tabelle1").Activate
      For z = 1 To 5
   If OriginalWorkBook.Worksheets("Tabelle1").Cells(1, x) = Source.Worksheets("Tabelle1").Cells(1, z) Then
   
    LastRow = Source.Worksheets("Tabelle1").Cells(Rows.Count, z).End(xlUp).Row
  
    Source.Worksheets("Tabelle1").Range(Cells(2, z), Cells(LastRow, z)).Select
    Selection.Copy
    OriginalWorkBook.Activate
    i = OriginalWorkBook.Worksheets("Tabelle1").Cells(Rows.Count, x).End(xlUp).Row
OriginalWorkBook.Worksheets("Tabelle1").Cells(i + 1, x).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
   End If
   Next z
   z = 0
Next x
        
Application.ScreenUpdating = True
     End Sub

Hope this helps and does what you need.... and like you said, if someone else wants to help. go ahead.

you have to change the

Const MyDir As String = "C:\Users\Shadow\Desktop\file a&b\"

to the correct address in your computer

Dim wbk As Workbook
strSecondFile = "D:\Excel\BOE.xls"
Set wbk = Workbooks.Open(strSecondFile)


in the 3rd line i received an error.Error is Subscript out of range.But the excel file present in the same location.If u know answer reply me soon.

I'm testing it and it seems to work fine.... verify the extension on the file and the actual address. that error is given when the file can't be found.... try closing the file when running the script... it may help

Dear All

I have been searching for a VBA solution to my problem. On reading the Posts here, I realised they relate almost to what I need. I should be very grateful to forum members for help, please.

I have the following two sheets in Excel:

a) Sheet 1 named (Receipts) with 2 columns as follows:

Column A = Invoice No.
Column B = Date Paid (current receipts)

b) Sheet 2 named (DataTable) with about 8 columns, which include the following:

Column A = Invoice No.
Column G = Date Paid

The invoice numbers in Sheet 1 column A are also available in Sheet 2 column A.

Column G in Sheet 2 contains both the actual dates invoices were paid (in dd/mm/yyyy format) and also the text ‘UNPAID’ for those invoices not yet paid.

I need help with a VBA code that will replace the texts ‘UNPAID’ in whichever cells they appear in Sheet 2 Column G, with the actual dates paid, as shown in Sheet 1 Column B, in respect of all the invoice numbers listed also in Sheet 1 Column A.

IN SUMMARY:
a) Go to Sheet 1 (named ‘Receipts’)
b) Pick the invoice nos. in Column A including their related ‘dates paid’ in Column B.
c) Go to Sheet 2 (named ‘Data Table’)
d) Search Column A for the invoice nos. picked from Sheet 1 (named ‘Receipts’)
e) For each matching invoice no. found in Sheet 2 (Data Table), replace the text ‘UNPAID’ which appears against that invoice no. in Column G with the ‘date paid’ value picked up from Sheet 1.
f) All invoice nos. from Sheet 1 Column A that already have ‘date paid’ values in Sheet 2 Column G against their corresponding nos. should be ignored, (i.e. no action).

I have attached a small sample of the sheets referred to. I need help with this as the entries I am regularly dealing with run into hundreds of rows. The Data Table (Sheet 2) is regularly updated with new unpaid invoices. At the same time, Sheet 1 (Receipts) is also updated with very many paid invoices nos. and dates added regularly.

Thanks all in advance.

Kenny