Excel (take worksheet from 2 files?)

**mehere** · mehere, February 17th, 2009 01:44 PM

to step through a macro, you would go into Tools >> Macro >> Visual Basic Editor. if Macro1 isn't showing then on the left hand side, double click Module 1 to pull up the macro.

in the gray area, click you mouse next to this line: Workbooks.Open Filename:=varFile1, this will put a dot in the gray area and highlight the line. then click Debug >> Step Into. then to go to each line, click F8 and watch what it's doing. let me know exactly which line is erroring.

**Rebelle** · Rebelle, February 17th, 2009 01:56 PM

w00t...I stepped thru.

i could see it opened file1 and highlighted header column and rows A-D like I want and then it did a copy but after error.

Still giving the error on line below in bold:

Code:

'Full File Names
    varFile1 = strLocation & "\" & strFile1
    varFile2 = strLocation & "\" & strFile2
    
    'Open strFile1 for copying
    Workbooks.Open Filename:=varFile1
    If WorksheetFunction.CountA(Cells) > 0 Then
        'Search for any entry, by searching backwards by Rows.
        LastRow = Cells.Find(What:="*", After:=[A1], _
              SearchOrder:=xlByRows, _
              SearchDirection:=xlPrevious).Row
              'MsgBox LastRow
    End If
    strRange1 = "A1:D" & LastRow
    Range(strRange1).Select
    Selection.Copy
    Windows("Final_Template").Activate

**mehere** · mehere, February 17th, 2009 01:57 PM

make sure you only have 1 copy of final_template.xls open.

**Rebelle** · Rebelle, February 17th, 2009 02:14 PM

not sure why machine didn't like it without .xls but i added it for all final_template and works beautifully!!! You're awesome!

**mehere** · mehere, February 17th, 2009 02:17 PM

cool ... it could be the version of software that requires that. i was just gonna suggest doing that. test it with two real files and make sure it works the way you want it to.

**Rebelle** · Rebelle, February 18th, 2009 11:52 AM

w00t..this is soooo good. i was able to follow the code you applied and got it to create a pivot table and pull in another file with info.

but i have another question. if you take File2.xls in post dated Feb 13 from me, i start with adding 4 columns A-D and then in A2 - D2 enter formulas, but then I have to copy/paste all down to last row. Can this be done with code to automatically copy/paste down to last row?

I've tried with another file in the past that uses vlookup for 5 columns but it would never copy down.

**Mythily** · Mythily, April 21st, 2009 11:51 AM

You can use drag and drop, instead of copying it till the end.

**mehere** · mehere, April 21st, 2009 01:04 PM

w00t..this is soooo good. i was able to follow the code you applied and got it to create a pivot table and pull in another file with info.

but i have another question. if you take File2.xls in post dated Feb 13 from me, i start with adding 4 columns A-D and then in A2 - D2 enter formulas, but then I have to copy/paste all down to last row. Can this be done with code to automatically copy/paste down to last row?

I've tried with another file in the past that uses vlookup for 5 columns but it would never copy down.

i never saw this question ... yes it can be done in code. you just need to be able to get the last row and last column being used than you can just select that range and paste it. if you can show me what you need, i can write it up real quick.

**Rebelle** · Rebelle, April 21st, 2009 04:34 PM

Hey Mehere,

Ok, I've attached spreadsheet. I get the file with only second tab __Data...without the columns highlighted. I then add the first tab __Lookup and then I add highlighted columns on __Data tab. I create a vlookup on the __lookup data..you'll be able to see formulas.

I'd like to be able to run a macro to input the formulas from B2:K2 and copy/paste all the way down. Every time I get this file, the number of rows will be different...

Thanks so much for looking at this!

**mehere** · mehere, April 22nd, 2009 02:37 PM

will it always be column B through column K? if so, here's the VBA Code ...

Code:

Sub copyFormulas()
    lastRow = ActiveSheet.UsedRange.Rows.Count
    Range("B2:K2").Select
    Selection.Copy
    Range("B3:K" & lastRow).Select
    ActiveSheet.Paste
End Sub

Thread: Excel (take worksheet from 2 files?)

LinkBack

Thread Tools

Display

Similar Threads

Tip for Reports Exported to Excel

From excel to database/web app

Dynamically Reference Worksheet Cell/Range

Excel question

Retrieving Web Data using Excel

Bookmarks

Bookmarks

Posting Permissions