Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-19-2023, 01:31 PM
Alex1s85 Alex1s85 is offline VBA code to format table, insert 3 columns right, do a vlookup and split table in separate workbooks Windows 10 VBA code to format table, insert 3 columns right, do a vlookup and split table in separate workbooks Office 2016
Advanced Beginner
VBA code to format table, insert 3 columns right, do a vlookup and split table in separate workbooks
 
Join Date: Oct 2019
Location: Thessaloniki, Greece
Posts: 30
Alex1s85 is on a distinguished road
Default VBA code to format table, insert 3 columns right, do a vlookup and split table in separate workbooks

Hey all!


I have a table which contains data from xml files.
This table has 12 columns.



I'm trying to create a vba code which will do the following:

1. delete the first column table.
2. delete blank rows.
3. add a column next to column 3 in order to format data from column 3 as =TEXT([@column3],"000000000").
4. delete column 3.
5. Rename (new) column 3 as "ApplicantVatNumber".
6. Insert 3 columns right named as "Skroutz", "Mickey", 'Mini".
7. do a vlookup in all 3 new columns using as key the column 3 and table array from respective sheets in other workbook.
8. Split table in multiple workbooks based on the value in column A. New workbooks have to be named as "Walt_Disney_X.xlsx" where X is the value in column A. These workbooks have to be stored in folders that i will define.
9. Keep the original sheet.


This my code but, aparently, doesn't work:


Sub FormatTable()
'Delete the first column
Columns(1).Delete
'Delete blank rows
Rows.SpecialCells(xlCellTypeBlanks).Delete
'Add column next to column C
Columns(3).Insert
'Format data in new column
Range("D2").Formula = "=TEXT([@column3],""000000000"")"
'Delete column C
Columns(3).Delete
'Rename column D as "ApplicantVatNumber"
Columns(3).Name = "ApplicantVatNumber"
'Insert 3 columns to the right
Columns(4).Insert
Columns(5).Insert
Columns(6).Insert
'Name new columns
Columns(4).Name = "Skroutz"
Columns(5).Name = "Mickey"
Columns(6).Name = "Mini"
'Do vlookup using column C as key
Range("Skroutz2:Skroutz" & Cells(Rows.Count, "C").End(xlUp).Row).Formula = "=iferror(VLOOKUP(C2,'path to xlsx'sheet,2,FALSE),'0'"
Range("Mickey2:Mickey" & Cells(Rows.Count, "C").End(xlUp).Row).Formula = "=iferror(VLOOKUP(C2,'path to xlsx'sheet,2,FALSE),'0'"
Range("Mini2:mini" & Cells(Rows.Count, "C").End(xlUp).Row).Formula = "=iferror(VLOOKUP(C2,'path to xlsx'sheet,2,FALSE),'0'"
'Split table into multiple workbooks
Dim ws As Worksheet
Dim var As Variant
Set ws = ActiveSheet
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
var = ws.Range("A2:F" & lastRow)
Dim i As Integer
For i = 2 To lastRow
If Not IsEmpty(Cells(i, 1)) Then
var(i - 1, 1) = Cells(i, 1).Value
If Not Dir("Walt_Disney_" & Cells(i, 1).Value & ".xlsx") = "" Then
Set newWB = Workbooks.Add
Set newWS = newWB.Sheets(1)
ws.Range("A" & i & ":F" & i).Copy Destination:=newWS.Range("A1")
newWB.SaveAs "Walt_Disney_" & Cells(i, 1).Value & ".xlsx"
newWB.Close
Else
Set newWB = Workbooks.Open("Walt_Disney_" & Cells(i, 1).Value & ".xlsx")
Set newWS = newWB.Sheets(1)
ws.Range("A" & i & ":F" & i).Copy Destination:=newWS.Cells(newWS.Rows.Count, 1).End(xlUp).Offset(1, 0)
newWB.Save
newWB.Close
End If
End If
Next i
End Sub



Any help will be valuable!

Thanks in advance!
Attached Files
File Type: xlsx samplefile.xlsx (9.8 KB, 1 views)
Reply With Quote
  #2  
Old 01-19-2023, 02:41 PM
Alex1s85 Alex1s85 is offline VBA code to format table, insert 3 columns right, do a vlookup and split table in separate workbooks Windows 10 VBA code to format table, insert 3 columns right, do a vlookup and split table in separate workbooks Office 2016
Advanced Beginner
VBA code to format table, insert 3 columns right, do a vlookup and split table in separate workbooks
 
Join Date: Oct 2019
Location: Thessaloniki, Greece
Posts: 30
Alex1s85 is on a distinguished road
Default

Heeeeeeeeeelp
Reply With Quote
  #3  
Old 01-19-2023, 03:15 PM
Guessed's Avatar
Guessed Guessed is offline VBA code to format table, insert 3 columns right, do a vlookup and split table in separate workbooks Windows 10 VBA code to format table, insert 3 columns right, do a vlookup and split table in separate workbooks Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

Is this assignment only to be done with VBA or can you explore Excel's Power Query to do most of the work?

If you aren't already familiar with Power Query you can start with some youtube videos
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #4  
Old 01-19-2023, 03:21 PM
Alex1s85 Alex1s85 is offline VBA code to format table, insert 3 columns right, do a vlookup and split table in separate workbooks Windows 10 VBA code to format table, insert 3 columns right, do a vlookup and split table in separate workbooks Office 2016
Advanced Beginner
VBA code to format table, insert 3 columns right, do a vlookup and split table in separate workbooks
 
Join Date: Oct 2019
Location: Thessaloniki, Greece
Posts: 30
Alex1s85 is on a distinguished road
Default

Yes, i am not familiar with PQ but i will give it a try.

This is a daily task. I 'm getting data from xml and i have to perform all these actions.
I thought that a vba code assigned to a button would be most user friendly.

Thanks for your time!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Split Table in separate rows and turn in picture Format allenku Word VBA 1 08-08-2021 08:29 PM
Split Table in separate rows and turn in graph allenku Word VBA 3 08-04-2021 04:16 AM
VBA code to format table, insert 3 columns right, do a vlookup and split table in separate workbooks Code to find a named (bookmarked?) table, replicate a row or table, and delete a specified table. kevinbradley57 Word VBA 9 09-21-2017 04:58 PM
VBA code to format table, insert 3 columns right, do a vlookup and split table in separate workbooks Format tab leader in “Table of figures” separate from text? WTR_girl12 Word 1 09-03-2015 10:48 AM
VBA code to format table, insert 3 columns right, do a vlookup and split table in separate workbooks Autonumbering clauses in separate table columns bertietheblue Word Tables 4 08-13-2015 07:55 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:25 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft