Copy Data Range From 1 Workbook To Another

xlite · Oct 4th 2006

Re: Copy Data Range From 1 Workbook To Another

hi tqv1969,

you can add a clean up code after each wkbk is opened:

Code

Workbooks.Open FileName:=myPath & x(i) 'open each source file
cleanUp

here's a sample code, you can customize it further:

Code

Sub cleanUp()
Sheets("Sheet2").Activate
Cells.UnMerge 'unmerge all cells
    For Each cell In Range("A1", Range("A65536").End(xlUp))
        Select Case cell = "SELECTION CRITERIA:"
            Case True
            cell.EntireRow.Clear
        End Select
    Next
End Sub

hth

mutant · Oct 6th 2006

Re: Copy Data Range From 1 Workbook To Another

Xlite,

I've 2 problems using the codes to copy the data range from the actual source workbooks to destination workbook.

It was working perfectly fine when I used it to copy a data range from various test workbooks to the destination workbook which I had created for the testing purpose.

Problem 1:

When I ran the macro on the actual workbooks, I got an error message "Subscript out of range". I don't understand why the outcome should be any different from running it on test workbooks.

Problem 2:

The data range to be copied from each workbook is different. I just realised that the cells to be copied are not necessarily saved in B8:R8 in each workbook. They may be saved in different row but the same cols. How do I set a condition for the lookup?

Eg. Copy Br:Rr only when Ar contains the word UK; r stands for row number.

Can this be implemented?

Would you please have another look at the codes and help me with the 2 problems I have?

Thanks very much.

Cheers,
Nadia

xlite · Oct 7th 2006

Re: Copy Data Range From 1 Workbook To Another

hi Nadia,

i've tackled yr 2nd problem, try this code and see if yr 1st problem still exists:

Code

Sub UpdateSales()
    Dim p As String, x As Variant, myPath As String
    myPath = "C:\Documents and Settings\project\My Documents\ozgrid\"
    p = myPath & "*.xls"
    x = GetFileList(p)
     
    Select Case IsArray(x)
    Case True 'files found
        For i = LBound(x) To UBound(x)
            Select Case x(i) = ThisWorkbook.Name 'exclude the output file
            Case True
                 'do nothing
            Case False
                Application.ScreenUpdating = False
                Workbooks.Open FileName:=myPath & x(i)
                
                copyMe 'run copyMe code
'                ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).Offset(1, 0) = Application.WorksheetFunction.Substitute(x(i), ".xls", "")
'                Workbooks(x(i)).Sheets("Sheet1").Range("B8:R8").Copy ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).Offset(0, 1)
                Application.DisplayAlerts = False
                Workbooks(x(i)).Close
                Application.DisplayAlerts = True
            End Select
            Application.ScreenUpdating = True
        Next i
    Case False 'no files found
        MsgBox "No matching files"
    End Select
End Sub


Sub copyMe()
Dim myUK As Range, cell As Range
Dim destRng As Range


Set myUK = ActiveWorkbook.Sheets(1).Range("A1", Range("A65536").End(xlUp))


For Each cell In myUK
Set destRng = ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).Offset(1, 0)


    Select Case UCase(cell) = "UK" 'uCase ignores capital letters
        Case True
        Range(cell.Offset(0, 1), cell.Offset(0, 17)).Copy destRng
    End Select


Set destRng = Nothing
Next


End Sub

Display More

hope this helps

mutant · Oct 7th 2006

Re: Copy Data Range From 1 Workbook To Another

Xlite,

Thanks for the codes. I will try it out & let u know of the progress when I go back to work next week.

Just another question.. What's the likelihood of picking up vba yourself?

Anyway, have a good weekend.

Cheers,
Nadia

xlite · Oct 7th 2006

Re: Copy Data Range From 1 Workbook To Another

Quote from mutant

Xlite,
... What's the likelihood of picking up vba yourself?

hi Mutant,
if you noticed my join date, its Sept 2003, abt 3 years ago.
i started asking questions a lot then coz i had 0.001 percent knowledge of VBA and maybe just 5% knowledge of formulas.
abt the same time i bought a VBA for Dummies book.

i read the book and whenever in doubt i'll keep asking questions here in Ozgrid. After a year i can do without the Dummies book, so i bought a VBA for developers book.
learning both from browsing Ozgrid posts and the book, i began helping to answer questions posted here after abt 18mths(since join date).

by answering questions i realised that i still have a lot to learn, but in the process i get to learn more than when i was asking questions

now i'm quite comfortable with vba, although i don't think i can write a single chapter on it :p
but the main satisfaction comes when i'm applying the skills i learn here to my daily work, its exciting to see them work flawlessly (almost!)

anyway, if you are really keen to learn vba:
keep browsing Ozgrid and get yrself a Dummy : D

mutant · Oct 13th 2006

Re: Copy Data Range From 1 Workbook To Another

Xlite,

Thanks for your reply & advice. I'll try to get a copy of the dummies. Anyway, the codes didn't work for my actual workbooks. :crying: Do you mind to help me test the codes on the actual workbooks please?

I have attached a few sample workbooks here; have a go & let me know.

Thanks very much.

Cheers,
Nadia

xlite · Oct 13th 2006

Re: Copy Data Range From 1 Workbook To Another

hi Nadia,

my codes didn't work b'coz i assumed that there were no blanks in yr table.
i've since changed the code to include the cell with "UK" and then overwrite that with the date in cell A9.

Code

Sub copyMe()
    Dim myUK As Range, cell As Range
    Dim destRng As Range, myDate As Range
     
    Set myUK = ActiveWorkbook.Sheets(1).Range("A1", Range("A65536").End(xlUp))
    Set myDate = ActiveWorkbook.Sheets(1).Range("A9")
     
    For Each cell In myUK
        Set destRng = ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).Offset(1, 0)
         
        Select Case UCase(cell) = "UK" 'uCase ignores capital letters
        Case True
            Range(cell, cell.Offset(0, 17)).Copy destRng
            destRng.Value = myDate.Value
            destRng.NumberFormat = "dd/mm/yyyy"
        End Select
         
        Set destRng = Nothing
    Next
     
End Sub

Display More

have a look at the attached sample

hope this helps

mutant · Oct 14th 2006

Re: Copy Data Range From 1 Workbook To Another

Xlite,

Many thanks to u, again! Will look at it again next week. Also, will try to understand what each line of the codes means so that I can learn from you. :cool:

Have a great weekend. :music:

Cheers,
Nadia

mutant · Nov 10th 2006

Re: Copy Data Range From 1 Workbook To Another

Xlite,

Sorry that I didn't get to thank you earlier for your help. I was away from work for the past 3 weeks. The code works perfectly!

Thanks very much.

Cheers,
Nadia

Copy Data Range From 1 Workbook To Another

Participate now!

Share