• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Append cell data from two tabs to another workbook macro

DSM2019

New Member
I have a script that takes data from certain cells and writes them to a repository file. I have been asked to add additional cells from a second tab is the source workbook and I'm not quite sure how to accomplish with what I have. I've tried some things but end up destroying my working script. Any help is greatly appreciated. My working script is below:

Option Explicit
Sub UpdateLogWorksheet()
Dim historyWkb As Workbook
Dim historyWks As Worksheet
Dim myfilename As String
'destination file location
myfilename = "http://starpoint.xxx.com/sites/itprojects/PMOOperations/PMO Resource Reporting\Repository.xlsx"
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myRng As Range
Dim myCopy As String
Dim myCell As Range

'set cells to copy from input sheet - some contain formulas
myCopy = "B4,B5,B6,B7,D4,D5,D6,B10,A14,A18,A22,A26,A31,A35,B38,B39,B40,B41,B42,D38,D39,D41,B46,B47,B48,D46,D47,D48,A52"
'set source sheet name
Set inputWks = Worksheets("Planned Business Value Form")
Set historyWkb = Workbooks.Open(myfilename)
'set destination sheet name
Set historyWks = Worksheets("Sheet1")

With historyWks
nextRow = .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0).Row
End With
With inputWks
Set myRng = .Range(myCopy)
End With
'Add timestamp and user column entries
With historyWks
With .Cells(nextRow, "A")
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
.Cells(nextRow, "B").Value = Application.UserName
oCol = 3
For Each myCell In myRng.Cells
historyWks.Cells(nextRow, oCol).Value = myCell.Value
oCol = oCol + 1
Next myCell
'save and close destination file
historyWkb.Close True
End With
End Sub
 
Top