Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #109609 > unrolled thread

Compare 2 Lists and Take an Action Q

Back to article view | Back to microsoft.public.excel.programming

Contents

  Compare 2 Lists and Take an Action Q seanryanie@yahoo.co.uk - 2016-11-18 11:17 -0800
    Re: Compare 2 Lists and Take an Action Q Claus Busch <claus_busch@t-online.de> - 2016-11-18 20:38 +0100
      Re: Compare 2 Lists and Take an Action Q GS <gs@v.invalid> - 2016-11-18 14:46 -0500
        Re: Compare 2 Lists and Take an Action Q Claus Busch <claus_busch@t-online.de> - 2016-11-18 21:07 +0100
          Re: Compare 2 Lists and Take an Action Q seanryanie@yahoo.co.uk - 2016-11-18 12:42 -0800
            Re: Compare 2 Lists and Take an Action Q Claus Busch <claus_busch@t-online.de> - 2016-11-18 21:51 +0100
              Re: Compare 2 Lists and Take an Action Q GS <gs@v.invalid> - 2016-11-18 16:04 -0500
            Re: Compare 2 Lists and Take an Action Q GS <gs@v.invalid> - 2016-11-18 16:05 -0500
              Re: Compare 2 Lists and Take an Action Q seanryanie@yahoo.co.uk - 2016-11-18 13:26 -0800
                Re: Compare 2 Lists and Take an Action Q seanryanie@yahoo.co.uk - 2016-11-21 08:09 -0800
                  Re: Compare 2 Lists and Take an Action Q Claus Busch <claus_busch@t-online.de> - 2016-11-21 17:22 +0100
                    Re: Compare 2 Lists and Take an Action Q seanryanie@yahoo.co.uk - 2016-11-21 08:33 -0800
                      Re: Compare 2 Lists and Take an Action Q seanryanie@yahoo.co.uk - 2016-11-21 09:19 -0800
                        Re: Compare 2 Lists and Take an Action Q Claus Busch <claus_busch@t-online.de> - 2016-11-21 19:05 +0100
                          Re: Compare 2 Lists and Take an Action Q seanryanie@yahoo.co.uk - 2016-11-21 11:39 -0800
                            Re: Compare 2 Lists and Take an Action Q Claus Busch <claus_busch@t-online.de> - 2016-11-22 07:47 +0100
                              Re: Compare 2 Lists and Take an Action Q seanryanie@yahoo.co.uk - 2016-11-21 22:58 -0800
                                Re: Compare 2 Lists and Take an Action Q Claus Busch <claus_busch@t-online.de> - 2016-11-22 08:04 +0100
                                  Re: Compare 2 Lists and Take an Action Q seanryanie@yahoo.co.uk - 2016-11-22 00:40 -0800
                                    Re: Compare 2 Lists and Take an Action Q Claus Busch <claus_busch@t-online.de> - 2016-11-22 10:14 +0100
                                      Re: Compare 2 Lists and Take an Action Q seanryanie@yahoo.co.uk - 2016-11-22 01:35 -0800
                                        Re: Compare 2 Lists and Take an Action Q Claus Busch <claus_busch@t-online.de> - 2016-11-22 10:56 +0100
                                          Re: Compare 2 Lists and Take an Action Q seanryanie@yahoo.co.uk - 2016-11-22 03:28 -0800
                                            Re: Compare 2 Lists and Take an Action Q Claus Busch <claus_busch@t-online.de> - 2016-11-22 13:26 +0100
                                            Re: Compare 2 Lists and Take an Action Q Claus Busch <claus_busch@t-online.de> - 2016-11-22 13:39 +0100
                                              Re: Compare 2 Lists and Take an Action Q seanryanie@yahoo.co.uk - 2016-11-22 05:14 -0800
                                                Re: Compare 2 Lists and Take an Action Q Claus Busch <claus_busch@t-online.de> - 2016-11-22 14:29 +0100
                                                Re: Compare 2 Lists and Take an Action Q Claus Busch <claus_busch@t-online.de> - 2016-11-22 14:52 +0100
                                                  Re: Compare 2 Lists and Take an Action Q seanryanie@yahoo.co.uk - 2016-11-22 11:20 -0800
                                                    Re: Compare 2 Lists and Take an Action Q seanryanie@yahoo.co.uk - 2016-11-22 12:19 -0800
                                                      Re: Compare 2 Lists and Take an Action Q Claus Busch <claus_busch@t-online.de> - 2016-11-22 22:36 +0100
    Re: Compare 2 Lists and Take an Action Q bulong <bulong@gmail.com> - 2016-11-19 09:29 +0000

Page 2 of 2 — ← Prev page 1 [2]

#109636

Claus, I need to copy

B; D; G, these should be copied to Columns
A; B; H

The rows for this 2nd filter should start at Row278, but as explained, this is dynamic, it depends on the length of Filter # 1

Thanks for your help, much appreciated

[toc] | [prev] | [next] | [standalone]

#109637

Hi Sean,

Am Tue, 22 Nov 2016 01:35:02 -0800 (PST) schrieb seanryanie@yahoo.co.uk:

> B; D; G, these should be copied to Columns
> A; B; H
> 
> The rows for this 2nd filter should start at Row278, but as explained, this is dynamic, it depends on the length of Filter # 1

try:

Sub ExtractUnmatched()
Dim myCnt As Long, LRow As Long
Dim c As Range

With Sheets("Reconciliation")
    LRow = .Cells(.Rows.Count, "M").End(xlUp).Row
    .Range("M1:Q" & LRow).AutoFilter Field:=5, Criteria1:="="
    myCnt = Application.Subtotal(3, .Range("M:M")) - 1
    If myCnt > 14 Then Sheets("Report").Rows(28).Resize(myCnt - 15).Insert
    .Range("N2:N" & LRow).Copy
    Sheets("Report").Range("B14").PasteSpecial xlPasteValues
    .Range("O2:O" & LRow).Copy
    Sheets("Report").Range("A14").PasteSpecial xlPasteValues
    .Range("P2:P" & LRow).Copy
    Sheets("Report").Range("H14").PasteSpecial xlPasteValues
    .AutoFilterMode = False
    
    LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    Set c = Sheets("Report").Range("A:A").Find("Payments",
LookIn:=xlValues, lookat:=xlPart)
    LRow2 = c.Row + 2
    .Range("A1:K" & LRow).AutoFilter Field:=11, Criteria1:="="
    myCnt = Application.Subtotal(3, .Range("A:A")) - 1
    If myCnt > 7 Then Sheets("Report").Rows(LRow2).Resize(myCnt - 7).Insert
    .Range("B2:B" & LRow).Copy
    Sheets("Report").Range("A" & LRow2).PasteSpecial xlPasteValues
    .Range("D2:D" & LRow).Copy
    Sheets("Report").Range("B" & LRow2).PasteSpecial xlPasteValues
    .Range("G2:G" & LRow).Copy
    Sheets("Report").Range("H" & LRow2).PasteSpecial xlPasteValues
    .AutoFilterMode = False
End With

End Sub

I sent you back your workbook.


Regards
Claus B.
-- 
Windows10
Office 2016

[toc] | [prev] | [next] | [standalone]

#109638

Perfect Claus, many thanks for your assistance

Just a few things to tidy up now. When I'm setting up a new Reconciliation, how do I delete the rows on the 'Report' sheet that the Filters are copied to? 

I want to do this just to have a neat layout at start point, as the Filter Code will insert if not enough Rows anyway

[toc] | [prev] | [next] | [standalone]

#109639

Hi Sean,

Am Tue, 22 Nov 2016 03:28:25 -0800 (PST) schrieb seanryanie@yahoo.co.uk:

> Just a few things to tidy up now. When I'm setting up a new Reconciliation, how do I delete the rows on the 'Report' sheet that the Filters are copied to? 
> 
> I want to do this just to have a neat layout at start point, as the Filter Code will insert if not enough Rows anyway

to delete the data and reset number of rows back in first sector to 14
and in second sector to 7 try:

Sub DeleteRows()
Dim First As Range, Last As Range, Start As Range
Dim Diff1 As Long, Diff2 As Long

With Sheets("Report")
    Set Start = .Range("A14")
    Set First = .Range("A:A").Find("Payments /", lookat:=xlPart)
    Set Last = .Range("A:A").Find("Adjusted Variance", lookat:=xlWhole)
    Diff1 = First.Row - 14 - 5
    Diff2 = Last.Row - First.Row - 4
    Start.Resize(Diff1, 8).ClearContents
    First.Offset(2, 0).Resize(Diff2, 8).ClearContents
    If Diff1 > 14 Then .Rows(Start.Row).Resize(Diff1 - 12).Delete
    If Diff2 > 7 Then .Rows(First.Row + 2).Resize(Diff2 - 6).Delete
End With
End Sub


Regards
Claus B.
-- 
Windows10
Office 2016

[toc] | [prev] | [next] | [standalone]

#109640

Hi Sean,

Am Tue, 22 Nov 2016 03:28:25 -0800 (PST) schrieb seanryanie@yahoo.co.uk:

> Just a few things to tidy up now. When I'm setting up a new Reconciliation, how do I delete the rows on the 'Report' sheet that the Filters are copied to? 
> 
> I want to do this just to have a neat layout at start point, as the Filter Code will insert if not enough Rows anyway

another suggestion:
Insert a new worksheet in your workbook with the original layout of
sheet "Report" without the data. If you want to do a new reconciliation
delete all contents in "Report" and copy the data from the template to
"Report".
Or a little easier delete sheet "Report", copy the template and name it
"Report"


Regards
Claus B.
-- 
Windows10
Office 2016

[toc] | [prev] | [next] | [standalone]

#109641

I like your idea of code to delete, but I couldn't get it quite to work, some of the template formulas were not in the correct place. Ended up with a 'Template' sheet and copying that in

Sub TidyupRows()
Sheets("Template").Visible = True
    Sheets("Template").Select
    Cells.Select
    Selection.Copy
Sheets("Template").Visible = False
    Sheets("Report").Select
    ActiveSheet.Paste
    Range("A1").Select
End Sub

[toc] | [prev] | [next] | [standalone]

#109642

Hi Sean,

Am Tue, 22 Nov 2016 05:14:09 -0800 (PST) schrieb seanryanie@yahoo.co.uk:

> I like your idea of code to delete, but I couldn't get it quite to work, some of the template formulas were not in the correct place. Ended up with a 'Template' sheet and copying that in

to delete all contents and insert the contents of "Template" try:
Sub RecNew()
Sheets("Report").UsedRange.ClearContents
Sheets("Template").UsedRange.Copy
Sheets("Report").Range("A1").PasteSpecial xlPasteAll
End Sub

Another way is to delete the sheet "Report" and create it new from
"Template":
Sub RecNew2()
Application.DisplayAlerts = False
Sheets("Report").Delete
Sheets("Template").Copy Before:=Sheets(1)
ActiveSheet.Name = "Report"
Application.DisplayAlerts = True
End Sub


Regards
Claus B.
-- 
Windows10
Office 2016

[toc] | [prev] | [next] | [standalone]

#109643

Hi Sean,

Am Tue, 22 Nov 2016 05:14:09 -0800 (PST) schrieb seanryanie@yahoo.co.uk:

> I like your idea of code to delete, but I couldn't get it quite to work, some of the template formulas were not in the correct place. Ended up with a 'Template' sheet and copying that in

for the copy method of contents it is not necessary to unhide the sheet.
If you copy the whole sheet you have to unhide "Template":

Sub RecNew2()
Application.DisplayAlerts = False
Sheets("Template").Visible = True
Sheets("Report").Delete
Sheets("Template").Copy Before:=Sheets(1)
ActiveSheet.Name = "Report"
Sheets("Template").Visible = xlVeryHidden
Application.DisplayAlerts = True
End Sub



Regards
Claus B.
-- 
Windows10
Office 2016

[toc] | [prev] | [next] | [standalone]

#109644

Thanks Claus, its working pretty well. I'm sure I'll think of tweaks to enhance it

[toc] | [prev] | [next] | [standalone]

#109645

Claus, I have 2 Macro Buttons on the Report sheet, I will replicate these on the Template sheet, but how does your code below handle these, does it delete them from Report first? I've had other worksheets when the size explodes when you copy sheets in multiple times with Buttons 

Sub RecNew() 
Sheets("Report").UsedRange.ClearContents 
Sheets("Template").UsedRange.Copy 
Sheets("Report").Range("A1").PasteSpecial xlPasteAll 
End Sub 

[toc] | [prev] | [next] | [standalone]

#109646

Hi Sean,

Am Tue, 22 Nov 2016 12:19:37 -0800 (PST) schrieb seanryanie@yahoo.co.uk:

> Claus, I have 2 Macro Buttons on the Report sheet, I will replicate these on the Template sheet, but how does your code below handle these, does it delete them from Report first? I've had other worksheets when the size explodes when you copy sheets in multiple times with Buttons 

ClearContents only deletes cell values. Other things like buttons will
remain.


Regards
Claus B.
-- 
Windows10
Office 2016

[toc] | [prev] | [next] | [standalone]

#109621

Cho mình một vé
_________________
'*kinky videos*' (https://t.co/aVqVb8LMct)

https://t.co/2K1zjCFslH




-- 
bulong

[toc] | [prev] | [standalone]

Page 2 of 2 — ← Prev page 1 [2]

Back to top | Article view | microsoft.public.excel.programming

csiph-web