VBA Copy Data to Another Workbook

[Redwolfx]

Good Afternoon,
So far I have the following Code

Sub Export()
Dim myCell8 As Range
Dim LastRow As Long
Dim nextRow As Long
Sheets("Original Refund").Select
LastRow = Range("A2000").End(xlUp).Row
For Each myCell8 In Worksheets("Original Refund").Range(Cells(9, 1), Cells(LastRow, 1))
With Worksheets("Data")
 nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
.Cells(nextRow, 1).Value = ActiveWorkbook.Name
.Cells(nextRow, 2).Value = Range("F6").Value
.Cells(nextRow, 3).Value = Range("D4").Value
.Cells(nextRow, 4).Value = Application.UserName
.Cells(nextRow, 5).Value = Range("B5").Value
.Cells(nextRow, 6).Value = myCell8.Value
.Cells(nextRow, 7).Value = Range("D5").Value
.Cells(nextRow, 8).Value = myCell8(1, 2).Value
.Cells(nextRow, 9).Value = Range("D6").Value
.Cells(nextRow, 10).Value = Range("B4").Value
End With
Next myCell8
End Sub

This works great for moving data from one sheet to another within the same workbook. What I'm trying to do is have the data moved to another workbook. I know that I can take this, then add code to copy and paste the new sheet to another workbook, but I'm trying to skip having to have a sheet available just to house data to move to another sheet.

What needs to be Added/Changed to acomplish this?

Thanks.

-Red

 

[James Snyder]

What you need to do is to directly address the other workbook once opened (or use ADO if closed). So if you have the following defines, it would look something like this:

Dim wkBook1 As Workbook
Dim wkBook2 As Workbook
Dim fromRange As Range
Dim toRange As Range

wkBook1.fromRange().copy destination:=wkBook2.Range()

Obviously, there would be a sheet in there, etc...

 

[Redwolfx]

The problem is that without moving it to another page, it's not a perfect Copy, As you can see from my code, information is puled from various cells and organized through the VBA. I'm tring to accomplish the same thing, but across workbooks.

 

[James Snyder]

Using your code and just addressing the workbook, it would look something like this (untested):

Sub Export()
    Dim wkBook1 As Workbook
    Dim wkBook2 As Workbook
    Dim myCell8 As Range
    Dim LastRow As Long
    Dim nextRow As Long
    
    LastRow = wkBook1.Sheets("Original Refund").Range("A2000").End(xlUp).Row
    With wkBook1.Sheets("Original Refund").Range("A1:A" & LastRow)
        For Each myCell8 In .Range(Cells(9, 1), Cells(LastRow, 1))
            With wkBook2.Worksheets("Data")
                 nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
                .Cells(nextRow, 1).Value = ActiveWorkbook.Name
                .Cells(nextRow, 2).Value = wkBook1.Sheets("Original Refund").Range("F6").Value
                .Cells(nextRow, 3).Value = wkBook1.Sheets("Original Refund").Range("D4").Value
                .Cells(nextRow, 4).Value = Application.UserName
                .Cells(nextRow, 5).Value = wkBook1.Sheets("Original Refund").Range("B5").Value
                .Cells(nextRow, 6).Value = myCell8.Value
                .Cells(nextRow, 7).Value = wkBook1.Sheets("Original Refund").Range("D5").Value
                .Cells(nextRow, 8).Value = myCell8(1, 2).Value
                .Cells(nextRow, 9).Value = wkBook1.Sheets("Original Refund").Range("D6").Value
                .Cells(nextRow, 10).Value = wkBook1.Sheets("Original Refund").Range("B4").Value
            End With
        Next myCell8
    End WIth
End Sub