Hi guys, i'm not amazing with VBA but can take stuff and change it to suit my requirements, although this code isn't finding the next empty row, it's just pasting data on top of data, so instead of getting 20 rows of data, i'm just getting 1 and it's really infuriating!!
I have a multitude of finance workbooks that each have a cover sheet, I am trying to obtain all the data in these cover sheets and then paste into headers, so I can produce a monthly automated finance report - this is the code I am using and like I said, it's not finding the nect empty row to paste into:
Sub copyNonAdjacentCellData()
Dim myFile As String, path As String
Dim erow As Long, col As Long
path = "P:\Finance Trackers"
myFile = Dir(path & "*.xlsx")
Application.ScreenUpdating = False
Do While myFile <> ""
Workbooks.Open (path & myFile)
Windows(myFile).Activate
Set copyrange = Sheets("MONTHLY REPORT").Range("N7,E9,E5,E7,W5,W7,W9,E11,N9,D5,E15,E17,E19,E21,N17,N19,N21,X21,N26,X26")
Windows("Finance_Tracker_Aggregator.xlsm").Activate
erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
col = 1
For Each cel In copyrange
cel.Copy
Cells(erow, col).PasteSpecial xlPasteValues
col = col + 1
Next
Windows(myFile).Close savechanges:=False
myFile = Dir()
Loop
Range("A:T").EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub
Really hoping that someone can help!!!
I have a multitude of finance workbooks that each have a cover sheet, I am trying to obtain all the data in these cover sheets and then paste into headers, so I can produce a monthly automated finance report - this is the code I am using and like I said, it's not finding the nect empty row to paste into:
Sub copyNonAdjacentCellData()
Dim myFile As String, path As String
Dim erow As Long, col As Long
path = "P:\Finance Trackers"
myFile = Dir(path & "*.xlsx")
Application.ScreenUpdating = False
Do While myFile <> ""
Workbooks.Open (path & myFile)
Windows(myFile).Activate
Set copyrange = Sheets("MONTHLY REPORT").Range("N7,E9,E5,E7,W5,W7,W9,E11,N9,D5,E15,E17,E19,E21,N17,N19,N21,X21,N26,X26")
Windows("Finance_Tracker_Aggregator.xlsm").Activate
erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
col = 1
For Each cel In copyrange
cel.Copy
Cells(erow, col).PasteSpecial xlPasteValues
col = col + 1
Next
Windows(myFile).Close savechanges:=False
myFile = Dir()
Loop
Range("A:T").EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub
Really hoping that someone can help!!!
