Copying excel cell data

[SjrH]

I have used DigDB to combine 5000+ excel files into one new file, but I now need to extract data from a single cell and paste it onto a row in the new file.

The cell (say A2) is on a worksheet called summary and I have recorded a macro to copy it and paste it into the new file, but the problem is that when DigDB combined the files (which all share the same structure), it named the sheets "summary (2)" "summary (3)" and so on...

How would I make the macro automatically increment the sheet number so it would copy A2 of summary (2), then summary (3) etc.... whilst pasting each value into a new row in the new file?

Thanks

 

[PHV]

Use a numeric sheet number instead of a sheet name.
An another way is the For Each instruction.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SjrH]

Thanks.

The simple copy and paste i'm using at the moment is

 Sheets("Summary").Select
    Range("F2").Select
    Selection.Copy
    Windows("Book7").Activate
    ActiveSheet.Paste

What I would then like to be able to do is create a loop so that the next sheet selected is

 Sheets("Summary (2)").Select

Sorry, I know nothing about VB! I've been toying with FOR_NEXT loop, but to no avail.