Solved: Copy and Paste from several workbooks to master workbook

[almouchie]

the problem i am having now is
I want to copy data from several invoices (found in workbooks on diectory) to the main database
I used record macro to get started but how can I
amend the macro to have it do the same (copy fields) for each invoice & paste it in the proper row in the database
i would like to do this for every invoice
i want it to insert the data in the appropriate row so as to have an updated data

I have made a template for the invoice so the accountant can use & its meant to help me with the macros.
everytime there is an nivoice he will use the template & add the data then save it on the directory
I have my master database & want to update the data from several invoices to my master sheet

where can I start
thanks for any help

[OBP]

It sounds like you are using Excel to perform an Access function.
As long as you know the names of the workbooks the VB is fairly simple, you just need a for/next or for/each loop to copy the data and then increment the row number and paste in the data. Look in the Excel VB Editor's help for info on for/next loops and also
activecell.offset.
If you can't get it working come back on here for further assistance.

[mdmackillop]

Hi
Welcome to VBAX
If you can post an example, it makes it easier to assist. Please remove any sensitive data though. You can post a workbook or zip file using Manage Attachments accessed from the Go Advanced button.
Regards
MD

[almouchie]

thanks for ur reply

thats what my recorded macro looks like
& i have attached a workbook as an example.
the preinvoice is the the shet I want to extract data from & paste it in the database workbook.
the preinvoice is the sheet in a workbook found in a folder
i want to update the info from each invoice workbook to the database
I have many invoices (workbooks) & I want to paste the data in the database each on a row


in simple terms I want to do the same code for all & any workbook instead of just the one written

ChDir "M:\GR\DECEMBER"
Workbooks.Open Filename:="M:\GR\DECEMBER\INVOICES-635.xls"
Windows("database.xls").Activate
Sheets("master").Select
Range("A3").Select
ActiveCell.FormulaR1C1 = "='[INVOICES-635.xls]pre invoice'!R1C7"
Range("B3").Select
ActiveCell.FormulaR1C1 = "='[INVOICES-635.xls]pre invoice'!R1C2"
Range("C3").Select
ActiveCell.FormulaR1C1 = "='[INVOICES-635.xls]pre invoice'!R3C5"
Range("D3").Select
ActiveCell.FormulaR1C1 = "='[INVOICES-635.xls]pre invoice'!R2C2"
Range("E3").Select
ActiveCell.FormulaR1C1 = "='[INVOICES-635.xls]pre invoice'!R3C2"
Windows("INVOICES-635.xls").Activate
Windows("database.xls").Activate
ActiveCell.FormulaR1C1 = "='[INVOICES-635.xls]pre invoice'!R28C5"
Range("AY3").Select
ActiveCell.FormulaR1C1 = "='[INVOICES-635.xls]pre invoice'!R29C4"
Columns("F:F").Select
Range("F2").Activate
Selection.NumberFormat = "[$-409]d-mmm-yy;@"
Columns("A:A").Select
Range("A2").Activate
Selection.NumberFormat = "[$-409]mmm-yy;@"

Sheets("preinvoice").Select
Range("L2").Select
ActiveCell.FormulaR1C1 = "='[INVOICES-635.xls]pre invoice'!R13C12"
Range("M2").Select
ActiveCell.FormulaR1C1 = "='[INVOICES-635.xls]pre invoice'!R13C13"
Range("M3").Select
Range("C2").Select
ActiveCell.FormulaR1C1 = "='[INVOICES-635.xls]pre invoice'!R1C7"
Range("D2").Select
ActiveCell.FormulaR1C1 = "='[INVOICES-635.xls]pre invoice'!R1C2"
Columns("C:C").Select
Selection.NumberFormat = "[$-409]mmm-yy;@"
Range("H2").Select
ActiveCell.FormulaR1C1 = "='[INVOICES-635.xls]pre invoice'!R2C2"
Range("I2").Select
ActiveCell.FormulaR1C1 = "='[INVOICES-635.xls]pre invoice'!R3C2"
Range("J2").Select
ActiveCell.FormulaR1C1 = "='[INVOICES-635.xls]pre invoice'!R3C5"
Range("K2").Select
ActiveCell.FormulaR1C1 = "='[INVOICES-635.xls]pre invoice'!R1C7"
Range("N2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-7]>0,DAYS360(RC[-3],RC[-7]),"""")"
Range("O2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-8]>0,NETWORKDAYS(RC[-4],RC[-8]),"""")"
Range("V2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-4]=RC[-2],""Correct"","" Difference in Amounts"")"
Range("K2").Select
Selection.NumberFormat = "[$-409]d-mmm-yy;@"
Range("G2").Select
Selection.NumberFormat = "[$-409]d-mmm-yy;@"

End Sub

[mdmackillop]

Hi,
The following is an outline approach. I've not tried to pick up your specific data references, but the code is easily adjusted to suit.
The target columns (TgtCol) are those where yoiu wish the results to appear.
Source are the cell references on each sheet from which the data will be collected.
I've assumed the sheet reference numbers are written in Column A of the Database sheet. These are used to open the relevant workbooks for collecting the data, and allow for the data to be written to the corresponding columns. A loop can be added to further automate the procedure.
This solution will enter data values rather than formulae linking to each of your invoice worksheets.
If this doesn't suit your purpose, or you need further assistance, please let us know.
Regards
MD

Sub GetData()
Dim TgtCol
Dim Source
Dim Data
TgtCol = Array(2, 3, 5, 7, 9)
Source = Array("B1", "B2", "B3", "D7", "E7")
Workbooks.Open Filename:="C:\AAA\" & ActiveCell & ".xls"
Data = Array(Range(Source(0)), Range(Source(1)), Range(Source(2)), _
Range(Source(3)), Range(Source(4)))
Windows("database.xls").Activate
Sheets("database").Activate
For i = 0 To UBound(TgtCol)
Cells(ActiveCell.Row, TgtCol(i)) = Data(i)
Next
End Sub

[almouchie]

thanks for ur reply MD

i tried to make sense of the code
& used help thou it wasnt much help
I need some help if its possible

I create a workbook & wrote random data in column B from cell B1 to B5
then adjusted the target column from 2 to 5
while selecting the cell A that will triggers opening the workbook
but it seems to give me blank

TgtCol = Array(2, 3, 4, 5, 6)
Source = Array("B1", "B2", "B3", "b4", "b5")

what exactly does this mean
Data = Array(Range(Source(0)), Range(Source(1)), Range(Source(2)), Range(Source(3)), Range(Source(4)))


about the loop
in simple terms how would I apply looping to my in the code

thanks

[mdmackillop]

Here's a sample. Extract all the files into C:\AAA\. Open CollectData and run the macro

[almouchie]

thanks for ur reply
I am trying to change the code to fit my fields
I have a question thou suppose I want to make a formula for a cell
how can this be intergrated in the code
how can i put a formula in my source area where I want cell E7 *12 or whatever forula I want
Is this feasible or not
thanks again

[mdmackillop]

You can write a formula into a cell using code.
eg Range("A1").Formula = "=E7*12"

[almouchie]

another question related
how can i have the formula in the code auto fill untill it finds a the cell in B for instance blankSelection.AutoFill Destination:=Range("M3:M13"), Type:=xlFillDefault

i dont want to name the range but rather to fill down as long cell B is not blank
thanks

[mdmackillop]

This will fill down in the column 11 cells to the right of column B from B3 to the first empty cell below it. Please note that it will not behave "correctly" if B4 is blank.

Sub FillOffset()
Range([B3], [B3].End(xlDown)).Offset(, 11).FillDown
End Sub

[almouchie]

thanks it worked out well

[almouchie]

now my question would be related to the code u so kindly provided
(targetcell & source)

if there a way to speed up the code especially when it has to fill many cells & from many workbooks

[austenr]

Try turning off calculations.

Application.Calculation = xlCalculationManual

at the beginning of the sub and then

Application.Calculation = xlCalculationAutomatic

at the end

[mdmackillop]

Also
Application.Screenupdating = False

[austenr]

OOPS...:banghead:

[almouchie]

i have that in the code
Application.Screenupdating = False at the beginning & True after the loop

thou it doesnt seem to speed it up much
& i am still dealing with a handful of workbooks (3 to 4 only)

[mdmackillop]

You could look at retreiving data from a closed workbook, which may be quicker. Here's one method, but there are others.
http://www.excelkb.com/article.aspx?id=10176

[almouchie]

THANKS for your replies
I am having a look at the code (link provided) trying to make sense of it

[almouchie]

i have a question about the code u provided a while ago

what can I do so the code will only fill in/update new/empty rows
that is if I want to copy the data from the workbooks that havent been updated & not for the old ones too
for now the workbooks are not many but later they will & i dont want it to update from the start everytime
am I clear in my question
thanks for ur help

[johnske]

You could look at retreiving data from a closed workbook, which may be quicker. Here's one method, but there are others.
http://www.excelkb.com/article.aspx?id=10176 Here's another, but note that although these methods can be very quick when small amounts of data are being obtained, they are very slow when large amounts of data or large ranges are involvedOption Explicit
Sub ExtractDataFromClosedBook()
Application.ScreenUpdating = False

'first we'll create links to extract the data from the closed book
'put your own path and ranges below
Sheets("Sheet1").Range("A1:H400") = _
"='" & ActiveWorkbook.Path & "\[SourceBook.xls]Sheet1'!A1:H400"

'now copy-paste to remove all the links (but retain the values)
With Sheets("Sheet1").Range("A1:H400")
.Copy
.PasteSpecial xlPasteValues
.Columns.AutoFit
End With
With Application
.Goto Range("A1"), True
.CutCopyMode = False
.ScreenUpdating = True
End With

End Sub