View Full Version : Problem with Code (Inserting New Row)
skaswani
09-05-2007, 06:00 AM
Hello,
here is my Code
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
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,
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
09-05-2007, 09:23 AM
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
09-05-2007, 09:51 AM
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
09-05-2007, 10:27 AM
Doug
Sorry, i didnot get it
Rows(1).entirerow.insert. ?
where should i write & is this a Code??
YellowLabPro
09-05-2007, 10:56 AM
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
09-06-2007, 06:06 AM
i think i should use Vlookup mannually :(
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
09-06-2007, 07:24 AM
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
09-06-2007, 07:28 AM
actuly the sheets are based on Branches!
so, One sheet have many/few records
skaswani
09-07-2007, 06:21 AM
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
IF(B7>0,IF(ISNA(VLOOKUP(B7,'\text1 A'!$B$1:$B$550,1,0)),"Not Found", "OK"),"")
A is the Sheet name
Please kindly help me
skaswani
09-07-2007, 01:07 PM
no reply?
YellowLabPro
09-07-2007, 01:37 PM
S-
I am hung up for a while tonight.... I will look at as soon as I have some time.
Doug
skaswani
09-11-2007, 06:11 AM
man.. where are u
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
09-11-2007, 07:41 AM
S-
I am going to wait to see what Rory comes up with, I have been tied up w/ work.
skaswani
09-11-2007, 09:17 AM
Rory .. plsssssssssss
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.