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

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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
376
rollingzep
R
A
  • Solved
Extract and Merge Files
Replies
1
Views
135
rowebca
rowebca
C
  • Question Question
Using pattern searches with a variable to find the correct values
2
Replies
10
Views
266
chazrab
C
V
  • Question Question
VBA excel worksheet2 out of range even though the cell is within the range and additional value created
Replies
4
Views
257
MARK858
MARK858
I
  • Question Question
Unable to run VBA code
Replies
6
Views
165
RoryA
RoryA

Forum statistics

Threads
1,224,825
Messages
6,181,191
Members
453,021
Latest member
pingpong7117

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