VBA to find next empty row in worksheet isn't working!!!

jimbo1977

New Member
Joined
Oct 15, 2019
Messages
1
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!!!
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,956
Office Version
365
Platform
Windows
Are you looking to paste the values onto the active sheet, or a specific sheet?
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,563
erow is derived from Sheet1, copyrange is on Sheets("MONTHLY REPORT")
Are they the same sheet?
 

Watch MrExcel Video

Forum statistics

Threads
1,099,566
Messages
5,469,446
Members
406,652
Latest member
LJA

This Week's Hot Topics

Top