1

Here is my current macro:

Sub Macro1()

Dim newRow As Integer
Dim rng As Range
Set rng = Selection

newRow = InputBox("Paste Where?")


Workbooks("SSO_TFR_SUMMARY").Worksheets("Sheet1").Cells(newRow, 4).Value = Worksheets("Main Circuit").Cells(rng.Row, 15).Value
Workbooks("SSO_TFR_SUMMARY").Worksheets("Sheet1").Cells(newRow, 5).Value = Worksheets("Main Circuit").Cells(rng.Row, 16).Value
Workbooks("SSO_TFR_SUMMARY").Worksheets("Sheet1").Cells(newRow, 6).Value = Worksheets("Main Circuit").Cells(rng.Row, 6).Value
Workbooks("SSO_TFR_SUMMARY").Worksheets("Sheet1").Cells(newRow, 7).Value = Worksheets("Main Circuit").Cells(rng.Row, 17).Value

End Sub

I am taking data from 4 cells in one workbook and pasting it into a new workbook. Selecting the row to be copied is working fine because there is really no easy way to differentiate what needs to be copied. Currently the macro has me entering the row that the data should be pasted to in the new workbook. how can modify this to search each time i run it and automatically enter the next available spot?

I have searched and found multiple solutions that will search for the next empty cell but I could not get them to work.

Thanks in advance!

Eric Flower
  • 13
  • 2
  • Take a look [here](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) you just need to find the lastrow and add 1. – gtwebb Apr 28 '16 at 21:15

1 Answers1

0

Untested

Sub macro1()

Dim rng As Range
Dim lastRow As Long
Dim book1 As Workbook
Dim book2 As Workbook
Dim sheet1 As Worksheet
Dim sheet2 As Worksheet

Set book1 = Workbooks("SSO_TFR_SUMMARY")
Set book2 = Workbooks("name of book here") 'change name
Set sheet1 = book1.WorkSheets("Sheet1")
Set sheet2 = book2.WorkSheets("Main Circuit")
Set rng = sheet2.Selection

lastRow = sheet1.Cells(sheet1.Rows.count, "D").End(xlUp).Row + 1

With sheet1

    .Cells(lastRow, 4).Value2 = sheet2.Cells(rng.Row, 15).Value2
    .Cells(lastRow, 5).Value2 = sheet2.Cells(rng.Row, 16).Value2
    .Cells(lastRow, 6).Value2 = sheet2.Cells(rng.Row, 6).Value2
    .Cells(lastRow, 7).Value2 = sheet2.Cells(rng.Row, 17).Value2

End With

End Sub
findwindow
  • 3,133
  • 1
  • 13
  • 30
  • i had to remove the sheet2. and leave line 14 as follows: Set rng = Selection Other than that this worked like a charm. Thank you so much! – Eric Flower Apr 29 '16 at 15:36