Solved

VBA to copy paste columns form one file to other

Posted on 2016-09-20
20
79 Views
Last Modified: 2016-10-02
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
Comment
Question by:Nirvana
  • 8
  • 7
  • 3
  • +1
20 Comments
 
LVL 34

Expert Comment

by:PatHartman
ID: 41809529
Why wouldn't you use a query to select the records you want and append them to the "template"?
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41809559
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
 

Author Comment

by:Nirvana
ID: 41810140
@PatHartman: Sorry not sorry how do i run a Query
0
 

Author Comment

by:Nirvana
ID: 41810219
@aikmark: its an excel file that i am running attaching the input and output files
Output-file-_555330735.xlsx
Input-File.xlsx
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41810779
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
 

Author Comment

by:Nirvana
ID: 41811017
@PatHartman: I am not using Access at all. it is all in excel
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41811022
OK, then you are stuck writing code.  One of the Excel experts will help.
0
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41814437
Are you trying to split the data into multiple workbooks?
0
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41814616
Are the Input Files empty to start with?
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:Nirvana
ID: 41814622
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
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41814625
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
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41814711
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
 

Author Comment

by:Nirvana
ID: 41814799
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
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41814803
Sorry add a colon after exit_proc, I must have deleted it when I tidied up

exit_proc:

Open in new window

0
 

Author Comment

by:Nirvana
ID: 41814828
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
 
LVL 17

Accepted Solution

by:
Roy_Cox earned 500 total points
ID: 41814848
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
 

Author Closing Comment

by:Nirvana
ID: 41815085
Brilliant
0
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41815379
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
 

Author Comment

by:Nirvana
ID: 41823391
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now