Command Button To Import Worksheets

[saban1974]

Super new to creating macros so need some help.

I have a 'master' workbook and 8 other workbooks that contain data on a worksheet that i need to import into the master workbook.

Essentially what I want is a commandbutton called 'import data'

The worksheets in every workbook are called 'userformworksheet' and the ranges are identical as well A:AH

any help getting started would be appreciated. thanks.

[Tinbendr]

Will the names of the workbooks to import be fixed?

Do all the imported worksheets end up on one sheet?

What version of Excel do you have?

[saban1974]

Will the names of the workbooks to import be fixed? YES

Do all the imported worksheets end up on one sheet? YES

What version of Excel do you have? 2010

Thanks.

[Tinbendr]

On the Master sheet, insert a ActiveX commandbutton. Double click on it and inside that event insert this code.

Please Login or Register  to view this content.

[saban1974]

hey, thanks for the reply. getting a debug error after i updated the code with the file names I'm using. i attached the file so you can see. do i need to add the directory of the file and/or do the files need to be open? again, thanks a lot so far.

[saban1974]

also, looking at the file i sent. it only has 2 of the 8 files i need to import from. pretty sure that's not an issue, i can just add those once i hear back

[Tinbendr]

Ugh! You have tables.

Ok, try this

Please Login or Register  to view this content.

[saban1974]

my bad, lol!

i think i'm close, but when i try running the macro getting a message that the file doesn't exist. here's the code so far

Please Login or Register  to view this content.

[Tinbendr]

You'll have to ensure that the path is correct. The way i do this is to navigate there with Windows Explorer, the click in the address bar and Ctrl-C. Then go back to the code and paste that into the MyPath. Be sure to include the trailing backslash.

[saban1974]

ok, got it to work, sort of

the data is importing, the file extension had to be changed to .xlsm from .xlsx. However, it's importing with the field names which I already have on the destination worksheet. is there any way to omit the first 2 rows from the files i'm importing from?


Sub CommandButton1_Click()
Dim WS_m As Worksheet
Dim WB_I As Workbook
Dim WBAry() As Variant
Dim NextRow As Long
Dim LastRow As Long
Dim MyPath As String

Set WS_m = ThisWorkbook.Worksheets("userformworksheet")

With WS_m
NextRow = .Cells.Find(What:="*", _
After:=.Cells.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row + 1
End With

MyPath = "Q:\Quality Assurance\2014\QA Forms\" 'Change as required.
ReDim WBAry(1) ' Total number of files -1
WBAry(0) = "QAForm v1 Tia.xlsm"
WBAry(1) = "QAForm v1 Omry.xlsm"

For A = 0 To UBound(WBAry)
Set WB_I = Workbooks.Open(MyPath & WBAry(A))

With WB_I.Worksheets("userformworksheet")
LastRow = .Cells.Find(What:="*", _
After:=.Cells.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
With .Range("A1:AH" & LastRow) 'change range as required.
.Copy WS_m.Range("A" & NextRow)
End With
End With
WB_I.Close False

With WS_m
NextRow = .Cells.Find(What:="*", _
After:=.Cells.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row + 1
End With
Next
End Sub

[Tinbendr]

change this line to the row number you want to start with.

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[saban1974]

this all worked, thanks a lot for the help.