AlbinoRyno88
07-03-2017, 08:58 AM
Hello all,
This is my first post and I have looked all over the internet to find an answer. I am new to VBA and coding so my terminology is not so great, so please bear with me. What I am trying to do is create a macro that will copy some data from one workbook and create a new workbook and paste that data in the new workbook then save the workbook. I want the macro to copy specific data. Below I have my data pasted so you can see what I am trying to do and I have also attached my spreadsheet if you need to look at that as well.
What I am specifically trying to do is have the macro evaluate a certain date in column A (in this case 6/19) and a specific word in column B(Sales). If it meets both criteria ( 6/19 and Sales) I then need it to copy the entire column and paste into a new worksheet and save it. Right now when I execute the macro it just copies the first line (line 2) and then pastes it correctly.
Sub mysales()
Dim LastRow As Integer, i As Integer, erow As Integer
LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
If Cells(i, 1) = "6/19" And Cells(i, 2) = "Sales" Then
Range(Cells(i, 1), Cells(i, 50)).Select
Selection.Copy
Workbooks.Open Filename:="C:\Users\ryanb\Downloads\Nightbook.xlsm"
Worksheets("Sheet2").Select
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
ActiveSheet.Paste
ActiveWorkbook.Save
Application.CutCopyMode = False
End If
Next i
End Sub
Thank you in advance for you help!
Ryno
This is my first post and I have looked all over the internet to find an answer. I am new to VBA and coding so my terminology is not so great, so please bear with me. What I am trying to do is create a macro that will copy some data from one workbook and create a new workbook and paste that data in the new workbook then save the workbook. I want the macro to copy specific data. Below I have my data pasted so you can see what I am trying to do and I have also attached my spreadsheet if you need to look at that as well.
What I am specifically trying to do is have the macro evaluate a certain date in column A (in this case 6/19) and a specific word in column B(Sales). If it meets both criteria ( 6/19 and Sales) I then need it to copy the entire column and paste into a new worksheet and save it. Right now when I execute the macro it just copies the first line (line 2) and then pastes it correctly.
Sub mysales()
Dim LastRow As Integer, i As Integer, erow As Integer
LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
If Cells(i, 1) = "6/19" And Cells(i, 2) = "Sales" Then
Range(Cells(i, 1), Cells(i, 50)).Select
Selection.Copy
Workbooks.Open Filename:="C:\Users\ryanb\Downloads\Nightbook.xlsm"
Worksheets("Sheet2").Select
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
ActiveSheet.Paste
ActiveWorkbook.Save
Application.CutCopyMode = False
End If
Next i
End Sub
Thank you in advance for you help!
Ryno