save original data after macro is run again

Hi
Experts

I have developed a macro which imports data from another excel file. 
Ex: consider I have 2 excel files A & B.
I run macro  in file A today, it copies whatever data from file B.
say 20 rows have been filled  in file A(starting from A5 to A25). I do some 
changes on the file A which has few conditional formatting and forlumas.
I re-run the macro after 8 days in file A to get the new updated data from 
file B.

The problem I am facing is >>>
"If I re-run the macro, it deletes the data from file A. It pastes 
everything starting from top left cell.
When I generated macro in file A, I have selected top left cell while 
recording macro.

My requirement::::::
 Now I want the macro to put the data from the  cell A27  onwards and also 
retain the changes to the data from Cell A5 to A25.
So everytime I run the macro it should add data to the excel file  from the 
available blank cell and not copy over the same data.

Thnanking in anticipation
0
MINALZUNKE (11)
7/6/2005 6:16:06 PM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
518 Views

Similar Articles

[PageSpeed] 56

Minal,

Posting your code would help.  But, perhaps a change from code like:

Range("A5").Select

to

Range("A65536").End(xlUp)(2).Select

will fix that problem.

HTH,
Bernie
MS Excel MVP


"MINAL ZUNKE" <MINALZUNKE@discussions.microsoft.com> wrote in message 
news:BCD8029F-321A-4EC3-9E86-564212DDC98B@microsoft.com...
> Hi
> Experts
>
> I have developed a macro which imports data from another excel file.
> Ex: consider I have 2 excel files A & B.
> I run macro  in file A today, it copies whatever data from file B.
> say 20 rows have been filled  in file A(starting from A5 to A25). I do some
> changes on the file A which has few conditional formatting and forlumas.
> I re-run the macro after 8 days in file A to get the new updated data from
> file B.
>
> The problem I am facing is >>>
> "If I re-run the macro, it deletes the data from file A. It pastes
> everything starting from top left cell.
> When I generated macro in file A, I have selected top left cell while
> recording macro.
>
> My requirement::::::
> Now I want the macro to put the data from the  cell A27  onwards and also
> retain the changes to the data from Cell A5 to A25.
> So everytime I run the macro it should add data to the excel file  from the
> available blank cell and not copy over the same data.
>
> Thnanking in anticipation 


0
Bernie
7/6/2005 6:25:03 PM
hi Bernie 

I have pasted code here

Windows("Qry_CNC MILL_JOB.xls").Activate
    Range("A2:E100").Select
    ActiveWindow.SmallScroll Down:=-87
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = 1
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = 1
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = 1
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = 1
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = 1
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = 1
    End With
    Selection.Copy
    Windows("job status xcel.xls").Activate
    Range("A5").Select
    ActiveSheet.Paste
    Range("F7").Select
    Application.CutCopyMode = False
    ActiveWorkbook.Save
End Sub

I hope to have a workable solution from you.
thnx


"Bernie Deitrick" wrote:

> Minal,
> 
> Posting your code would help.  But, perhaps a change from code like:
> 
> Range("A5").Select
> 
> to
> 
> Range("A65536").End(xlUp)(2).Select
> 
> will fix that problem.
> 
> HTH,
> Bernie
> MS Excel MVP
> 
> 
> "MINAL ZUNKE" <MINALZUNKE@discussions.microsoft.com> wrote in message 
> news:BCD8029F-321A-4EC3-9E86-564212DDC98B@microsoft.com...
> > Hi
> > Experts
> >
> > I have developed a macro which imports data from another excel file.
> > Ex: consider I have 2 excel files A & B.
> > I run macro  in file A today, it copies whatever data from file B.
> > say 20 rows have been filled  in file A(starting from A5 to A25). I do some
> > changes on the file A which has few conditional formatting and forlumas.
> > I re-run the macro after 8 days in file A to get the new updated data from
> > file B.
> >
> > The problem I am facing is >>>
> > "If I re-run the macro, it deletes the data from file A. It pastes
> > everything starting from top left cell.
> > When I generated macro in file A, I have selected top left cell while
> > recording macro.
> >
> > My requirement::::::
> > Now I want the macro to put the data from the  cell A27  onwards and also
> > retain the changes to the data from Cell A5 to A25.
> > So everytime I run the macro it should add data to the excel file  from the
> > available blank cell and not copy over the same data.
> >
> > Thnanking in anticipation 
> 
> 
> 
0
MINALZUNKE (11)
7/7/2005 7:39:04 AM
Change

    Range("A5").Select
    ActiveSheet.Paste


to

    Range("A65536").End(xlUp)(2).Select
    ActiveSheet.Paste

Bernie


"MINAL ZUNKE" <MINALZUNKE@discussions.microsoft.com> wrote in message 
news:042BAD28-A99F-4E98-967E-A1FBA50567E4@microsoft.com...
> hi Bernie
>
> I have pasted code here
>
> Windows("Qry_CNC MILL_JOB.xls").Activate
>    Range("A2:E100").Select
>    ActiveWindow.SmallScroll Down:=-87
>    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
>    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
>    With Selection.Borders(xlEdgeLeft)
>        .LineStyle = xlContinuous
>        .Weight = xlThin
>        .ColorIndex = 1
>    End With
>    With Selection.Borders(xlEdgeTop)
>        .LineStyle = xlContinuous
>        .Weight = xlThin
>        .ColorIndex = 1
>    End With
>    With Selection.Borders(xlEdgeBottom)
>        .LineStyle = xlContinuous
>        .Weight = xlThin
>        .ColorIndex = 1
>    End With
>    With Selection.Borders(xlEdgeRight)
>        .LineStyle = xlContinuous
>        .Weight = xlThin
>        .ColorIndex = 1
>    End With
>    With Selection.Borders(xlInsideVertical)
>        .LineStyle = xlContinuous
>        .Weight = xlThin
>        .ColorIndex = 1
>    End With
>    With Selection.Borders(xlInsideHorizontal)
>        .LineStyle = xlContinuous
>        .Weight = xlThin
>        .ColorIndex = 1
>    End With
>    Selection.Copy
>    Windows("job status xcel.xls").Activate
>    Range("A5").Select
>    ActiveSheet.Paste
>    Range("F7").Select
>    Application.CutCopyMode = False
>    ActiveWorkbook.Save
> End Sub
>
> I hope to have a workable solution from you.
> thnx
>
>
> "Bernie Deitrick" wrote:
>
>> Minal,
>>
>> Posting your code would help.  But, perhaps a change from code like:
>>
>> Range("A5").Select
>>
>> to
>>
>> Range("A65536").End(xlUp)(2).Select
>>
>> will fix that problem.
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>> "MINAL ZUNKE" <MINALZUNKE@discussions.microsoft.com> wrote in message
>> news:BCD8029F-321A-4EC3-9E86-564212DDC98B@microsoft.com...
>> > Hi
>> > Experts
>> >
>> > I have developed a macro which imports data from another excel file.
>> > Ex: consider I have 2 excel files A & B.
>> > I run macro  in file A today, it copies whatever data from file B.
>> > say 20 rows have been filled  in file A(starting from A5 to A25). I do 
>> > some
>> > changes on the file A which has few conditional formatting and 
>> > forlumas.
>> > I re-run the macro after 8 days in file A to get the new updated data 
>> > from
>> > file B.
>> >
>> > The problem I am facing is >>>
>> > "If I re-run the macro, it deletes the data from file A. It pastes
>> > everything starting from top left cell.
>> > When I generated macro in file A, I have selected top left cell while
>> > recording macro.
>> >
>> > My requirement::::::
>> > Now I want the macro to put the data from the  cell A27  onwards and 
>> > also
>> > retain the changes to the data from Cell A5 to A25.
>> > So everytime I run the macro it should add data to the excel file  from 
>> > the
>> > available blank cell and not copy over the same data.
>> >
>> > Thnanking in anticipation
>>
>>
>> 


0
Bernie
7/7/2005 11:48:46 AM
Reply:

Similar Artilces:

Running Money 2003 in Vista
Hi I run this version perfectly satisfactorily in my Vista Ultimate desktop.with my normal log on. No fiddling was required to get it to start. It worked perfectly out of the box, just as in XP Referring to emails on earlier versions in the Newsgroup, my data is in a Documents folder on the D Drive well away from Vista, though it is secured. In this machine SSL2 is not enabled I have a newish laptop running Windows Home Premium with a similar configuration for all my data on the D drive. I recently tried to open Money for the first time since installation using a copy of the Money fil...

Formatting a subtotal line with a macro
I already have in use a macro that runs a sort and subtutal function. I would like to format the subtotal row using visual basic since there is already a macro in place. Hi Look at this: ActiveCell.Font.Bold = True Regards, Per "DJDKAL" <daniel.kalfayan@hotmail.com> skrev i meddelelsen news:A86040B0-B4C4-4E11-A80C-E80132C9710F@microsoft.com... >I already have in use a macro that runs a sort and subtutal function. I > would like to format the subtotal row using visual basic since there is > already a macro in place. This is what I've g...

Errors saving to network drive
When my users open an Excel spreadsheet located on a network drive, make their changes, and then try to save the spreadsheet, they get an error message saying "Your changes could not be saved to Whateverfile.xls, but were saved to XXXXXXXX ( 8 digit hex number ). Close the existing document, then open the temporary document and save it under a new name" The users have been explicitly granted full rights, including Delete and Modify, to the shared folder in which the spreadsheet resides. I have disabled their Symantec Antivirus realtime protection for the time being. This pr...

How to make Run Integration work in GP 10
Can anyone tell me how to make the Run Integration tool work in GP 10. We just did our first client upgrade to GP 10 and the client has only 1 integration that they used to be able to run via the RUN INTEGRATION Tool. Any help would be greatly appreciated. -- Tammy Chavez I have the same probelm...did you get any answers to this? I have to go into Integration Manager to run it b/c Tools>Run Integration does nothing. I even checked the ini files to make sure the path is correct. "Tammy Chavez" wrote: > Can anyone tell me how to make the Run Integration tool work in...

Saving toolbar changes
After spending a lot of time to customize a toolbar in Excel 2003, it disappears when opening up another file, or starting the app again. I repeatedly change it, save it as XLB, XLT, save multiple copies in every possible location...but the damn thing always defaults to its own toolbar settings. This makes toolbars almost useless. How can one insist that PPT use YOUR toolbar setting, rather than its own default Thanks. Hi Jeff, If I have a lot of tool bar changes to make, I close all the workbook that are not hidden then unhide my personal.xls from the Window menu. I don't know why...

VB Macro #2
I have a macro that is generating Excel files. When the macro runs, it saves two different tabs in the workbooks as values. It needs to test and wait until the formulas that are pulling amounts from another database are correctly populated with the amounts. Right now, I am getting N/A# instead of amounts for some, but not all, of the files. I think the problem is that the formulas have not completed pulling the amounts before the macro pastes that tab as zeros. (N/A# is what appears when the formulas have not completed calculating - once they have completed, the N/A# is replaced ...

Can the data in a chart table be right justified?
Ecxel 2003 and previous versions of the product center the data in the data columns. Can the data in the columns of a chart table be right justified? In article <DABF738B-6C0D-458B-B082-FA9BD8F126A7@microsoft.com>, =?Utf- 8?B?c2FtIGVhZ2xl?= <sam eagle@discussions.microsoft.com> says... > Ecxel 2003 and previous versions of the product center the data in the data > columns. Can the data in the columns of a chart table be right justified? > Have you tried to format the table? If yes, and you haven't been successful it is probably because XL allows very limited cust...

Change cannot be saved due to sharing violation
Hello I've this message while saving the excel file even if no change ha been done to the file. There is no share on this file (exclusive use) File resides on a network drive It's very disturbing Thanks for your help Vobiscu -- Vobiscu ----------------------------------------------------------------------- Vobiscum's Profile: http://www.msusenet.com/member.php?userid=245 View this thread: http://www.msusenet.com/t-187102186 http://support.microsoft.com/default.aspx?scid=kb;en-us;328170 Thanks for your answer, I will try tomorrow noo Vobiscu -- Vobiscu ----------------...

Data migration - Adventure Works
Hiya... I have a company where the adventure works db has been used and had a lot of data populated into the system. We have now purchased MSCRM and have obtained the company reg keys. What is the easiest way to get the data from the 1 system to the next? We will be establishing a new AD domain and users for the new system.... Data Migration Framework? Redeployment Framework? ;) redeploment tools http://www.microsoft.com/downloads/details.aspx?FamilyID=bfced393-61db-49af-9a50-4a90b311fa7d&DisplayLang=en -- John O'Donnell Microsoft CRM MVP http://www.mscrmfaq.us "funboy...

Problem with macro assigned to custom toolbar button
Problem with macro assigned to custom toolbar button I am having a probelm with macros assigned to a custom button on custom button. I am using excel 2000. 1. I have created a macro called "Jump" in a workbook named "Test1" 2. I then created a custom toolbar and added a custom toolbar button t that toolbar. 3. Through the customize dialog box I assign the Macro "Jump" to th custom button. Everything works fine UNTIL. If I do a SAVE AS for the workbook "Test1" and call it something els like "Test2" and then close "Test2" an...

Label a chart of counts with other percentage data
Here's the data: Group 2005 2006 2007 LTM Data A 19.4% 22.8% 21.2% 19.9% Profitability A 6 7 7 7 Count B 9.5% 31.6% 30.4% 30.7% Profitability B 2 3 3 3 Count C 22.4% 23.6% 16.6% 17.6% Profitability C 15 16 17 18 Count D 19.2% 20.5% 15.9% 13.7% Profitability D 8 8 9 10 Count I have successfully generated a stacked bar chart that shows the counts per group by year. Now I would like to include a label for each group to show profitability for each group in each year in the 4 stacks. How would I do that? Thanks, --...

Creating a chart based on the data in an embedded worksheet
Hi, I have a worksheet with several embedded worksheets. I would like to create a chart based on the data of one of the embedded worksheets without putting the chart in the embedded worksheet. I have tried unsuccessfully to do this. I just wondered if anyone knew how to do it. Thanks, JK JK - You're embedding worksheets within worksheets? Why? Why not just insert the worksheets in line with the main worksheet? To open or edit the embedded worksheet, the parent Excel has to open another instance of Excel, and the chart on the outside of this other instance will never be able to acce...

Macros not performing correctly
I recorded a macro to place a vlookup formula in four cells that are side by side. When I run the macro it only performs the first task, it does not go on to do the other three. I used the tab key to go to the next cell while recording the macro, I don't know if that's why it's not working or if it's something else. The script looks like this: Sub EERates() ' ' EERates Macro ' Macro recorded 3/4/2005 by BCBSOK ' ' ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Sheet1!R3C1:R102C2,2,0)" Range("F15").Select ActiveCell.FormulaR1C...

Publisher can not save file
I recently started having troubles with my Publisher 2003. Whenever I go to save my files now, using save as or just the save button, it gives me a dialog box that says "Can not save file." It does that twice, then it disappears. I also noticed that when it does this, it leaves the .tmp files in the directory where I tried to save. I can change the name of the files, and sometimes it will save it. Most of the time not though. I have NAV, and I noticed that it was said there was an issue with Publisher and NAV. Is this the same with the 2005 version, and is this anything anyone...

Money 04 runs very slow
file size is 17,480 kb, about 5 years data. Machine is a P4 2.2 GHz, 512 mb ram. Money runs very slow, processing downloaded statements, switching screens, etc. I switched from Quicken about a year ago. That didn't run nearly this slow. Any ideas? Will trimming the file size help at all or much? Thanks Ideas? See http://umpmfaq.info/faqdb.php?q=4. Trimming? Most people haven't had great luck trying this through means like File|Archive--which, by the way, I'd discourage for any number of reasons. "Tom Orlando" <anonymous@discussions.microsoft.com> wrote i...

Run time error 5 : HQClient
When I double click HQ Client I have message box "Run time error 5 : Invalid procedure call or argument" how should I do? I used RMS 2.0 -- TOY2TOY ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGr...

Saving #5
Is there a way of speeding up saves in excel or a way to condense the size of a workbook. Jason, This link can help with the size issue... http://www.contextures.on.ca/xlfaqApp.html#Unused Item 2d is the one you want. Regards, Jim Cone San Francisco, USA "Jason Zischke" <JasonZischke@discussions.microsoft.com> wrote in message news:18CCEA1F-2479-4ED9-A5F6-21B781D0F403@microsoft.com... > Is there a way of speeding up saves in excel or a way to condense the size of > a workbook. ...

High Memory Usage after IS Maintenance Runs
I have an Exchange 5.5 server whose priv.edb file is about 2GBs. During normal usage the store.exe file is use about 70MBs of RAM. When the IS Maintenance runs at night the RAM usage shoots to 800MBS and does not come down after IS Maintenance is done. I have to restart the Information Store service to restore proper system operation. How do I fix this issue? Why do you think that proper operation is less than 800MB of RAM? By default, Exchange should take all of the system memory available and not 70MB (note that it should give it back when asked). That's normal behavior vs. w...

how to run onhand value report
I get the message enter parameter when entering the zoom feature On Sat, 6 Mar 2010 17:36:01 -0800, junebugg <junebugg@discussions.microsoft.com> wrote: >I get the message enter parameter when entering the zoom feature You'll have to give us some more context than that, junebugg. What's the "onhand value report"? What's the "zoom feature"? You can see your database; we cannot! -- John W. Vinson [MVP] ...

Saving document downloaded from email
I downloaded a word 2007 document from a windows mail email yesterday by double clicking on it and opening in word 2007. I then worked on the document and clicked "save". Today I cannot find the document on the computer, it seems that it has not saved. Can anyone explain what has gone wrong? Thanks Catherine If you use Word and Outlook 2007 this should no longer happen as the combination does not allow you to save to a temporary location With other e-mail applications, it is anyone's guess what might occur. You should never open Word attachments directly from e-m...

Data validation list from another worksheet?
Is it possible that the value list for data validation be populated fro another worksheet? Puneet Aror -- puneetarora_1 ----------------------------------------------------------------------- puneetarora_12's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1840 View this thread: http://www.excelforum.com/showthread.php?threadid=38572 Sure is! Use a named range as described here: http://www.officearticles.com/excel/drop-down_using_data_validation_in_microsoft_excel.htm ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "punee...

How do I save a header?
I need to permanently save a header and it will never save once I open up a new file. Any suggestions? Try a look in the Excel Help under 'Create a Template'. Saving the header in the template should mean each time you open that template the header will be there. Regards, Tom "Global10" wrote: > I need to permanently save a header and it will never save once I open up a > new file. Any suggestions? Open a new workbook. Customize as you wish including your print settings.........group the sheets before setup so's all sheets get same Heade...

"external data sources" and "external data ranges"
what is the difference between these? i have run the vb macro code on http://support.microsoft.com/kb/330383 to check if i have any external data sources or data ranges but there are none. the reason i need to know is becuase i'm working with office sharepoint server 2007 and i cannot access a file thru the web access web part because it says: "The workbook that you selected cannot be loaded because it contains the following features that are not supported by Excel Services: External data ranges (also called query tables)" anyone? thanks ...

Saving Pictures
I read all the info on the forum about saving pictures but when I open Power Poing, I don't get the usual buttons at the top. It opens to a page layout with Home, Insert, Page layout and so on. When I try to open the email just to see what files are in it, it shows the slide show. -- Gary So, first save it to your hard drive. Then open PowerPoint Then Open the file within PPT "skydiver9696" <skydiver9696@discussions.microsoft.com> a �crit dans le message de news: 396594B0-40D7-4D7F-A560-6F93662A8D57@microsoft.com... >I read all the info on the forum abou...

Difference between Publish and Save As Web Page in Excel 2002
I am trying to find aout the differences between using the Publish button in Save as Web Page and using Save in there. I have seen that when I use Save, it saves the Named ranges as bookmarks where Publish saves them as cell references. Can you tell me why and if there are any other differences I may need to know Hi, when you publish, the result can be viewed as an excel web page, whereas if you "save as web page", it just makes an xml sheet. Save As only allows one sheet in the workbook whereas publish allows more. - mark >-----Original Message----- >I am trying t...