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.

Answers


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)

Need Your Help

how to open a shell window output print real time debug information in python

python shell debugging redirect

Can I open a shell window for print debug information in python without break the execute sequence of code?

Django Pillow or Pil required when submitting images with forms?

django forms python-imaging-library imagefield pillow

OK. Been working on submitting a form where on of the fields is an imagefield and save as an object in the database. Can't get anything to work... So Im back where im started and my simple question...