Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 152
  • Last Modified:

VBA to copy paste columns form one file to other

I Receive an Input file with thousands of rows and multiple columns

Below are the steps

1: In the Input file I have columns A:R
2: I have file upload template (standard template desighne to upload in oracle)
3: In the Input file I have to filter by customer number (column D) customer numbers would be repeated
4: For each of the customer Copy the Respective columns and paste to Upload template
5: after filtering by unique customer in input file Copy all the rows of input file Column O to Column B of upload template the same way  Column N to D Column G to E Column P to F Column D to M

Input File (Columns)                   Output File(Columns) from Row 10      
O                                                      B
N                                                      D
G                                                      E
P                                                      F
D                                                      M

6: file save the output file with Customer number (column D of the input file or column M of the output file)
7:  Repeat the same for all customers

Please help creating VBA
0
Nirvana
Asked:
Nirvana
  • 8
  • 7
  • 3
  • +1
1 Solution
 
PatHartmanCommented:
Why wouldn't you use a query to select the records you want and append them to the "template"?
0
 
aikimarkCommented:
Are you running a stand-alone VB Classic application or some MS Office application (Word, Excel, Access, etc.)?

Please post some representative input and output files.
0
 
NirvanamanagerAuthor Commented:
@PatHartman: Sorry not sorry how do i run a Query
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
NirvanamanagerAuthor Commented:
@aikmark: its an excel file that i am running attaching the input and output files
Output-file-_555330735.xlsx
Input-File.xlsx
0
 
PatHartmanCommented:
Nirvana,
The question cannot be answered in a vacuum.    If you are running the code in Access, then you would link to the input spreadsheet.  Create a query that selects the rows/columns you want.  Then export the query back to Excel.  Two lines of code plus a select query which the wizard will help you to build.  If you are doing this in Excel or some other application, you will need more code because you will have to write a loop that performs the function that the query performed in Access and then inside the loop, write each record to the output file as it is selected.
0
 
NirvanamanagerAuthor Commented:
@PatHartman: I am not using Access at all. it is all in excel
0
 
PatHartmanCommented:
OK, then you are stuck writing code.  One of the Excel experts will help.
0
 
Roy CoxGroup Finance ManagerCommented:
Are you trying to split the data into multiple workbooks?
0
 
Roy CoxGroup Finance ManagerCommented:
Are the Input Files empty to start with?
0
 
NirvanamanagerAuthor Commented:
yes i am trying to split the data. from the main data input files will be created based on the column D and from the input files each of the output files will be created
0
 
Roy CoxGroup Finance ManagerCommented:
oK. I don't think that your destination columns are correct. I think

D goes to M, G to E, H to F, N to D and O to B.

It's the container that seems wron
0
 
Roy CoxGroup Finance ManagerCommented:
I think this does what you want.

I have cleaned all data from the OutPut file. Both files should be in the same folder with a sub folder called Import. If it needs any adjustments let me know.

Currently producing 44 new workbooks.
Input-File--1-.xlsm
Output-file-.xlsx
0
 
NirvanamanagerAuthor Commented:
Hey Roy, getting an error as below

"Cannot run the macro ‘Input-File—1-.xIsm’!ExfractToSheets. The macro may not be available in this workbook or all macros may be disabled,"

I have enabled the macros in both the files error.png
and when I debug in the code i am getting the error at line "exit_proc"

do I have to do something different
0
 
Roy CoxGroup Finance ManagerCommented:
Sorry add a colon after exit_proc, I must have deleted it when I tidied up

exit_proc:

Open in new window

0
 
NirvanamanagerAuthor Commented:
This worked brilliant.. just need small help my output file has macro, so when i change the line to

"sPath = ThisWorkbook.Path & Application.PathSeparator & "Output-file-.xlsm"

for every file it prompts do you want to save the file as macro enabled workbooks how do i change that?

otherwise it is just brilliant. thanks again
0
 
Roy CoxGroup Finance ManagerCommented:
Just need s  the file type changing in the saveas code, xlsx becomes xlsm and file type is 52 not 51

'/// saves to a folder named Import in the master workbook's directory
            ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Import\" & "Customer - " & sNm & ".xlsm", 52
            ActiveWorkbook.Close True

Open in new window

0
 
NirvanamanagerAuthor Commented:
Brilliant
0
 
Roy CoxGroup Finance ManagerCommented:
Pleased to help. Do you still need the other question working on, the code that I posted there works but I think this makes it unnecessary.
1
 
NirvanamanagerAuthor Commented:
Hi Roy sorry to come back to I am facing some challenges
1: if i have the data less than 57 rows, in output file i get data from previous files ( which means in any case the data is getting copied till 57 rows)
2: IN column B i have to copy text as "MI" this also gets copied to only to 57 rows
3: changing the output file to xls; the oracle is accepting on xls files. I have changed the type to xls and number to 57

'/// saves to a folder named Import in the master workbook's directory
            ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Import\" & "Customer - " & sNm & ".xls", 57
            ActiveWorkbook.Close True

please help me out
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 8
  • 7
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now