Loop to copy data into new sheet

[Rado88]

Hi All,

I've a query regarding copying data from Loop into newly added sheet / workbook in excel. For some reasons I can't paste more than one row to new sheet. I modified the code few times but for some reason I can't past all the finding to a new sheet when I don't know it's name

I'm also thinking about adding adding a new workbook option instead of copying to new sheet

What I've:

Sheet A - C with data

Sheet A: Data which will be filtered on base of Sheet C cells value
Sheet C: 3 cells which determine what to copy / delete from sheet A (after user adds values / text in this sheet)

o - will be the number of column where the P value can be found
P - P3 will be updated with text or value by user

What I want to do:
I've a loop which searches for specific data from sheet A and then it should paste it into new sheet, later on it should copy next selection to this new sheet and next... until all rows from sheet meeting the criteria set-up in sheet 3 are met (but only 1 sheet should be added). In short I want to paste all the data meeting requirements from sheet C into new excel sheet in order to help them to get the data they need to work on faster.

The issue:
For now only the first row is copied and pasted into new sheet. I'm not sure how do define the new sheet

Code I've:

Dim o As Long
Dim P As Variant
Dim P2 As Variant
Dim P3 As Variant


o = Worksheets("SheetC").Cells(5, 5).Value


P = Worksheets("SheetC").Cells(5, 9).Value
P2 = Worksheets("SheetC").Cells(8, 9).Value
P3 = Worksheets("SheetC").Cells(11, 9).Value


A = Worksheets("SheetA").Cells(Rows.Count, o).End(xlUp).Row


For i = A To 2 Step -1


If Worksheets("SheetA").Cells(i, o).Value = Z Then


Worksheets("SheetA").Range(Cells(i, 1), Cells(i, 7)).Select


If Worksheets("SheetA").Cells(i, o).Value = P2 And Worksheets("SheetA").Cells(i, o).Value <> "" Then


Worksheets("SheetA").Range(Cells(i, 1), Cells(i, 7)).Select


If Worksheets("SheetA").Cells(i, o).Value = P3 And Worksheets("SheetA").Cells(i, o).Value <> "" Then


Worksheets("SheetA").Range(Cells(i, 1), Cells(i, 7)).Select


End If
End If
End If


Next i

And below is the part which is not working as it should.


Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Paste

End Sub

I've also replaced . Select at the end of each loop with copy but it didn't wok as only one row was pasted. If only I knew how to tell excel to copy to the newly created sheet / workbook

 

[mse330]

The last code I posted is working ok on a sample file I created ... Anyway, since you've figured out the issue & is now doing what you need is what matters :D

Glad to help & thanks for the feedback

 

[Rado88]

Hmm, I've encountered a new issue with this code. I tried to repeat the same operation but instead of copying the data to new sheet I wanted to copy it to new workbook. The issue I encounter is that when it successfully copies first row and it should return to Workbook Test to continue the loop...... the error 1004 appears

I tried to add the below part to my code or even provide full Workbook path, but for some reason the macro can't seem to return to first excel file after pasting the row

Dim WSR as Workbook
Dim WSO as Workbook
Dim WSR1 as Worksheet
Dim WSO1 as Worskheet

Set WSR = Worksheets("Test") - here is the loop and data now
Set WSR1 = WSR.Sheets("Sheet1")
Set WSO = Add.Workbook
Set WSO1 = WSO.Sheets("Sheet1")

Any one know how this can be done with or without Boolean?

 

[Rado88]

Hmm, I've encountered a new issue with this code. I tried to repeat the same operation but instead of copying the data to new sheet I wanted to copy it to new workbook. The issue I encounter is that when it successfully copies first row and it should return to Workbook Test to continue the loop...... the error 1004 appears

I tried to add the below part to my code or even provide full Workbook path, but for some reason the macro can't seem to return to first excel file after pasting the row

Dim WSR as Workbook
Dim WSO as Workbook
Dim WSR1 as Worksheet
Dim WSO1 as Worskheet

Set WSR = Worksheets("Test") - here is the loop and data now
Set WSR1 = WSR.Sheets("Sheet1")
Set WSO = Add.Workbook
Set WSO1 = WSO.Sheets("Sheet1")

Any one know how this can be done with or without Boolean?

Just solved the issue after changing .Select in last part of the macro to .activate