You should change your testing sub to a function.
A function can return a value (LastRow)...
'---
Function testing123() As Long
Dim LastRow As Long
With Worksheets("4050CC30001")
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
End With
testing123 = LastRow
End Function
'---
To use it, assign the functions return value to a variable...
x = testing123()
Cells(x, 47).Value = "Sludge"
-OR-
Use it directly in your code...
Cells(testing123(), 47).Value = "Sludge"
'---
You may want to take a look at my universal last row function.
Download the workbook/code from...
http://blog.contextures.com/archives...ith-excel-vba/
'---
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
(free and commercial excel programs)
"Rowland Hamilton"
wrote in message
...
Folks:
I finally got my find last row formula to work but I can't seem to get it to work inside my macro
with other variables.
Note: My previous copy data was contiguous 1 region but now I need to copy non-contiguous copy
data with the parameters in this code (B1:M & lastrow)
Also, I want to use an array for worksheets so that its easier to add and remove cost centers.
Finally, if I can get this licked, I'll need a separate macro with a formula for first row, since
I have another set of data that resides below a table I don't need.
Thank you - Rowland
This worked:
START CODE:
Sub testing123()
Dim LastRow As Long
With Worksheets("4050CC30001")
LastRow = Worksheets("4050CC30001").Cells(Rows.Count, "B").End(xlUp).Row
.Range(Cells(1, 2), Cells(LastRow, 13)).Select
End With
End Sub
END CODE
Now how do I get that to work within this:
START CODE:
'Completed Code ? Sample CC
Option Explicit
Sub Populate_line_item_Workbook_Browser_Method()
Dim MasterWB As Workbook
Dim SourceWB As Workbook
Dim rngSrc As Range
Dim rngDst As Range
Dim ws As Worksheet
Dim varFileName As Variant
Dim I As Long
Dim myArr As Variant
Dim LastRow As Long
Set MasterWB = Workbooks("Line items-Combined16.xlsm")
''''''''''Clear MasterWB'''''''''''''''''''''''''''''''''''''''''' '''''''''''
MasterWB.Sheets("Master-Incoming").Activate
Rows("3:3").Select
Range("E3").Activate
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.ClearContents
Range("a1").Activate
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''
?"4050CC30001", "301AA1234", "50BB9999" and "65961LL3201"
myArr = Array("4050CC30001", "301AA1234", "50BB9999", "65961LL3201")
'For I = LBound(myArr) To UBound(myArr) ? Don?t know how to make array work
varFileName = Application.GetOpenFilename(, , "Please select source workbook:")
If TypeName(varFileName) = "String" Then
Set SourceWB = Workbooks.Open(Filename:=varFileName, UpdateLinks:=0)
For Each ws In SourceWB.Worksheets
If ws.Name Like "4050CC30001" And ws.Visible < xlSheetHidden Or _
ws.Name Like "301AA1234" And ws.Visible < xlSheetHidden Or _
ws.Name Like "50BB9999" And ws.Visible < xlSheetHidden Or _
ws.Name Like "65961LL3201" And ws.Visible < xlSheetHidden Then
'Expand Column groups, Collapse Row groups ? need to hide lower table
ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=2
'copy
?This works for contiguous data region, but my new data is not contiguous:
?Set rngSrc = ws.Range("A3").CurrentRegion.Offset(1, 0)
LastRow = ws.Cells(Rows.Count, "B").End(xlUp).Row
?I can?t get this to work:
Set rngSrc = ws.Range(Cells(1, 2), Cells(LastRow, 13))
'paste
Set rngDst = MasterWB.Sheets("Master-Incoming").Range("A" &
Rows.Count).End(xlUp).Offset(1)
rngSrc.Copy
rngDst.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If
Next ws
SourceWB.Close False
?On contiguous data, got error message when it ran out of new sheets so I _
added MsgBoxes:
MsgBox "Copied all data from source workbook"
Else
MsgBox "No file selected"
End If
Application.Goto MasterWB.Worksheets("Master-Incoming").Range("A1"), True
End Sub
END CODE