royUK Thanks for your reply. Because I require column order to be rearranged in specific order.
Input reports are stored in different formats. Before fetching the output ( Default template) I require the data to get delimit and rearrange the column order and update the output.
Attaching the test file shortly.
Good Day,
Require assistance in trimming the below code + borders to be included for the range that consist text.
Sub Update_Data()
Dim iRange As Range
Dim iCells As Range
Application.ScreenUpdating = False
Sheets("Input").Select
Range("B3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Output").Select
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteValues
Sheets("Input").Select
Range("C3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Output").Select
Range("D4").Select
Selection.PasteSpecial Paste:=xlPasteValues
Sheets("Input").Select
Range("D3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Output").Select
Range("J4").Select
Selection.PasteSpecial Paste:=xlPasteValues
Sheets("Input").Select
Range("E3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Output").Select
Range("K4").Select
Selection.PasteSpecial Paste:=xlPasteValues
Sheets("Input").Select
Range("F3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Output").Select
Range("L4").Select
Selection.PasteSpecial Paste:=xlPasteValues
Sheets("Input").Select
Range("N3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Output").Select
Range("G4").Select
Selection.PasteSpecial Paste:=xlPasteValues
Sheets("Input").Select
Range("O3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Output").Select
Range("F4").Select
Selection.PasteSpecial Paste:=xlPasteValues
Sheets("Input").Select
Range("H3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Output").Select
Range("M4").Select
Selection.PasteSpecial Paste:=xlPasteValues
Sheets("Input").Select
Range("I3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Output").Select
Range("N4").Select
Selection.PasteSpecial Paste:=xlPasteValues
Set iRange = ThisWorkbook.ActiveSheet.UsedRange
For Each iCells In iRange
If Not IsEmpty(iCells) Then
iCells.BorderAround _
LineStyle:=xlContinuous, _
Weight:=xlThin
End If
Next iCells
End SubPlease help to trim the code - Anna
vcoolio this code works perfect.. Thanks a lot
could you please explain the code how it works,
Because the required output column order is this(1,2,3,4,5,6,7,8,9,10,11)
In your code its specified as random order "Array(1, 2, 3, 10, 11, 12, 4, 5, 6, 7, 8)" and "column 9" header is missing (in the code).
Additionally, is it possible to delimit and extract the data in output sheet?
For eg..
Input:-
Column 10 has the below data
1-2 Days Australia
13-14 Days way Vietnam
5-6
Required Output:
Col.8
1-2
13-14
5-6
Col.9
Australia
Vietnam
<blank>
Thanks for your reply..
Is it possible to customize the order list? Because in the real data, Column headers will be in string..
Hi All
Beginner in VBA and macros,
Looking for a column lookup to export the values from one table to another table.
Attaching the workbook for better understanding.
- Anna
Bulk means large data or multiple entries which should be replaced.
So bulk find and replace is not possible?
Thanks Roy, So Bulk find and replace is impossible??
Because find and replace dialog box allows us to find 1- replace all but not find multiple and replace at one go..
Because I have lot of data to find and replace. So I thought vba will be the solution to make it easier. Please share your suggestions
Hi,
I have a list of employee names and IDs in A2:A9 and B2:B9 respectively in Input Sheet and Employee names scattered in Base Sheet.
Employee names should be replaced with their ID's using VBA code. It will be very helpful if someone suggestions to bulk find and replace.
Attached sample book.
Thanks