Help Identifying the Last Row on the Correct File

R

reberryjr

Well-known Member
Joined
Mar 16, 2017
Messages
714
Office Version
  1. 365
Platform
  1. Windows
I found some code that I'm trying to deploy in my workbook. It relates to another post I made, but since I found what appears to be an alternate solution, I started a new thread. Hopefully that's not against protocol.

https://www.mrexcel.com/forum/excel...ocedure-build-opening-files-copying-data.html

This code, is supposed to loop through the contents of a specific folder, activate each workbook, copy data and paste it to a different workbook. The code is activating the first file, but it's not pasting anything. It actually appears to be reading the receiving file as the source file, since it's telling me that the LastRow is the 3rd row, when it is actually the 1st row. I've been looking at this for a couple of hours now, so I'm hoping that another set of eyes can help.

Code: 
Sub ImportWorksheets()

   Dim sFile, FolderPath As String          'file to process
   Dim tsws3 As Worksheet     'Bios sheet in trainer wkbk
   Dim ts, cs As Workbook     'client wkbk
   Dim csws3 As Worksheet     'Bio sheet in client wkbk
   Dim rowTarget As Long         'output row
   Dim tsLastRow3 As Long
   FolderPath = "C:\Users\Rodger\Desktop\Bodies by Trish\Client Sheets\"
   
   ThisWorkbookName = ActiveWorkbook.Name
   
   'check the folder exists
   If Not FileFolderExists(FolderPath) Then
      MsgBox "Specified folder does not exist, exiting!"
      Exit Sub
   End If
   
   'reset application settings in event of error
   On Error GoTo errHandler
   Application.ScreenUpdating = False
   
   'set up the target worksheet
   Windows(ThisWorkbookName).Activate
   Set tsws3 = ThisWorkbook.Sheets("Bios")
   
   tsLastRow3 = tsws3.Range("C" & Rows.Count).End(xlUp).Row
   
   'loop through the Excel files in the folder
   sFile = Dir(FolderPath & "*.xlsm*")
   Do Until sFile = ""
      
      'open the source file and set the source worksheet - ASSUMED WORKSHEET(1)
      Set cs = Workbooks.Open(FolderPath & sFile)
      Set csws3 = cs.Sheets("Bio") 'EDIT IF NECESSARY
      
      'import the data
      With tsws3
         .Range("A" & tsLastRow3 + 1).Value = "=Today()"
         .Range("B" & tsLastRow3 + 1).Value = csws3.Range("B3").Value
         .Range("C" & tsLastRow3 + 1).Value = csws3.Range("C3").Value
         .Range("D" & tsLastRow3 + 1).Value = csws3.Range("D3").Value
         .Range("E" & tsLastRow3 + 1).Value = csws3.Range("E3").Value
         .Range("F" & tsLastRow3 + 1).Value = csws3.Range("G3").Value
         .Range("G" & tsLastRow3 + 1).Value = csws3.Range("C7").Value
         .Range("H" & tsLastRow3 + 1).Value = csws3.Range("H3").Value
         .Range("I" & tsLastRow3 + 1).Value = csws3.Range("E7").Value
         .Range("J" & tsLastRow3 + 1).Value = csws3.Range("F7").Value
         .Range("K" & tsLastRow3 + 1).Value = csws3.Range("G7").Value
         .Range("L" & tsLastRow3 + 1).Value = csws3.Range("B11").Value
         .Range("M" & tsLastRow3 + 1).Value = csws3.Range("B12").Value
         .Range("N" & tsLastRow3 + 1).Value = csws3.Range("C11").Value
         .Range("O" & tsLastRow3 + 1).Value = csws3.Range("C12").Value
         .Range("P" & tsLastRow3 + 1).Value = csws3.Range("D11").Value
         .Range("Q" & tsLastRow3 + 1).Value = csws3.Range("D12").Value
         .Range("R" & tsLastRow3 + 1).Value = csws3.Range("E11").Value
         .Range("S" & tsLastRow3 + 1).Value = csws3.Range("E12").Value
         .Range("T" & tsLastRow3 + 1).Value = csws3.Range("F11").Value
         .Range("U" & tsLastRow3 + 1).Value = csws3.Range("F12").Value
         .Range("V" & tsLastRow3 + 1).Value = csws3.Range("G11").Value
         .Range("W" & tsLastRow3 + 1).Value = csws3.Range("G12").Value
         .Range("X" & tsLastRow3 + 1).Value = csws3.Range("H11").Value
         .Range("Y" & tsLastRow3 + 1).Value = csws3.Range("H12").Value
         .Range("Z" & tsLastRow3 + 1).Value = csws3.Range("C17").Value
         .Range("AA" & tsLastRow3 + 1).Value = csws3.Range("D17").Value
         .Range("AB" & tsLastRow3 + 1).Value = csws3.Range("C16").Value
         .Range("AC" & tsLastRow3 + 1).Value = csws3.Range("D16").Value
         .Range("AD" & tsLastRow3 + 1).Value = csws3.Range("B21").Value
         .Range("AE" & tsLastRow3 + 1).Value = csws3.Range("E21").Value
         .Range("AF" & tsLastRow3 + 1).Value = csws3.Range("B22").Value
         .Range("AG" & tsLastRow3 + 1).Value = csws3.Range("C22").Value
         .Range("AH" & tsLastRow3 + 1).Value = csws3.Range("D22").Value
         .Range("AI" & tsLastRow3 + 1).Value = csws3.Range("E22").Value
         .Range("AJ" & tsLastRow3 + 1).Value = csws3.Range("F22").Value
         .Range("AK" & tsLastRow3 + 1).Value = csws3.Range("B23").Value
         .Range("AL" & tsLastRow3 + 1).Value = csws3.Range("C23").Value
         .Range("AM" & tsLastRow3 + 1).Value = csws3.Range("D23").Value
         .Range("AN" & tsLastRow3 + 1).Value = csws3.Range("E23").Value
         .Range("AO" & tsLastRow3 + 1).Value = csws3.Range("F23").Value
         .Range("AP" & tsLastRow3 + 1).Value = csws3.Range("B24").Value
         .Range("AQ" & tsLastRow3 + 1).Value = csws3.Range("C24").Value
         .Range("AR" & tsLastRow3 + 1).Value = csws3.Range("D24").Value
         .Range("AS" & tsLastRow3 + 1).Value = csws3.Range("E24").Value
         .Range("AT" & tsLastRow3 + 1).Value = csws3.Range("F24").Value
         .Range("AU" & tsLastRow3 + 1).Value = csws3.Range("B25").Value
         .Range("AV" & tsLastRow3 + 1).Value = csws3.Range("C25").Value
         .Range("AW" & tsLastRow3 + 1).Value = csws3.Range("D25").Value
         .Range("AX" & tsLastRow3 + 1).Value = csws3.Range("E25").Value
         .Range("AY" & tsLastRow3 + 1).Value = csws3.Range("F25").Value
         .Range("AZ" & tsLastRow3 + 1).Value = csws3.Range("B26").Value
         .Range("BA" & tsLastRow3 + 1).Value = csws3.Range("C26").Value
         .Range("BB" & tsLastRow3 + 1).Value = csws3.Range("D26").Value
         .Range("BC" & tsLastRow3 + 1).Value = csws3.Range("E26").Value
         .Range("BD" & tsLastRow3 + 1).Value = csws3.Range("F26").Value
         .Range("BE" & tsLastRow3 + 1).Value = csws3.Range("B27").Value
         .Range("BF" & tsLastRow3 + 1).Value = csws3.Range("C27").Value
         .Range("BG" & tsLastRow3 + 1).Value = csws3.Range("D27").Value
         .Range("BH" & tsLastRow3 + 1).Value = csws3.Range("E27").Value
         .Range("BI" & tsLastRow3 + 1).Value = csws3.Range("F27").Value
         .Range("BJ" & tsLastRow3 + 1).Value = csws3.Range("B28").Value
         .Range("BK" & tsLastRow3 + 1).Value = csws3.Range("C28").Value
         .Range("BL" & tsLastRow3 + 1).Value = csws3.Range("D28").Value
         .Range("BM" & tsLastRow3 + 1).Value = csws3.Range("E28").Value
         .Range("BN" & tsLastRow3 + 1).Value = csws3.Range("F28").Value
         .Range("BO" & tsLastRow3 + 1).Value = csws3.Range("B29").Value
         .Range("BP" & tsLastRow3 + 1).Value = csws3.Range("C29").Value
         .Range("BQ" & tsLastRow3 + 1).Value = csws3.Range("D29").Value
         .Range("BR" & tsLastRow3 + 1).Value = csws3.Range("E29").Value
         .Range("BS" & tsLastRow3 + 1).Value = csws3.Range("F29").Value
         .Range("BT" & tsLastRow3 + 1).Value = csws3.Range("B30").Value
         .Range("BU" & tsLastRow3 + 1).Value = csws3.Range("C30").Value
         .Range("BV" & tsLastRow3 + 1).Value = csws3.Range("D30").Value
         .Range("BW" & tsLastRow3 + 1).Value = csws3.Range("E30").Value
         .Range("BX" & tsLastRow3 + 1).Value = csws3.Range("F30").Value
         .Range("BY" & tsLastRow3 + 1).Value = csws3.Range("B31").Value
         .Range("BZ" & tsLastRow3 + 1).Value = csws3.Range("C31").Value
         .Range("CA" & tsLastRow3 + 1).Value = csws3.Range("D31").Value
         .Range("CB" & tsLastRow3 + 1).Value = csws3.Range("E31").Value
         .Range("CC" & tsLastRow3 + 1).Value = csws3.Range("F31").Value
         .Range("CD" & tsLastRow3 + 1).Value = csws3.Range("B32").Value
         .Range("CE" & tsLastRow3 + 1).Value = csws3.Range("C32").Value
         .Range("CF" & tsLastRow3 + 1).Value = csws3.Range("D32").Value
         .Range("CG" & tsLastRow3 + 1).Value = csws3.Range("E32").Value
         .Range("CH" & tsLastRow3 + 1).Value = csws3.Range("F32").Value
         .Range("CI" & tsLastRow3 + 1).Value = csws3.Range("B33").Value
         .Range("CJ" & tsLastRow3 + 1).Value = csws3.Range("C33").Value
         .Range("CK" & tsLastRow3 + 1).Value = csws3.Range("D33").Value
         .Range("CJ" & tsLastRow3 + 1).Value = csws3.Range("E33").Value
         .Range("CK" & tsLastRow3 + 1).Value = csws3.Range("F33").Value
      End With
      
      'close the source workbook, increment the output row and get the next file
      cs.Close SaveChanges:=False
      'rowTarget = rowTarget + 1
      sFile = Dir()
   Loop
   
errHandler:
   On Error Resume Next
   Application.ScreenUpdating = True
   
   'tidy up
   Set csws3 = Nothing
   Set cs = Nothing
   Set tsws3 = Nothing
End Sub
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
I would better qualify this line as you know the workbook name instead of Set tsws3 = ThisWorkbook.Sheets("Bios")
 
Upvote 0
I'm not sure that I understand what you mean by "better qualify" that line. That was how I learned to set the sheets, so I'm not aware of another method.
 
Upvote 0
what I mean is name the workbook rather than using thisworkbook method, it jusy makes thongs clearer in the code and also thisworkbook only applies to the workbook the code is in
 
Upvote 0
So, I figured out the issue I was having, and it's too embarrassing to relay here. LOL. I am struggling with another issue, in the same code...my LastRow argument. Essentially, the receiving workbook has column headers. So, without any data, the LastRow is 1. My code has the update going to LastRow+1, so it will update Row2. However, when I loop to the next workbook, it's still seeing row 1 as the last row, instead of row 2. Thoughts on how to make the LastRow more dynamic?
 
Upvote 0
Actually, I may have figured it out. I moved the LastRow piece, inside of the Do Until, and it appears to be working...for now.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Loop through Rows
Replies
4
Views
295
rollingzep
R
V
  • Question Question
VBA excel worksheet2 out of range even though the cell is within the range and additional value created
Replies
4
Views
213
MARK858
MARK858
D
  • Solved
Merge files and keep source formatting.
Replies
9
Views
312
Alex Blakenburg
A
B
  • Question Question
Excel VBA Searching Unique Variables to Match against Exported Schedule Files to Determine Date
Replies
1
Views
124
sijpie
sijpie
S
  • Question Question
Gathering Data From Multiple Files in Same Folder in Particular Range
3 4 5
Replies
41
Views
1K
kvsrinivasamurthy
K

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top