Help withVB to copy last entry from one workbook/worksheet to a seperate workbook/worksheet

[sparkd]

I currently have a workbook called "v6.xlsm" and sheet named "Log"

What Im having difficulty codng is:

1. Copy the last "non blank" Column A and Column B cells from the workbook "v6.xlsm" sheet name "Log"
2. Open a separate workbook called "v7.xls"
3. Open the worksheet on this workbook called "Log"
4. Paste values only into the first available blank cells after row1 (A and B)

 

[sparkd]

Im not even sure that this can be done using VB?

 

[Fluff]

Will the values in cols A & B always be on the same row?
Also do you have formulae in those columns?

 

[sparkd]

Will the values in cols A & B always be on the same row?
Also do you have formulae in those columns?

• Col A & B values don't have any formulas
• No the values in cols A&B will keeping changing down the worksheet

eg

Scenario One: If they are three existing rows of data with Col A&B populated) and the worksheet is accessed then row 4 is auto populated) In this instance I would need the code to copy row 4 (A and B) values

Scenario Two: If they are four existing rows of data with Col A&B populated) and the worksheet is accessed then row 5 is auto populated) In this instance I would need the code to copy row 5 (A and B) values

 

[Fluff]

How about

Sub CopyRows()
   Dim Wbk As Workbook
   Dim Ws As Worksheet
   Dim Fname As String
   
   Set Ws = Sheets("Log")
   Fname = "[COLOR=#ff0000]C:\Mrexcel[/COLOR]\v7.xls"
   Set Wbk = Workbooks.Open(Fname)
   Wbk.Sheets("Log").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 2).Value = Ws.Range("A" & Rows.Count).End(xlUp).Resize(, 2).Value
   [COLOR=#0000ff]Wbk.Close True[/COLOR]
End Sub

Change the part in red to the path where v7 can be found.
If you don't want to save & close v7.xls then remove the line in blue

 

[sparkd]

Hi Fluff


This is brilliant and exactly what I needed. Much appreciated.
Only one question for my own knowledge.
I specified for the paste to occur after the first blank row AFTER row one on the 'receiving workbook'

How do I change you code to IGNORE the after row one.

i.e. start from row one not row two

 

[Fluff]

Like

Sub CopyRows()
   Dim Wbk As Workbook
   Dim Ws As Worksheet
   Dim Fname As String
   
   Set Ws = Sheets("Log")
   Fname = "C:\Mrexcel\v7.xls"
   Set Wbk = Workbooks.Open(Fname)
   If Wbk.Sheets("Log").Range("A1") = "" Then
      Wbk.Sheets("Log").Range("A1").Resize(, 2).Value = Ws.Range("A" & Rows.Count).End(xlUp).Resize(, 2).Value
   Else
      Wbk.Sheets("Log").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 2).Value = Ws.Range("A" & Rows.Count).End(xlUp).Resize(, 2).Value
   End If
   Wbk.Close True
End Sub