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

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