copy and paste data from one workbook to another.

[excel_rooki]

hi all,

I have a macro that will send multiple emails to my suppliers as an attachment and the attachment only includes information that relates to them. I am wondering if someone could tweak it so it will pull data from another workbook and paste it into the main worksheet before the macro sends the worksheet.

i would like the macro to pull specific columns from workbook "a" worksheet "a" and input it into my main workbook "b" worksheet "b" then send the worksheet "b" then send the emails with the attachments.

the columns i need from worksheet "a", starting from row 2 are the following, C,H,D,E,J,L,M,V,W,N,O,P,X,Y and i would like them to go in worksheet "b" starting from row2 A,B,C,D,E,F,G,H,I,J,K,L,M,N. the macro should not take any data from row 1 because the header is in that row.

i would also need all blank cells in column M in worksheet "b" filled in with "reconfirm delivery date" and any blank cells in column N in worksheet "b" with the data that is in column J from the specific row.

here is my current Macro

Please Login or Register  to view this content.

a.xlsxb.xlsm

[dflak]

I did this exact thing on my last job. Please include a sample workbook with the source data. Don't include proprietary data but try to include 2 or three vendors.

Here are some tips on how to de-sensitize data:
https://www.excelforum.com/tips-and-...tize-data.html

[excel_rooki]

hi Dflak,

thanks for taking the time to help.

I have attached 2 workbooks, one being the workbook where the data is, and the other workbook is where I want the transfer to go.

please note, the macro i uploaded is to send the worksheet to specific supplier, its has nothing to do with the data transferring.

sample a.xlsxsample b.xlsx

[beyond Excel]

Hello.
Download the two workbooks from the 'zip' and open both workbooks.

Click on the shape and check if what was copied by the macro is what you were looking for.

Sub transfer_from_A() 
Dim wb As Workbook, mCols, i%, ws As Worksheet 
Dim Rng As Range, a, Q& 
'----------------------------\ 
Set wb = Workbooks("A.xlsx") 
mCols = "A,H,D,E,J,L,M,V,W,N,O,P,X,Y" 
'----------------------------/ 
Application.ScreenUpdating = False 
Set Rng = wb.Sheets(1).Cells(1).CurrentRegion 
Set Rng = Rng.Offset(1).Resize(Rng.Rows.Count - 1): Q = Rng.Rows.Count 
 
Set ws = ThisWorkbook.Sheets(1) 
ws.Cells(1).CurrentRegion.Offset(1).Delete xlShiftUp 
 
mCols = Split(mCols, ",") 
For i = 0 To UBound(mCols) 
  mCols(i) = Columns(mCols(i)).Column 
Next 
 
With ws.Range("A2").Resize(Q, 1 + UBound(mCols)) 
  .Value = Application.Index(Rng, Evaluate("Row(1:" & Q & ")"), mCols) 
  Application.Intersect(.Cells, ws.Range("G:G,J:K,N:N")).NumberFormat = "m/d/yyyy" 
End With 
End Sub  

[beyond Excel]

As I don't understand what you want to do in the M and N columns, re-upload b.xlsm with those columns completed 'by hand', please.

[excel_rooki]

hi Beyond,

yes that is what i wanted to a degree. regarding M & N (sample b), in every empty cell in column M, i would like "please reconfirm the due date" pasted in there and in every empty cell in column N, i would like the data (due date) from the sample a column N pasted in there.

also A,H,D,E,J,L,M,V,W,N,O,P,X,Y is an error, it should be C,H,D,E,J,L,M,V,W,N,O,P,X,Y

for some reason workbook sample a was incorrect, i have now uploaded the correct one.sample b.xlsxsample a.xlsx

[beyond Excel]

Explain the differences you have between the workbooks in post #1 and the workbooks in post #6.

[excel_rooki]

there is no difference now. i edited post #6 and re-added the workbooks, for some reason not all the columns were in them .

let me explain a bit better now i have time.

as well as transferring columns C,H,D,E,J,L,M,V,W,N,O,P,X,Y to columns A,B,C,D,E,F,G,H,I,J,K,L,M,N,

because there are empty cells in columns x & y in workbook sample a, not every cell will be filled in column m & n in workbook sample b.

so, what i would like the macro to also do is, fill in the empty cells in column m in workbook sample b with " please re-confirm the due date"
and in column n (workbook b) i would like the macro to paste the data from column n from sample a into the empty cells.
the macro will be putting column n in workbook sample a into the empty cells in column n in sample b as well as column j.

i hope that is clearer for you.

sample a.xlsxsample b.xlsx

[dflak]

I played a bit more with your source data to make it a bit more "diverse." I gave this file back to you as an attachment so you can see what data I used.

The Control Panel sheet is used to configure the data. Columns F and G contain the Supplier Name and the email address for the supplier. Use semicolons to separate addressees. Columns I and J are used to define the Directories for where the data file is downloaded, where you want to save a copy of this data as an archive and where you want the reports to be stored. Cell J4 is the name of the file with the data download. Since the program makes an archive, you may want to download to the same file name every time so you don't have to change it.

Column D is a pivot table of the suppliers that are in the database. It is updated automatically, so don't do anything with it.

Cell B1 is today's date. Cell B2 is an override date in case you want to run the report with a different date stamp. Cell B3 is the date the report uses. If cell B2 is blank, then it's today's date.

Click on Read Date. This action imports the data into the Raw Data Tab.

Click on Make Report. This action uses a pivot table to organize the data and then copies it into the Report Sheet which is then saved as a separate file under the supplier name with a date stamp.

The program then goes on to mail the report.

The Mail setup tab is used to define parts for the mail program. The TO field is looked up from the table on the control panel sheet. You can use formulas to generate the subject or even the body.

The Mail Flag is either true or false. With True, the mail goes out automatically. With False, a draft copy is made and left up on the monitor.

I think this program is most of the way there. You may want the interrupt the process between making the report and mailing it to give you the opportunity to modify the generated reports. Let me know if you want to do this. I will have to give you a mail report button to make it a three-step process.

[excel_rooki]

oh wow, thanks for that!! i will have a play and get my head around it and get back to you in due course! fyi, there will be over 2000 rows so i hope it will be ok.

[excel_rooki]

ok, i think i got the hang of it but im stuck at "read data". i added the path to my file in Path To Data and added my file name (lets call it 160123.xlsx) in the Data File Name so it can pull all the information into the raw data tab and when i press read data, i get an error message saying "the source file 160123.xlsx does not exist"

also there is no data column n that needed to be pulled from column y

achive data file works fine

make report works fine, i added my email address in to see if it works, and it does. (apart from having the missing data in column "n" in the attached workbook).

if you could break down the explanation a bit more or tell me where i have gone wrong, that would be great.