0

I am new to VBA programming. I want to import the entire row/(s)(example range from column "A" to "BT")that contains only current (today's) date (example, date is in column "B5") from a source excel sheet into current(backup excel)excel sheet automatically without opening the source excel.So, that I can backup my data regularly at the end of the day from backup excel file. Actually, the source worksheet is being updated regularly and in a day I have 5-10 new rows with the same date(current/today's) and so I want to copy all the new data(s) to backup excel sheet in rows next to previous day's backup in a backup excel file every day. Thank you The below VBA code seems working but it is copying the only column of interest but I need the entire rows to be copied. Do you have any idea to modify the below VBA code so that, it will also copy entire rows?

Option Explicit

Private Sub Workbook_Open() Call ReadDataFromCloseFile End Sub

Sub ReadDataFromCloseFile() On Error GoTo ErrHandler Application.ScreenUpdating = False

Dim src As Workbook ' OPEN THE SOURCE EXCEL WORKBOOK IN "READ ONLY MODE". Set src = Workbooks.Open("C:\Users\HAMENDRA\Desktop\Equation\Otarry.xlsm", True, True) ' GET THE TOTAL ROWS FROM THE SOURCE WORKBOOK. Dim iTotalRows As Integer iTotalRows = src.Worksheets("Records").Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row).Rows.Count ' COPY DATA FROM SOURCE (CLOSE WORKGROUP) TO THE DESTINATION WORKBOOK. Dim iCnt As Integer ' COUNTER. For iCnt = 1 To iTotalRows Worksheets("Sheet1").Range("B" & iCnt).Formula = src.Worksheets("Records").Range("B" & iCnt).Formula Next iCnt ' CLOSE THE SOURCE FILE. src.Close False ' FALSE - DON'T SAVE THE SOURCE FILE. Set src = Nothing

ErrHandler: Application.EnableEvents = True Application.ScreenUpdating = True End Sub

Improve this question
4
  • look at using ADO and SQL Commented Jul 13, 2017 at 9:55
  • The above vba code is working correctly, but the only thing is it is pulling only single column. Where as I need the entire row to get updated. Can you throught a light to the above vba code. Commented Jul 15, 2017 at 6:55
  • How is it working? Your post is how to get data from a workbook without opening it, you are opening it with Workbooks.Open("C:\Users\HAMENDRA\Desktop\Equation\Otarry.xlsm", True, True) change your question title. Commented Jul 15, 2017 at 11:16
  • Yes, you are correct in the sense. I just want to mean that not physically. Commented Jul 15, 2017 at 18:03

1 Answer 1

Reset to default
0

Replace:

Worksheets("Sheet1").Range("B" & iCnt).Formula = src.Worksheets("Records").Range("B" & iCnt).Formula

With:

Worksheets("Sheet1").Range("B" & iCnt).EntireRow.Formula = src.Worksheets("Records").Range("B" & iCnt).EntireRow.Formula

But, you should also edit the title of this post. As the previous commentor said, you are opening the workbook. I'd suggest something like "How to copy entire row using VBA". Of course, if you had just googled "How to copy entire row VBA," you probably would have found the answer. :-)

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.