Posts by jolivanes

jolivanes · 2020-04-02T06:06:38+01:00

If your "Master" Workbook is saved in a different folder and you want to specify the folder where the files you want to copy from, this should work.

Code

Sub Maybe_Like_So()
Dim myPath As String, wb As String
Application.ScreenUpdating = False
myPath = "C:\Folder One\SubFolder One\SubFolder Two" '<---- Change as Required
wb = Dir(myPath & "\*")
Do Until wb = ""
Workbooks.Open myPath & "\" & wb
With Workbooks(wb).Sheets(1)
.UsedRange.Offset(1).Copy ThisWorkbook.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1)
End With
Application.CutCopyMode = False
Workbooks(wb).Close False
wb = Dir
Loop
Application.ScreenUpdating = True
End Sub

Display More

jolivanes · 2020-04-01T23:02:34+01:00

Save your "Master" workbook in the same folder where the workbooks you copy from are located.

Open the "Master" workbook and try this code.

Code

Sub Maybe_Like_So()
Dim wb As String
Application.ScreenUpdating = False
wb = Dir(ThisWorkbook.Path & "\*")
Do Until wb = ""
If wb <> ThisWorkbook.Name Then
Workbooks.Open ThisWorkbook.Path & "\" & wb
With Workbooks(wb).Sheets(1)
.UsedRange.Offset(1).Copy ThisWorkbook.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1)
End With
Application.CutCopyMode = False
Workbooks(wb).Close False
End If
wb = Dir
Loop
Application.ScreenUpdating = True
End Sub

Display More

jolivanes · 2020-04-01T17:23:02+01:00

When you changed this

Code

Case Is = "$"

to this

Code

Case Is = "$", "N/A"

did it do what you intended?

jolivanes · 2020-04-01T06:22:19+01:00

This is what I understood it to be from what you asked for in your first post.

If they are not part of a larger string, this should work.

Code

Sub Maybe()
Dim i As Long, c As Range
Application.ScreenUpdating = False
For i = 1 To ThisWorkbook.Sheets.Count
With Sheets(i)
For Each c In .Range("H1:H" & .Cells(.Rows.Count, 8).End(xlUp).Row)
Select Case UCase(c.Value)
Case Is = UCase("Mem")
c.Resize(, 1).Insert Shift:=xlToRight
Case Is = UCase("Ser")
c.Resize(, 2).Insert Shift:=xlToRight
Case Is = "$"
c.Resize(, 4).Insert Shift:=xlToRight
Case Else
End Select
Next c
End With
Next i
Application.ScreenUpdating = True
End Sub

Display More

Change as you see fit.

Good luck

jolivanes · 2020-04-01T06:15:06+01:00

I am sorry. I must have totally missed the "AU" and "N/A", that you mentioned now, in your first post.

IMHO, your attachment does not even come close to your description in your first post

jolivanes · 2020-04-01T04:57:43+01:00

Just noticed your code. I guess that answers my question.

Are the "Mem", "Ser" and "$" single entries or part of a longer string?

Any chance of attaching a representative workbook?

jolivanes · 2020-04-01T04:56:50+01:00

Re: "MEM and all the contents in that row need to move right 1"

All the contents in that Row means, to me anyway, from Column A on. Does it mean the same to you because when it is "SER" it looks like from Column H on to the right.

jolivanes · Tuesday, 3:18 am

Code

Sub Maybe()
Dim c As Range
For Each c In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(2)
c.Offset(, 2).Value = "'" & Format(c, "MM-DD-YYYY")
Next c
End Sub

jolivanes · Mar 24th 2020

Headers are in top row. Change "Header" to the header name you're looking for.

Code

Sub Maybe()
Columns(Rows(1).Find("Header", , , 1).Column).Replace "Ms ", ""
End Sub

jolivanes · Mar 18th 2020

Re: "Not to mention shorter"

Columns(3).ColumnWidth = [Sheet2!A1]

jolivanes · Mar 16th 2020

Still waiting for either a positive or negative answer.

jolivanes · Mar 11th 2020

A very simple example.

If the Font color of Cell B1 is Index 3 (Red) as the result of conditional formatting and the Font color of Cell C1 is made Red (Index 3 or vbRed) manually.

Run This code;

Code

Sub Copy_Colors()
Cells(1, 2).Offset(4).Interior.ColorIndex = Cells(1, 2).Font.ColorIndex
Cells(1, 3).Offset(4).Interior.ColorIndex = Cells(1, 3).Font.ColorIndex
End Sub

If the conditional format rule for cell B1 would be to make the Font red if the Value of Cell B1 > 8 then you can use

Code

If Range("B1").Value > 8 Then Range("B1").Offset(4).Interior.ColorIndex = 3

jolivanes · Mar 10th 2020

You can't copy the results of conditional formatting.

You'll have to use the reasoning of the conditional formatting for that also

jolivanes · Mar 7th 2020

I guess this post does not rank under

"If anyone help me out am surely thankful to them but unnecessarily i will not thank....."

Excel VBA – Copy or Move files from one folder to another

jolivanes · Mar 6th 2020

Did it work?

jolivanes · Mar 5th 2020

Just to ease your mind.

Carim's suggestion is the best way to go.

But if you would have had "<> 0" in place of "= 0", you would have been in business.

BTW, next time use code tags around your code.

jolivanes · Feb 28th 2020

Change references like Folder names and paths where required.

The original files to be moved are in Column A, starting at Row 2.

The nenamed files are in Column B with the original and new name in the same Row.

Code

Sub Maybe()
Dim strPath_Old As String
Dim strPath_New As String
Dim i As Long
strPath_Old = "C:\Temp" '<---- Folder where the files to be renamed are in
strPath_New = "C:\Temp\Temp" '<---- Folder where the files are to be moved into
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
Name strPath_Old & "\" & Cells(i, 1) As strPath_New & "\" & Cells(i, 1).Offset(, 1).Value
Next i
End Sub

jolivanes · Feb 27th 2020

And another possibility.

Which one is the best depends on if there is a possibility of mistakes/differences in the naming of the files.

I guess you can always keep them in your library for future use.

Code

Sub Maybe_C()
Dim c As Range
For Each c In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
If InStr(c.Value, "_") <> 0 Then c.Value = Left(c, InStrRev(c.Value, "_") - 1) & ".docx"
Next c
End Sub

jolivanes · Feb 27th 2020

BTW, forgot to mention that in the future you're better off attaching a workbook.

That way people that are assisting you can try their suggestion before posting.

jolivanes · Feb 26th 2020

And without looping maybe.

Code

Sub Maybe_A()
Columns(1).Replace What:="_Translated.docx", Replacement:=".docx", LookAt:=xlPart
End Sub

Posts by jolivanes

Copy data from Multiple workbooks simultaneously into One excluding first row

Copy data from Multiple workbooks simultaneously into One excluding first row

move contents of cell to right based on cell value

move contents of cell to right based on cell value

move contents of cell to right based on cell value

move contents of cell to right based on cell value

move contents of cell to right based on cell value

Date Conversion to MM-DD-YYYY

Find replace using column Name

Set Column Width - question about shg code February 2008

Renaming a file names in folder.

Copy the cell background colour fromm the conditonal format applied to one range of cells to another range of cells

Copy the cell background colour fromm the conditonal format applied to one range of cells to another range of cells

Renaming a file names in folder.

Renaming a file names in folder.

VBA Code to Select and Delete Cells with "ShipTo" in it.

Renaming a file names in folder.

Renaming a file names in folder.

Renaming a file names in folder.

Renaming a file names in folder.