Problem with Code (Inserting New Row)

[skaswani]

Hello,

here is my Code

[VBA]

Public bookname As String
Sub Macro2()

Dim Current As Worksheet

For Each Current In Worksheets


Windows([bookname]).Activate

Sheets(Current.Index).Select

Range("B1:B700").Select
Selection.Copy



Windows("matching_file.xls").Activate

' Selection.Insert Shift:=xlDown

'Range("A3:B3").Select
'Selection.Insert Shift:=xlDown

'Range("A3").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Next

Columns("A:A").Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal


End Sub
[/VBA]


Problem :- problem i am facing that if i use Range("A1").select & then Insert a Row (in run time) it gives me error

is this possible that i shouyldnot Insert New Row, but it will Append Data from last Unused row

i hope you got my point!

regards,

[rory]

Can you explain exactly what you are trying to achieve? Obviously you are copying data, but from which sheet(s) of which workbook and to which sheet(s) of which workbook? I suspect there are neater ways of achieving what you want, but we need to know exactly what you are doing.

[skaswani]

Hello,

ok, let me try

1st,

i get a Xls sheet every month which has 200+ worksheets with customer's information

i have to check/compare that the person present in Current Month File is in Last Month File or not!


2nd,

i am making a form in Excel,

which will take 2 Variables,

1) Previous File
2) Current File



The Common Key b/w Both File id Customer No which in both Sheet is in Cell "B"


in "Column M " i have to apply a Formula, by which display will be "Found " or "Not Found"


if the result says not found then automatically ROW color change to RED



so, this will be done in a Loop ,

btw, Sheets are identical , i mean both Workbook have same Sheets Names



i hope i have clear my point,


what i did in my previous code , i though i should Copy/Paste all Account # in a Temp Sheet so that I?ll compare (by Vlookup or Match or any mean)

regards

[YellowLabPro]

Hello Skaswani,
Lets put the my comment on-hold for a moment.

It appears you are selecting columns, "A:A" and trying to insert a row.
If this is correct, you need to select a row, Rows(1).entirerow.insert.

Where is your code breaking, you are familiar w/ stepping through your code I take it?


Looks like you have had good success here, glad you found your way to the board.

Doug

[skaswani]

Doug

Sorry, i didnot get it

Rows(1).entirerow.insert. ?
where should i write & is this a Code??

[YellowLabPro]

Put that on hold for now, I jumped the gun. I mis-read something in your code and that suggestion was offered in haste.
Run through your code and tell us what line it is breaking on.

[skaswani]

i think i should use Vlookup mannually

[rory]

Is there one worksheet in each file per customer or multiple customers on each worksheet? It might be useful to see a sample of your data.

[YellowLabPro]

Skaswani,
If you can put some sample data up like Rory mentions, also provide the line of code where the code fails will help too.

[skaswani]

actuly the sheets are based on Branches!

so, One sheet have many/few records

[skaswani]

Hello,

I have change a scenario,


I am attaching sample files, for the month of Jun & July

I have a Compare.xls file, which take 2 filenames in variable,

Open them & WANT TO apply this code, but I don?t know to do it in LOOP & with Variables



Code is

[VBA]
IF(B7>0,IF(ISNA(VLOOKUP(B7,'\text1 A'!$B$1:$B$550,1,0)),"Not Found", "OK"),"")
[/VBA]

A is the Sheet name

Please kindly help me

[skaswani]

no reply?

[YellowLabPro]

S-
I am hung up for a while tonight.... I will look at as soon as I have some time.

Doug

[skaswani]

man.. where are u

[rory]

Let me see if I understand this correctly:
From Compare.xls you want to open this month's and last month's workbooks, check each sheet for this month against the corresponding sheet for last month and check all this month's customers to see if they are in last month's file? If that's right, then what do you want to do if:
1. There are new/different branches between the months?
2. A customer was present last month but not this month?

Also, how can the code identify which cells contain the customer name and ID? They seem to have the same format as the 'X-Days past due' cells which doesn't help.

[YellowLabPro]

S-
I am going to wait to see what Rory comes up with, I have been tied up w/ work.

[skaswani]

Rory .. plsssssssssss