More Specific Data-Pull Code

[SoCoMike]

Hey all, so I'm working with the following code to pull information from all workbooks in a folder.

Sub OpenFile()
Dim sPath As String
Dim sFil As String
Dim strName As String
Dim twbk As Workbook
Dim owbk As Workbook
Dim ws As Worksheet

Set twbk = ActiveWorkbook
sPath = "Q:\SAFETY\Leadership Safety Audit\Solomon\2018\Training Lab\Audits"
sFil = Dir(sPath & "*.xls")
Do While sFil <> ""
strName = sPath & sFil
Set owbk = Workbooks.Open(strName)
Set ws = owbk.Sheets(1)
ws.Range("A1:L1", Range("A" & Rows.Count).End(xlUp)).Copy
twbk.Sheets(1).Range("A65536").End(xlUp)(2).PasteSpecial xlPasteValues
owbk.Close False
sFil = Dir
Loop
twbk.Save
End Sub

It works, but I would like it to be more precise.
For starters, it will only pull from the active sheet of each in the folder.
Is there a way to be more specific? All of my workbooks have the information on the same sheet, all named 'Sheet 1', but aren't always the last sheet opened before save/close.

Thanks in advance.

 

[Fluff]

Step through the code using F8 & see what happens.

 

[SoCoMike]

And now the macro isn't working at all, even after replacing it with the code just used prior. :/

EDIT: Okay, got the old code to work.. adding another module instead of editing this one and going to try.

 

[SoCoMike]

The following lines highlight yellow when stepping through the code.

Sub OpenFile()

Set twbk = ActiveWorkbook

sPath = "Q:\SAFETY\Leadership Safety Audit\Solomon\2018\Training Lab\Audits"

sFil = Dir(sPath & "*.xls")

Do While sFil <> ""

twbk.Save

End Sub

 

[Fluff]

Other than you are missing a \ at the end of your path, I can't see anything wrong & it works for me.

"Q:\SAFETY\Leadership Safety Audit\Solomon\2018\Training Lab\Audits[COLOR=#ff0000]\[/COLOR]"

Also none of that code has been changed, so if it worked before it should still work now.
What error messages are you getting?

 

[SoCoMike]

I figured the error part out, I messed up trying to make copies of the original.

Crap, when I first started working with this code that threw me off, the missing trailing slash at the destination path.. I figured it out before, had a brainfart.

Code works.. only issue is each pull is being added to the right instead of starting a new row beneath the sheet prior.

 

[Fluff]

Ok, add this line

Lr = .Range("A" & .Rows.Count).End(xlUp)(2).Row
[COLOR=#ff0000]         i=0[/COLOR]
         For Each Rng In ws.Range("F2,F3, C3, C2, H12, H21, H30, H39, H45, H55, H61")

 

[SoCoMike]

How it works now:

Cells are pasted in A2, B2, C2, D2, E2, F2, G2, H2, I2, J2, K2 for the first workbook.
Then L3, M3, N3, O3, P3, Q3, R3, S3, T3, U3, & V3 for the second workbook.
Then it fills W3 all the way to UZ3 with the rest of the information.

How it should operate:

Each workbook is entered in cells A:K, each workbook entered into that range in the row below that.
First workbook pull - A2:K2
Second workbook pull - A3:K3
Third pull - A4:K4
etc

 

[SoCoMike]

Beautiful!!

 

[Fluff]

Glad to help & thanks for the feedback