Simple Copy-Paste

[Boris7265]

Hi everybody,

I am having a problem and be honest I am stuck with it , therefore any advise will be very helpful and appreciated .
The problem is that I have very large excel file .There 6 columns that is
Name,JSTExpenses, Amount, Date, Court and Current Balance. The management would like now to separate JSTExpenses that called “<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:City w:st="on"><st1lace w:st="on">Alliance</st1lace></st1:City> “ into separated excel file( they would like it is to be new excel worksheet in the sane excel file) . I can do via vb code that I looked up on the forum ( very thanks full for that)but the problem that with every row applied to Alliance second row comes that issued to “JJL Service” that is also name in JST Expenses column so there are two rows and one row in unique and another is not .”JJL Service” is the same for all JSTExpenses column .The problem I need to separated rows and copy them in new worksheet by “Alliance” that I can do and by “JJL service” that is the same for all expenses .The arrangement is in the first initial excel file like this
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
<st1:City w:st="on"><st1lace w:st="on">Alliance</st1lace></st1:City>
JJL
<st1:City w:st="on"><st1lace w:st="on">Alliance</st1lace></st1:City>
JJL
Meditation
JJL
<o> </o>
Common row in here only Name if the checks <st1lace w:st="on"><st1:City w:st="on">Alliance</st1:City></st1lace> and JJL should be come together.
<o> </o>
Thank you in advance for any advice or input
<o> </o>
The Best Regards,
<o> </o>
BorisGomel

 

[rs2k]

Hi Boris,

Can you post your code and a small sample of your data please?

Cheers
Colin

 

[Boris7265]

Good Morning rs2K,

Here is code that I am working so far, but it is not working .I would like to copy the entire rows from sheet1 with condition “ISU:Issue Summons Fee” that it is in column F and paste them in just new added worksheet. So far I am working on this and no success .When I start to run macro the whole excel file is frozen and I have to close it.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Dim c As Range<o></o>
Dim SrchRng<o></o>
<o> </o>
<o> </o>
Set SrchRng = ActiveSheet.Range("Z1", ActiveSheet.Range("Z65536").End(xlUp))<o></o>
Do<o></o>
Set c = SrchRng.Find("ISU:Issue Summons Fee", LookIn:=xlValues)<o></o>
If Not c Is Nothing Then c.EntireRow.Select<o></o>
Selection.Copy<o></o>
<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1lace w:st="on">Loop</st1lace> While Not c Is Nothing<o></o>
<o></o>
Dim Filename As String<o></o>
Dim NewSheet As Worksheet<o></o>
Filename = InputBox("Enter the file name")<o></o>
Worksheets.Add().Name = Filename<o></o>
Set NewSheet = ActiveSheet<o></o>
<o> </o>
Range("A1").Select<o></o>
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _<o></o>
:=False, Transpose:=False<o></o>

<o> </o>
Thank you in advance for help with the code.
<o> </o>
The Best Regards.
<o> </o>
BorisGomel<o></o>