Copy and Paste Data From one Workbook to Another Based on Criteria

[nryan]

Hello,

I have two workbooks: 1) called "Source" and 2) "Trend". I want to put a command button in Trend that copies data from Source and pastes it into Trend.

The workbook Source is always open on a computer downstairs. Users record data on it throughout the day.

The workbook Trend is on my computer and below is the code I have written so far. Basically there is a column in the Source workbook where the user must select from 3 options "Vertical", "Angle", or "n/a". I try to use those 3 selections to group the data into the Trend workbook (using the If-Then-ElseIf statement).

Private Sub CommandButton1_Click()

Dim LastRow As Integer, i As Integer, eRow As Integer


Workbooks.Open Filename:="File Path\Source.xlsm"


LastRow = Worksheets("ACP").Range("A" & Rows.Count).End(xlUp).Row


For i = 2 To LastRow


    If Cells(i, 5) = "Vertical" Then
        Range(Cells(i, 1)).Select
        Range(Cells(1, 6)).Select
        Range(Cells(i, 11), Cells(i, 12)).Select
        Selection.Copy
        
        eRow = ThisWorkbook.ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        ActiveSheet.Cells(eRow, 2).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
    
    ElseIf Cells(i, 5) = "Angle" Then
        Range(Cells(i, 1)).Select
        Range(Cells(1, 6)).Select
        Range(Cells(i, 11), Cells(i, 12)).Select
        Selection.Copy
        
        eRow = ThisWorkbook.ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        ActiveSheet.Cells(eRow, 2).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
    
    ElseIf Cells(i, 5) = "n/a" Then
        Range(Cells(i, 1)).Select
        Range(Cells(1, 6)).Select
        Range(Cells(i, 11), Cells(i, 12)).Select
        Selection.Copy
    
        eRow = ThisWorkbook.ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        ActiveSheet.Cells(eRow, 2).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        
    End If
    
Next i


Workbooks("Source.xlsm").Close


End Sub

There are no errors. The Source workbook opens and asks me if I want to save. I assume that means the code looped all the way through the For-Next loop and got to the end where the last line of code before End Sub is Workbooks("Source.xlsm").Close. However I don't see the data in the Trend workbook. I think the problem might be that I'm not Activating the Trend workbook and "Sheet1" in the trend workbook correctly with the line of code I use:

eRow = ThisWorkbook.ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

I assumed that "ThisWorkbook.ActiveSheet" would be the workbook and worksheet which the command button is on (aka "Sheet1" in the Trend workbook) but maybe I'm wrong?

I'm not very experienced with VBA and the code I have above was mostly taken from examples out there on the web. Now I need some help with the details I think.

Thank you. I look forward to hearing your responses.
-Nick

 

[davesexcel]

You should dim and set your active workbook , before you open the other workbook. when you open the other workbook, it becomes the active workbook. even though the code is from the original workbook.
so:

dim wb as workbook,ws as worksheet
set wb=thisworkbook
set ws=wb.sheets("MySheet")
'then open the other workbook

ws will then refer to your original workbook and worksheet.

 

[nryan]

Hi davesexcel,

I tried your suggestion but the result is the same. Here is the revised code with your suggestions.

Private Sub CommandButton1_Click()

Dim wb As Workbook, ws As Worksheet

Set wb = ThisWorkbook
Set ws = wb.Worksheets("ACP")

Dim LastRow As Integer, i As Integer, eRow As Integer

Workbooks.Open Filename:="\\cmicro.com\Shares\Amb\Amb-Probes\DataLogs\CQS-03-033-2012 Coax Shelf Cut Log R2.6.xlsm"

LastRow = Worksheets("ACP").Range("A" & Rows.Count).End(xlUp).Row


For i = 2 To LastRow

    If Cells(i, 5) = "Vertical" Then
        Range(Cells(i, 1)).Select
        Range(Cells(1, 6)).Select
        Range(Cells(i, 11), Cells(i, 12)).Select
        Selection.Copy
        
        eRow = wb.ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        ActiveSheet.Cells(eRow, 2).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
    
    ElseIf Cells(i, 5) = "Angle" Then
        Range(Cells(i, 1)).Select
        Range(Cells(1, 6)).Select
        Range(Cells(i, 11), Cells(i, 12)).Select
        Selection.Copy
        
        eRow = wb.ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        ActiveSheet.Cells(eRow, 7).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
    
    ElseIf Cells(i, 5) = "n/a" Then
        Range(Cells(i, 1)).Select
        Range(Cells(1, 6)).Select
        Range(Cells(i, 11), Cells(i, 12)).Select
        Selection.Copy
    
        eRow = wb.ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        ActiveSheet.Cells(eRow, 12).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        
    End If
    
Next i

Workbooks("CQS-03-033-2012 Coax Shelf Cut Log R2.6.xlsm").Close

End Sub

It's possible that my Range(Cells(i, 1)).Select, Range(Cells(i, 6)).Select, etc. lines of code may be wrong. The data I need is scattered in columns 1, 6, 11, and 12 on the Source workbook and I'm trying to paste them into the Trend workbook in columns 2-5 for "Vertical", 7-10 for "Angle", and 12-15 for "n/a". I thought these lines of code would take care of that:

For "Vertical"

eRow = wb.ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Cells(eRow, 2).Select

For "Angle"

eRow = wb.ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Cells(eRow, 7).Select

For "n/a"

eRow = wb.ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Cells(eRow, 12).Select

 

[davesexcel]

You don't require wb.ws , just ws.

You shouldn't have to select anything. Will look in later, if somebody hasn't help you with this.

 

[nryan]

I've tried to narrow down the problem and I found that I cant even copy the data with the code I have. I tried to use the code below to simply copy two columns in the Source workbook so I could manually paste them into the Trend workbook, but when I go to paste I get nothing.

Private Sub CommandButton1_Click()

Dim wb As Workbook, ws As Worksheet


Set wb = ThisWorkbook
Set ws = wb.Worksheets("ACP")


Dim LastRow As Integer, i As Integer, eRow As Integer


Workbooks.Open Filename:="File Path\Source.xlsm"


LastRow = Worksheets("ACP").Range("A" & Rows.Count).End(xlUp).Row


For i = 2 To LastRow


    If Cells(i, 5) = "Vertical" Then
        Range(Cells(i, 11), Cells(i, 12)).Select
        Selection.Copy
        
    End If
    
Next i


End Sub

Can anyone see why this might be?