CODEWITHSUNDEEP
excelvba
stephen draper
stephen draper

Reputation: 11

trying to copy row from one workbook to another

I am currently trying to make a macro to pull all of the data from a specific row on my sales quote template workbook and then make it paste into the next available row on my quote logger workbook but im really struggling to find the right code to make it work. ive searched around but found nothing concrete that I could use.

what I have so far is below. 

Option Explicit
Sub Test()

Dim sht1 As Worksheet, sht2 As Worksheet
Dim i As Long

Set sht1 = ThisWorkbook.Worksheets("Sheet1")
Set sht2 = "..............RAYOTEC LOGGER.xlsm"

For i = 2 To sht1.Cells(sht1.Rows.Count, "M").End(xlUp).Row
    If sht1.Range("M" & i).Value = "No" Then
        sht1.Range("A" & i).EntireRow.Cut sht2.Range("A" & sht2.Cells(sht2.Rows.Count, "M").End(xlUp).Row + 1)
    End If
Next i

End Sub

Thanks in advance for any help

Upvotes: 0

Views: 53

Answers (2)

John Gomes
John Gomes

Reputation: 1

Also you can use the below for the rest of the code as it just takes the values easily and deletes the row data that you do not need.

Dim sht1 As Worksheet 
Dim sht2 As Worksheet
Dim i, n As Long
Dim iMx, nMx As Long

iMx = sht1.Cells(sht1.Rows.Count, "M").End(xlUp).Row  

For i = 2 To iMx

    nMx = sht2.Cells(sht2.Rows.Count, "A").End(xlUp).Row
    If sht1.Range("M" & i).Value = "No" Then
        sht2.Range("A" & nMx + 1).EntireRow.Value = sht1.Range("A" & i).EntireRow.Value
        sht1.Range("A" & i).EntireRow.Delete Shift:=xlUp
        i = i - 1
    End If

Next i

Upvotes: 0

Imran Malek
Imran Malek

Reputation: 1719

Change the part of your code before FOR loop with this,

Dim sht1 As Worksheet
Dim sht2 As Worksheet
Dim wb As Workbook
Dim i As Long

Set sht1 = ThisWorkbook.Worksheets("Sheet1")
Set wb = Application.Workbooks.Open("..............RAYOTEC LOGGER.xlsm")
Set sht2 = wb.Sheets("Sheet1")

Upvotes: 3

Related Questions