CODEWITHSUNDEEP
excelvba
Debbie
Debbie

Reputation: 1

Copy data from one sheet to the next available row in another sheet

I have never used macros before, but can anyone tell what am I missing in the following macro, so the data from sheet 1 would be copied to the next available line in sheet 2? I have tried "LastRow","NextRow"commands, but I can't get it right. Any help would be great.

Sheets("Sheet1").Range("B2").Select
Selection.Copy
iRow = Worksheets("Sheet2").Cells(Rows.Count, 1).End(x1up).Row + 1
Sheets("Sheet2").Range ("B" & iRow)
ActiveSheet.Paste

Upvotes: 0

Views: 2628

Answers (2)

Caleb Hoch
Caleb Hoch

Reputation: 1

IN regards to this, I have the below code. However, my issue is that I have multiple versions of this macro running and pulling information to the same sheet. I need to be able to have the macro find the next row and not overwrite what is already on the page.

Sub Product_Value_In_Specific_Row() Dim c As Range Dim J As Integer Dim Source As Worksheet Dim Target As Worksheet

' Change worksheet designations as needed
Set Source = ActiveWorkbook.Worksheets("mongodb")
Set Target = ActiveWorkbook.Worksheets("Inputs")

J = 2     ' Start copying to row 1 in target sheet
For Each c In Source.Range("A2:A100")   ' Do 1000 rows
    If c = "Product" then
       Source.Rows(c.Row).Copy Target.Rows(J)
       J = J + 1
    End If
Next c

End Sub

I have been getting around this by changing the "J = 2" line to few rows down, but my other sheets have varying amounts of rows and would like for them to just copy and paste into the inputs sheet and not overwrite each other.

Upvotes: 0

SJR
SJR

Reputation: 23081

Try either of these. It's xlup not x1up, and you weren't pasting to your destination cell (and Selects are unnecessary).

iRow = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlup).Row + 1
Sheets("Sheet1").Range("B2").Copy Sheets("Sheet2").Range ("B" & iRow)

or

iRow = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlup).Row + 1
Sheets("Sheet2").Range ("B" & iRow).value=Sheets("Sheet1").Range("B2").value

Upvotes: 1

Related Questions