Excel VBA : Replace() output used as range variable
I will do my best to explain this. Hopefully my notes in script will clarify my rambling. In short on completion this script will flip through historical production sheets compiling select data from theses sheets into one. Within the current code I am having trouble getting the Replace() output to work correctly when identifying the next empty cell to paste to. I am attempting to limit the amount of code changes needed when I begin moving through the work books section. I hope that makes sense. "copy paste needed code to copy past with limited changes. my idea was only having to change the target column." My skills are limited but I am learning so please feel free to give advise and feedback on method or design. at present I am stumped trying to debug the way my columnCEL variable is working in range selection. I appreciate anything constructive someone can give me. Links , info sources and so on I have been surfing this site and other for hours got a few things figured out but...
Sub parse() Dim strPath As String Dim columnCEL As String Dim lastrow As Range 'open WB to consolidate too Workbooks.Open "c:\prodplan\compiled\plancon.xlsx" Set lastrow = ActiveSheet.UsedRange.End(xlDown).Offset(1, 0) Set objexcel = CreateObject("Excel.Application") objexcel.Visible = True objexcel.DisplayAlerts = False strPath = "C:\prodplan" Set objfso = CreateObject("Scripting.FileSystemObject") Set objFolder = objfso.GetFolder(strPath) 'Loop through objWorkBooks For Each objfile In objFolder.Files If objfso.GetExtensionName(objfile.Path) = "xlsx" Then Set objworkbook = objexcel.Workbooks.Open(objfile.Path) objworkbook.Worksheets("plan").Select 'start header dates and shifts copy from objworkbook to consolidated WB objworkbook.Worksheets("plan").Range("b6:p7").Select Selection.copy Workbooks("plancon.xlsx").Worksheets("data").Range("B1").Select lastrow.Select ActiveCell.PasteSpecial 'start loop for objworkbook name copy to field in plancon corisponding with date/shift and copy/past select row data. objworkbook.Close False 'Move proccesed folder to new Dir End If 'remove msgbox after debug MsgBox "loop stoper remove for final application" Next objexcel.Quit End Sub "destination WB "Plancon table structure Date shift day Total sales Inventory Travel source WB NAME Thurs Night 3429 source WB NAME Fri Day 2735 source WB NAME Fri Night 5294 source WB NAME Sat Day 7672 source WB NAME Sat Night 0 Source structure Shift: Thurs Fri Fri Shift: Night Day Night Date: 40360 40361 40361 Total sales 3429 2735 5294 Inventory 1750.5045 1685.854 3374.3956 Travel
Tim, Andy thank you for your time. You both are correct it should be offset(1,0). Tim in regards to the active cell on opening the destination work book, I had set it to "a1" but removed that with the idea it would not be needed as the code would set the Lastcell active prior to paste in the designated column which will be hard coded for each paste. I was attempting to find a way that I would not have to change the designation for the end row each time I entered a new column for paste. I believe Andy's suggestion will simplify things for me. I have not seen usedrange when searching though other code for examples and understanding" I have updated the code to reflect the suggestions. I have also added examples of the source and destination tables. Source table contains about 78 row labels that will be copied to destination WB in the examples format. In a perfect world a select all transpose paste would get me there. Unfortunately there is too much garbage and filtering it manually for 87+ WB was not appealing. I will have to loop with either a compare equals copy range. Or search equals copy range for each row in the source. Any advice on what one is faster/more productive? Either way thank you for your help/being a sounding board.
Did more looking .. Found this in in a MS developer articular nowhere near the top of a search ranking. Either way it worked out.
Dim MyColumn As String Dim Here As String Here = ActiveCell.Address MyColumn = Mid(Here, InStr(Here, "$") + 1, InStr(2, Here, "$") - 2) lastrow = ActiveWorkbook.ActiveSheet.Range(MyColumn & "65536").End(xlUp).Offset(1, 0)