Rowland Hamilton
Active Member
- Joined
- Nov 13, 2009
- Messages
- 250
Folks:
Nories code worked great for me when my data was contiguous and
I finally got my adjusted 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. (2nd priority to first problem)
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. (3rd priority, if lastrow works firstrow should work)
Thank you - Rowland
This worked:
START CODE:
END CODE
Now how do I get that to work within this:
START CODE:
END CODE
Key words: Last Row, VBA, Array, Dim, File browser, Copy/Paste between workbooks
Subject: Find Last Row use in VBA code with array, File browser and Copy/Paste between workbooks
Nories code worked great for me when my data was contiguous and
I finally got my adjusted 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. (2nd priority to first problem)
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. (3rd priority, if lastrow works firstrow should work)
Thank you - Rowland
This worked:
START CODE:
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 SubNow how do I get that to work within this:
START CODE:
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 SubKey words: Last Row, VBA, Array, Dim, File browser, Copy/Paste between workbooks
Subject: Find Last Row use in VBA code with array, File browser and Copy/Paste between workbooks
