dj44
05-20-2017, 10:03 AM
folks,
Good weekend,
:)
I am trying to copy and paste some data from different worksheets into different worksheets.
I made the setup and something worked but at a basic level and then I added some more variables and made it into a multidimensional array I think.
So now it's not working.
Sub Copy_Data()
Dim i As Long
Dim ws As Worksheet
Dim oOriginalSearchWS As Worksheet
Dim oDestinationSheet As Worksheet
Set ws = Worksheets("Table")
' Search this worksheet
For i = 2 To ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
Set oOriginalSearchWS = Worksheets(ws.Cells(i, "B").Value) ' Search this worksheet
' Send to these worksheet
Set oDestinationSheet = Worksheets(ws.Cells(i, "E").Value)
' if column d values are found in column c ranges
If InStr(1, oOriginalSearchWS.Range(ws.Cells(i, "C")).Value, ws.Cells(i, "D").Value) > 0 Then
'Copy paste the data
oOriginalSearchWS.Range(ws.Cells(i, "C")).Copy _
Destination:=oDestinationSheet.Range(ws.Cells(i, "F").Value) ' Paste Location
End If
Next i
End Sub
I tried a lot fo autofiltering this week but i had to do it one by 1 so now i have made a table to find these strings and copy and paste them.
I also didnt know how to increment the row loop counter ?
If somebody could take a look at my workbook to see where the set-up has gone i'll be really grateful.
thanking you very much for your time
Good weekend,
:)
I am trying to copy and paste some data from different worksheets into different worksheets.
I made the setup and something worked but at a basic level and then I added some more variables and made it into a multidimensional array I think.
So now it's not working.
Sub Copy_Data()
Dim i As Long
Dim ws As Worksheet
Dim oOriginalSearchWS As Worksheet
Dim oDestinationSheet As Worksheet
Set ws = Worksheets("Table")
' Search this worksheet
For i = 2 To ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
Set oOriginalSearchWS = Worksheets(ws.Cells(i, "B").Value) ' Search this worksheet
' Send to these worksheet
Set oDestinationSheet = Worksheets(ws.Cells(i, "E").Value)
' if column d values are found in column c ranges
If InStr(1, oOriginalSearchWS.Range(ws.Cells(i, "C")).Value, ws.Cells(i, "D").Value) > 0 Then
'Copy paste the data
oOriginalSearchWS.Range(ws.Cells(i, "C")).Copy _
Destination:=oDestinationSheet.Range(ws.Cells(i, "F").Value) ' Paste Location
End If
Next i
End Sub
I tried a lot fo autofiltering this week but i had to do it one by 1 so now i have made a table to find these strings and copy and paste them.
I also didnt know how to increment the row loop counter ?
If somebody could take a look at my workbook to see where the set-up has gone i'll be really grateful.
thanking you very much for your time