Open a workbook & copy data using

[lostin_space]

Hi all... i've got what i think is a common problem, but i'm a bit stumped with it...

I need to copy data from workbook 2, into workbook 1 - then store the data on a hidden worksheet, so i can then format it & present it all...

the name of workbook 2 will vary - this is'nt a problem, as i want the user to be prompted with a dialogue box & and for them to point to the right file manually, as it may be stored in different folders etc... -

i think the workbook.open function would do it...

i could'nt find quite what i was after on the kb or in other threads - anyone got some code to hand that can do this?

Cheers

Russ

[malik641]

Are you combining all the sheets into one specified worksheet? Or are you copying each sheet individually and making new sheets in the main workbook you are copying to?

If you're looking for the syntax to copy a worksheet from one workbook into another, you could look at this:

Workbooks("Workbook_To_Copy_From").Worksheets("Sheet1").Copy _
        After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)

[lostin_space]

wanted to prompt the user to point to the file...

used this...

Public Sub fileopener()
    Dim myFileName As Variant
    Dim SourceWkbk As Workbook
    Dim CurrentWkbk As Workbook
    Dim testWks As Worksheet
    Dim DestCell As Range
    myFileName = Application.GetOpenFilename("Excel files,*.xls")
    If myFileName = False Then
        Exit Sub 'user hit cancel
    End If
    Set CurrentWkbk = ActiveWorkbook
    Set SourceWkbk = Workbooks.Open(Filename:=myFileName)
    Set testWks = Nothing
    On Error Resume Next
    Set testWks = SourceWkbk.Worksheets("sheet1")
    On Error GoTo 0
    If testWks Is Nothing Then
        MsgBox "Missing the worksheet!"
    Else
        With CurrentWkbk.Worksheets("Test")
            Set DestCell = .Cells(.Rows.Count, "a").End(xlUp).Offset(1, 0)
        End With
        With testWks
            'choose one of these
            .Range("a1", .Cells.SpecialCells(xlCellTypeLastCell)).Copy
            DestCell.PasteSpecial Paste:=xlPasteAll
            Application.CutCopyMode = False
        End With
    End If
    SourceWkbk.Close savechanges:=False
End Sub

does the trick... think it must just have been a 'brain phart' :-) as i've not done any coding 'in anger' for a while...

[fwawergurl16]

Hi there lostin_space,

I find ur code very applicable for the VBA macro that I'm suppose to solve! I'm an intern with a media company, but im in the IT side. I'm suppose to do a macro that helps copy & paste to combine 5 workbooks. In each workbook is only 1 sheet of table. I want to combine all 5 into just 1 workbook/sheet, so that it'll make pivoting easier. So far, I'm unsuccessful 'til I came across ur codes.

Would u be able to help me in showing me how i could loop the code to make it run 5 times for all the workbooks involved? I've attached some samples of the doc to be combined.

I hope you'll be able to help me since I'm terribly new to VBA (practically know nuts about it!). Thanks loads & sorry for the trouble caused.