-1

I need to copy the same range (L18:L22) From all worksheets in workbook1 and then paste the data into Sheet1 of workbook2. The data from the 1st worksheet will go in the first empty cell of column A, the data from the 2nd worksheet will go in the first empty cell of column B...etc. I've only been able to copy data from one worksheet and got stuck trying to do it for all worksheets.

It only copies from my worksheet "M-025" but I got stuck when trying to modify it to copy the same range in all the worksheets. 

Sub TorqueData()

    Dim sBook_t As String
    Dim sBook_s As String
    Dim sSheet_t As String
    Dim sSheet_s As String

    sBook_t = "Opta Comms Export.xlsm"
    sBook_s = "Master Calibration Data - Num10.xlsm"
    lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
    sSheet_s = "Sheet1"
    sSheet_t = "M-025"
    Workbooks(sBook_t).Sheets(sSheet_t).Range("L18:L22").Copy
    Workbooks(sBook_s).Sheets(sSheet_s).Range ("A" & lMaxRows + 1)
End Sub
Community
  • 1
  • 1
CMC6
  • 1

1 Answers1

0

Check the below code. I have written as per your requirement. Do the minor adjustment if required as code is not tested.

Sub TorqueData()
Dim wbSrc As Workbook
Dim wbDest As Workbook
Dim lastrow As Long

Set wbDest = Workbooks.Open("Master Calibration Data - Num10.xlsm")
Set wbSrc = Workbooks.Open("Opta Comms Export.xlsm")

lastrow = 1
wbSrc.Activate
For Each sh In wbSrc.Sheets

     wbSrc.Sheets(sh).Range("L18:L22").Copy
     wbDest.Sheets("Sheet1").Activate
     lastrow = wbDest.Sheets("Sheet1").Range("A" & .Rows.Count).End(xlUp).Row + 1
     wbDest.Sheets("Sheet1").Range("A" & lastrow).PasteSpecial xlPasteValues

Next


End Sub
Paresh J
  • 2,401
  • 3
  • 24
  • 31
  • I encountered an Invalid or Unqualified Reference error for ".Rows..." so I removed the period before ".Rows.Count". It then provided error '1004' saying the destination workbook could not be found, even though it would open it up. I'm not sure why I get this error when it appears to find the workbook just fine. I then attempted to define the filepath (Dim strFilePath As String strFilePath = "C:\...") but was not successful. – CMC6 Oct 23 '14 at 14:11
  • Sorry, i forgot to correct that. It should be like : wbDest.Sheets("Sheet1").Rows.Count – Paresh J Oct 23 '14 at 14:39
  • Can you clarify where in your original code that should be? – CMC6 Oct 23 '14 at 15:24
  • lastrow = wbDest.Sheets("Sheet1").Range("A" & wbDest.Sheets("Sheet1").Rows.Count).End(xlUp).Row + 1 – Paresh J Oct 23 '14 at 15:25
  • Thanks. I still get the Run-time error '1004': saying that "Master Calibration Data - Num10.xlsm" could not be found, but it seems to locate it so I don't understand the error. – CMC6 Oct 23 '14 at 15:38
  • You can google it, try specifying path with file name. Check this url: http://stackoverflow.com/questions/12951946/excel-vba-open-workbook-perform-actions-save-as-close – Paresh J Oct 23 '14 at 15:47