help creating a macro

A

ale700

i need a macro that will be able to delete certain rows (remove them
completely, and, i.e. lets say theyre rows 1,2,3, then row 4 would
become row one), then save this workbook to the same location where it
was opened from, except with a slightly different filename (i.e.
include the words isolated at the end). now its prety simple to do,
the difficult part is that i need to do this for multiple files, from
multiple locations, the only good thing is that its always the same
rows that need to be deleted (or removed, which word you prefer): 1-19,
and row 2068. can anyone please help me??!!
 
T

Tom Ogilvy

in another workbook, list all your workbooks with their path

then you can loop through this list

Dim rng as Range, c as Range, bk as workbook
set rng = Range(Cells(1,1),cells(rows.count,1).End(xlup))
for each c in rng
set bk = workbooks.Open(c.Value)
bk.Worksheets(1).Rows(2068).Delete
bk.Worksheets(1).Range(1:19).EntireRow.Delete
bk.Save Application.Substitute(c.Value,".xls","isolated.xls")
bk.Close Savechanges:=False
Next
 
A

Anson

It should be something like this. I haven't tested it:

Sub afjsdlfjasdkljf()

Dim iFile as integer
Dim FilePivot as range
Dim CurrFile as workbook

'Assuming your file paths are in Sheet1 starting from Cell A2, heading A1
Set FilePivot = Thisworkbook.Sheets("Sheet1").[A1]

'Start from row 2 e.g. A2
iFile = 1
'Run through each file path
Do while isempty(FilePivot.offset(iFile,0))=False

'Open file
Set CurrFile = workbook.open(Filepivot.offset(ifile,0))

'Delete rows
currfile.Sheet(????Name of the sheet????).rows("1:19").delete
currfile.Sheet(????Name of the sheet????).rows("2049").delete

'Save and close
CurrFile.save
Currfile.close

ifile=ifile+1
Loop

set currfile=nothing

End sub
 
Top