Running macro into sheet1 changes formulas in sheet2 :(

mosu90 · #1 08-24-2008, 07:30 PM

Hy guys, i`m a new user, and a newbie in excel. However i realised some days ago i really need to make sth in excel.

I have a workbook where i registered a macro which gets some data from a .rtf delimited file and shows it in 1st sheet.

Now, i 2nd sheet i tried to order the data, so get just the data i need from sheet1, in the order i need.

This is one of the formula i use to get a string, and then delete some part to get just what i need.
=REPLACE(FM!C27,4,200,"")

The problem is that if i enter data manually in first sheet, it works. But if i try to RUN the macro to get info from other file (all files have exactly the same format) the formulas in 2nd sheet changes, and the data is not shows as it should.

For example that formula becomes =REPLACE(FM!R27,4,200,"")

i'll post here also my macro code( i didn't wrote it, i registered a macro)

Code:

Sub ImportFieldPlayer()
'
' ImportFieldPlayer Macro
'

'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\player.rtf" _
        , Destination:=Range("$A$1"))
        .Name = "28. João Luís Pereira Cerqueira"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 1252
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = True
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileOtherDelimiter = "|"
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

Thanks for your help, i`m waiting for a reply

mosu90 · #2 08-25-2008, 07:23 AM

ok, i realised that when i was running macro..some columns were added, and the old ones were moved to the right, so i added a code to delete all data in 1st sheet and now everything works.

now i have other problem, i have an other workbook with a lot of formulas, and when i want to copy them into my currentbook the formulas are not coppied , just the values

...what can i do?

SORRY FOR DOUBLE POST, but i posted it to realise that sth new was posted

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Macro stop running	schnett	Excel Worksheet Functions	1	04-10-2008 05:01 PM
Sheet2 does not match sheet1 when using =sheet1=a1 after insert row	b11nnl	Excel General	4	08-30-2007 08:14 AM
running a macro automatically	gbWildy	Excel Programming	1	07-19-2007 04:16 PM
Update sheet2 with sheet1	tqm1	Excel Programming	2	05-25-2007 05:20 AM
Select cell with mouse while running macro	kesse	Excel Programming	3	10-02-2006 04:45 PM

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)