Help Identifying the Last Row on the Correct File

R

reberryjr

Well-known Member
Joined
Mar 16, 2017
Messages
701
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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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

E
  • Question
VBA-help <3
Replies
1
Views
135
Peter_SSs
Peter_SSs
N
  • Solved
Copy/paste a row from within a loop, before that loop moves on to next cell
Replies
5
Views
238
Joe4
Joe4
H
  • Question
Copy from multiple worksheets and multiple source files in a folder and paste into multiple sheets in target workbook
Replies
0
Views
485
Hs72711
H
L
  • Solved
VBA help: how to set dynamic next available row
2
Replies
16
Views
659
Lil Stinker
L
M
  • Question
So I tried and I am new to VBA however tried this much and this worked .I really need help.
Replies
1
Views
158
Skyybot
Skyybot

Forum statistics

Threads
1,214,940
Messages
6,122,356
Members
449,080
Latest member
Armadillos

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