Copy paste data from a Workbook opened from Application.GetOpenFilename

[cidfidou]

Hi Excel Ninjas,

As i am new to VBA and I have been stuck for hours now, I would really appreciate your help.

I am trying to copy paste the first two tabs of a file that can be located anywhere on someones machine. (This is why I am trying to use the application get open file and to assign it a dim = WB2op
It seems that the reference to the workbooks is not correct when i go from one to the other...

Please help!!!


Thanks in advance


Sub ImportFile()

Dim WB2op As String
Dim CurWB As Workbook


Set CurWB = ThisWorkbook


WB2op = Application.GetOpenFilename _
(Title:="Please choose File", _
FileFilter:="Excel Files *.xlsx* (*.xlsx*),")


If WB2op = "False" Then
MsgBox "No file selected.", vbExclamation
Exit Sub
'Workbooks("mybook").Sheets("mysheet").Range("A1:A10"
Else


Workbooks.Open Filename:=WB2op

Windows("CurWB").Activate
Sheets(5).Select
Range("A1:AA15000").ClearContents


Windows("WB2op").Activate
Sheets(1).Select
Range("A1:N15000").Copy


Windows("CurWB").Activate
Sheets(5).Range("A1").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=True, Transpose:=False
Application.CutCopyMode = False
Sheets(5).Range("A1").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False



Windows("CurWB").Activate
Sheets(4).Select
Range("A1:AA30000").ClearContents

Windows("WB2op").Activate
Sheets(2).Select
Range("A1:N15000").Copy


Windows("CurWB").Activate
Sheets(4).Range("A1").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=True, Transpose:=False
Application.CutCopyMode = False
Sheets(4).Range("A1").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False







End If


End Sub

 

[JLGWhiz]

see if this runs a little better.

Sub ImportFile()
 Dim WB2op As String, CurWB As Workbook, WB2 As Workbook
 Set CurWB = ThisWorkbook
 WB2op = Application.GetOpenFilename _
 (Title:="Please choose File", _
 FileFilter:="Excel Files *.xlsx* (*.xlsx*),")
    If WB2op = "False" Then
        MsgBox "No file selected.", vbExclamation
        Exit Sub
    Else
        Set WB2 = Workbooks.Open(WB2op)
        CurWB.Sheets(5).Range("A1:AA15000").ClearContents
        WB2.Sheets(1).Range("A1:N15000").Copy
        CurWB.Sheets(5).Range("A1").PasteSpecial Paste:=xlPasteValues
        CurWB.Sheets(5).Range("A1").PasteSpecial Paste:=xlPasteFormats
        Application.CutCopyMode = False
        CurWB.Sheets(4).Range("A1:AA30000").ClearContents
        WB2.Sheets(2).Range("A1:N15000").Copy
        CurWB.Sheets(4).Range("A1").PasteSpecial Paste:=xlPasteValues
        CurWB.Sheets(4).Range("A1").PasteSpecial Paste:=xlPasteFormats
        Application.CutCopyMode = False
    End If
 End Sub

 

[cidfidou]

Hi JL,

Wouah!! Thank You so much... You made my day..Amazing support!!! I would love to offer you a beer for that!!!

Quick question : I have changed CurWB.Sheets(5) to CurWB.Sheets(tabname) as it was pasting in the wrong tab despite the fact it is sheet 5 in VBA project.

 

[cidfidou]

Also, how can I close WB2op?? thanks again

 

[JLGWhiz]

Also, how can I close WB2op?? thanks again

WB2.Close False

The False prevents the "Do you want to save it" alert from showing.

~Quick question : I have changed CurWB.Sheets(5) to CurWB.Sheets(tabname) as it was pasting in the wrong tab despite the fact it is sheet 5 in <acronym title="visual basic for applications">VBA</acronym> pro

The sheet index number (in your case 5) is based on the pecking order from left to right of the name tabs shown at the bottom of the screen. It is better to use the actual sheet name to identify the sheet or put the sheet into an Object Variable and use that throughout the code to save space. I say that with toungue in cheek, since some people create variables longer than the sheet name. But the Object Variables have more advantages than space saving as you will learn over time.

 

[cidfidou]

Thanks JL. Really appreciate your help. Greetings from Ireland

 

[JLGWhiz]

Thanks JL. Really appreciate your help. Greetings from Ireland

Happy to assist,
Regards, JLG

 

[cidfidou]

Hi JL,

I am gonna push my luck a bit more by asking how to add an extra step to make sure that the file that will be opened with Application.GetOpenFilename starts with let say 5xs (XXXXX_Report_Date). I am just trying this step to make sure that the user can not import any file and ruin the template.

As usual, thanks in advance