Copy data from one workbook to another

[Coldfire46]

Hi all,

I'm writing some code (see below) to add to a command button on a worksheet and seem to be having some trouble. All I want to do is select a row a data and copy it from one workbook into the first empty row of a worksheet in a different workbook.
The code seems to work when i = 2, but seems to fall down when it comes to i = 3 etc; any advice on where I'm going wrong would be really useful.

Private Sub CommandButton2_Click()
Application.ScreenUpdating = False
Set wsID = ThisWorkbook.Worksheets("Export to Auto-Loader")
LastRow = wsID.Range("A1:A" & Rows.Count).Find(what:="*", searchdirection:=xlPrevious, after:=[A1]).Row
    For i = 2 To LastRow
        wsID.Range(Cells(i, 1), Cells(i, 20)).Select
        Selection.Copy
Let Path = ThisWorkbook.Path
Workbooks.Open Filename:=Path & "\ProPricerImportSheet.xlsm"
p = Worksheets.Count
    For q = 1 To p
        If ActiveWorkbook.Worksheets(q).Name = "Task Auto Import" Then
        Worksheets("Task Auto Import").Select
    End If
    Next q
EmptyRow = ActiveSheet.Range("A1:A" & Rows.Count).Find(what:="*", searchdirection:=xlPrevious, after:=[A1]).Row
ActiveSheet.Cells(EmptyRow, 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Next i
Application.ScreenUpdating = True
End Sub

 

[Snakehips]

Coldfire46,

The Main reason your code fails after i = 2 is that the ProPricerImportSheet is still the active workbook when you loop and increment i to 3. You need to Activate the Export sheet, within the loop, before you can select row 3. Then there would be another error / alert when you will be try to open the Import workbook again whilst it is already open !!

I can help you tidy this code up if you can answer the following .....

If I understand correctly, you are trying to copy the range A2:T?? ?
Are you needing to use Copy and Paste in order to copy formatting or do you just want to copy values?
Is there any reason why you are looping row by row rather than do the whole range in one go?
Any reason why you are looping through the sheets rather than going direct to Task Auto Import ?
Should EmptyRow not be as per your code +1 ?

You are determining LastRow & EmptyRow ok but not in the more accepted way. Any reason?

Hope that helps.

 

[Coldfire46]

Hi Tony,

Many thanks for coming back to me; in answer to your questions:
Yes I am trying to copy A2:T??
I just want to copy the values across, the formatting is not important
There is no reason why I'm doing it one row at a time, it could be done all in one go
I'm looping through the sheets because there might be more than one worksheet entitled Summary, so I want the code to work on all sheets that have summary in the title
Yes Empty Row should be Last Row + 1 (no idea why I decided not to do it that way)
The reason for my slightly odd way of finding the last row is that the sheet that I'm copying the data into has a row with text at the very bottom of the worksheet (tell the user not to input anything after that row), but that isn't where the data ends so I've had to use a slightly different way to find the end

Any tidying up tips you can offer would be fantastic; you can probably tell from my code that I'm very new to VBA and so am trying to teach myself (I've got a very long way to go).

 

[Snakehips]

Thanks for that.

Can you clarify..
Your original code makes no mention of Summary as part of a sheet name? You are coding on a specific sheet... Task Auto Import ???

 

[Coldfire46]

My apologies Tony, I'm trying to write two different bits of code at the moment, I mentioned the summary tabs in error.
The code should not loop through any sheets, I just want to take the data and copy it into the second workbook (PropricerImportSheet)...

 

[Snakehips]

Sorry for the delay.

***** Assuming that the Import sheet is not open when you start and that you want to close and save it when done then something like...

Private Sub CommandButton2_Click()
Application.ScreenUpdating = False
Set wsID = ThisWorkbook.Worksheets("Export to Auto-Loader")
LastRow = wsID.Range("A1:A" & Rows.Count).Find(what:="*", searchdirection:=xlPrevious, after:=[A1]).Row
Set SrcRng = wsID.Range("A2:T" & LastRow)


Let Path = ThisWorkbook.Path
Workbooks.Open Filename:=Path & "\ProPricerImportSheet.xlsm"  '*****
With Sheets("Task Auto Import")
    Emptyrow = .Range("A1:A" & Rows.Count).Find(what:="*", searchdirection:=xlPrevious, after:=[A1]).Row + 1
    .Range(.Cells(Emptyrow, 1), .Cells(Emptyrow + LastRow - 2, 20)).Value = SrcRng.Value
End With
Application.ScreenUpdating = True


ActiveWorkbook.Close SaveChanges:=True  '*****  Rem out if you wish to close manually
MsgBox "Update complete"
End Sub

Hope that helps.

 

[Coldfire46]

Just tested this code and it works perfectly...I really appreciate your time and effort in helping me Tony

 

[Snakehips]

You are welcome.