Excel, VBA: How can I copy paste data to new workbook when 1 conditional applying to multiple ranges?

Question

I am a total n00b when it comes to excel and vba. Any help would be much appreciated.

There is data from a to k in excel. I am trying to: Check whether E>2, to export G(x), E(x), and J(x) for all lines (columns) where this is the case.

I can't manage to select properly, and joins this with conditional successfully. In addition, my pasting is super random. I am trying to export it to a given filename @ place, but haven't really gotten that far because cannot event export properly to different sheet in same workbook.

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False

Dim Value As Range
Dim Copyarea1 As Range
Dim Copyarea2 As Range
Dim Copyarea3 As Range
Dim Copymaster As Range
Dim Pastesheet As Range

Sheet4.Activate
sheet1.Activate

Set Copyarea1 = sheet1.Range("F2")
Set Copyarea2 = sheet1.Range("H2")
Set Copyarea3 = sheet1.Range("I2")
Set Copymaster = Union(Copyarea1, Copyarea2, Copyarea3)


sheet1.Select
For Each Value In Range(["H2:H2539"])
If Value > 2 Then
Value.Select
Selection.Copy

Else: ActiveCell.Offset(1, 0).Activate
End If
If Value = "" Then Exit Sub

Sheet4.Select
  Selection.PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
ActiveCell.Offset(1, 0).Activate
sheet1.Activate

Next
Application.ScreenUpdating = True

End Sub

When I replace Value with copy master I get correct initial selection but fail at offsetting. and the export part is no good. Only values to be copied, cells have formulas.

Good place to start; [avoid using Select](http://stackoverflow.com/q/10714251/445425) — chris neilsen, Aug 13 '14 at 06:37
I would also stop using object properties as variables (ie Value) for one. That invites nothing but grief. And as Chris sort of pointed out. In code you do NOT need to select a range or cell to act upon it. Ie Value.Select:Selection.Copy. YOu can simply write Value.Copy to achieve a more efficient equivalent result. — David, Aug 13 '14 at 12:36
Thanks for input. This makes my code smoother. I am still struggeling though to set up the correct conditional. Where I want it check each cell i H2:H2539, and for each instance where H>2 to Copy H, F and I on that row; and where H=<2, to skip to the next row without copying. — Ole Idland, Aug 14 '14 at 08:32
Could explain what is the purpose of `Copymaster`? And next question: when you copy F, H and I where do you paste those values? Do you want to paste them concatenated to one cell or each value should be pasted in cells next to each other? (like A2, B2, C2?) — lowak, Aug 14 '14 at 09:43
Sure. The purpose of "copymaster" was to attempt to link the conditional with the desired selection. So that if H2>2 then copy H2,F2,I2. If H2 not > 2, move to H3. I want to paste them in cells next to each other, in different workbook, although have only tired for different sheet so far. — Ole Idland, Aug 14 '14 at 11:25

score 0 · Accepted Answer · answered Aug 14 '14 at 12:57

This code at first counts rows in workbook Book2.xlsm sheet1 and then go through all cells in original workbook range H2:H2539. If value is more then 2 then values from this row in columns F, H and I are pasted in A, B, C row in workbook Book2.xlsm sheet1 in first empty row.

Private Sub CommandButton1_Click()

Workbooks.Open Filename:="C:\Users\User\Desktop\Book2.xlsm" 'change path to your workbook

Dim sh1 As Worksheet, sh2 As Worksheet

Set sh1 = ThisWorkbook.Sheets("sheet1")
Set sh2 = Workbooks("Book2.xlsm").Sheets("sheet1")

Application.ScreenUpdating = False

'counts rows in sheet2 column A (this is where values are going to be copied)
If IsEmpty(sh2.Range("A1").End(xlDown)) = True Then
    y = 1

Else
    y = sh2.Range("A1", sh2.Range("A1").End(xlDown)).Rows.Count + 1

End If


For i = 2 To 2539 'number of rows in your range (sheet1)

    If sh1.Cells(i, 8) > 2 Then

        sh2.Cells(y, 1) = sh1.Cells(i, 8).Offset(0, -2)
        sh2.Cells(y, 1).Offset(0, 1) = sh1.Cells(i, 8)
        sh2.Cells(y, 1).Offset(0, 2) = sh1.Cells(i, 8).Offset(0, 1)

        y = y + 1

    ElseIf sh1.Cells(i, 8) = "" Then: Exit Sub

    End If

Next i

Application.ScreenUpdating = True

Workbooks("2.xlsm").Close savechanges:=True 'closes your second workbook and save changes

End Sub

Really amazing. Thank you so much, only thing it does not close and save as expected. When I try to repeat the command it just wants to reopen workbook and repeat the process.. Any advice would be appreciated. — Ole Idland, Aug 18 '14 at 12:49
I amended code so that after _ElseIf sh1.Cells(i, 8) = "" Then:_ Workbooks("2.xlsm").Close savechanges:=True Exit Sub Then Workbook2 was saved and closed as expected. Thanks again. — Ole Idland, Aug 18 '14 at 12:58
2 further complicate matters, Any ideas on how to save the sh2 as .csv, in addition to xlsx.? So that it opens the xlsx file (or even this could be altered to CSV, thoug i suspect this will further complicate the formula? — Ole Idland, Aug 20 '14 at 13:45
You should ask another question - this exceeds this question range. — lowak, Aug 20 '14 at 13:58

Excel, VBA: How can I copy paste data to new workbook when 1 conditional applying to multiple ranges?

1 Answers1