Any thoughts on what I am doing wrong? Any idea on how I can get this working?
Thanks,
Ryan---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.
"ryguy7272" wrote:
> Thanks for the info JLGWhiz. I think it's going to be something like this:
> Sub CompareSheets1()
>
> 'Delete the sheet "Analysis" if it exist
> Application.DisplayAlerts = False
> On Error Resume Next
> ActiveWorkbook.Worksheets("Analysis-Sheet").Delete
> On Error GoTo 0
> Application.DisplayAlerts = True
>
>
> Dim ws1 As Worksheet, ws2 As Worksheet
> Dim lngRow As Long, lngFRow As Long
>
> Set ws1 = Worksheets("Analysis of Interest Prior")
> Set ws2 = Worksheets("Analysis of Interest Current")
>
> ws1.Copy After:=ws2
> Set ws1 = ActiveSheet
> ActiveSheet.Name = "Analysis-Sheet"
>
> ws1.Columns("F:J").Clear
> ws2.Range("E1:E9").Copy ws1.Range("F1:F9")
>
> Dim wks1 As Worksheet, wks2 As Worksheet
> Set wks1 = Sheets("Analysis of Interest Current")
> Set wks2 = Sheets("Analysis-Sheet")
>
> For i = iLastRow To 2 Step -1
> For Each j In wks1.Range("A2:A170")
> If IsError(Application.Match(Cells(i, "A").Value,
> wks2.Range("A2:A170"), 0)) Then
> wks1.Cells(i, "A").EntireRow.Insert
> wks2.Range.EntireRow.Copy
> ActiveCell.Value = wks2.Range("A2:A170").Value
> End If
> Next j
> Next i
> End Sub
>
> However, I don't know how to tell Excel to copy from the appropriate row in
> wks2.
> I know this is not right:
> wks2.Range.EntireRow.Copy
>
> There's no reference for the row! How do I tell Excel which row to copy?
> Also, the loop is not working right. I think I'm close to a solution. What
> do I need to do to make this work?
>
> Thanks,
> Ryan---
> --
> Ryan---
> If this information was helpful, please indicate this by clicking ''Yes''.
>
>
> "JLGWhiz" wrote:
>
> > If column A is formatted as text, you could use the InStr function to test
> > the other sheet for a match and if no match then copy the data over.
> >
> > for expl
> > Set wks1 = Sheets("Analysis of Interest Current")
> > Set wks2 = Sheets("Analysis-Sheet")
> > For Each c In wks1.Range("A2:A100")
> > If InStr(wks2.Range("A2:A500"), "7252-0000") = 0 Then
> > 'copy/paste the data
> > End If
> > Next
> >
> > The ranges are arbitrary but should give you an idea of the method.
> >
> > "ryguy7272" <(E-Mail Removed)> wrote in message
> > news:27537706-9704-4B69-9D6B-(E-Mail Removed)...
> > >A short time ago I was getting help with a macro that created a new sheet,
> > > and named it 'Analysis-Sheet'. Then, I copied everything from a sheet
> > > named
> > > 'Analysis of Interest Prior' to the 'Analysis-Sheet'. The code is below:
> > >
> > >
> > > Dim EntireRange As Long
> > > 'Delete the sheet "Summary-Sheet" if it exist
> > > Application.DisplayAlerts = False
> > > On Error Resume Next
> > > ThisWorkbook.Worksheets("Analysis-Sheet").Delete
> > > On Error GoTo 0
> > > Application.DisplayAlerts = True
> > >
> > > 'Add a worksheet with the name "Summary-Sheet"
> > > Set Basebook = ThisWorkbook
> > > Set Newsh = Basebook.Worksheets.Add
> > > Newsh.Name = "Analysis"
> > >
> > > Dim ws1 As Worksheet, ws2 As Worksheet
> > > Dim lngRow As Long, lngFRow As Long
> > >
> > > Set ws1 = Worksheets("Analysis of Interest Prior")
> > > Set ws2 = Worksheets("Analysis of Interest Current")
> > >
> > > ws1.Copy After:=ws2
> > > Set ws1 = ActiveSheet
> > > ActiveSheet.Name = "Analysis-Sheet"
> > >
> > > ws1.Columns("F:J").Clear
> > >
> > > Jacob Skaria helped mw with some of the code above.
> > >
> > > Now, I am trying to compare the values in Column A of 'Analysis-Sheet'
> > > (these values came from 'Analysis of Interest Prior') with the values in
> > > Column A of 'Analysis of Interest Current'. Then, I want to copy past the
> > > entire row from 'Analysis of Interest Current' if the value in Column A is
> > > not in Column A of 'Analysis of Interest Prior'. The purpose of this is
> > > that
> > > some new accounts are added to 'Analysis of Interest Current' from time to
> > > time, and I want to be able to pick up these new accounts in my analysis.
> > > For instance, I have account number 7250-0000 in both 'Analysis of
> > > Interest
> > > Prior' and 'Analysis of Interest Current', also, I have account number
> > > 7252-0000 in both 'Analysis of Interest Prior' and 'Analysis of Interest
> > > Current'. However, I account number 7254-0000 and account number
> > > 7255-0000
> > > only appear in 'Analysis of Interest Current', so I'd like to take copy
> > > all
> > > contents from both rows from 'Analysis of Interest Current' and place it
> > > under the end of the list of account numbers in 'Analysis-Sheet'. How can
> > > I
> > > do this?
> > >
> > > Ryan---
> > >
> > >
> > > --
> > > Ryan---
> > > If this information was helpful, please indicate this by clicking ''Yes''.
> >
> >
> >
|
Similar Threads
