Posts by CedricYong

Apologies, this is the full code if needed.

Hi.

For the past hour I have been trouble shooting my VBA code to resolve the following error when trying to merge excel files with the code, however this error pops up every time on the 2nd phase of the code where it copies all sheets' data onto a single sheet named "Combined", can anyone assist to troubleshoot my code?

I have attached test files just in case it is needed. Thanks!

Hi all,

Once again, it's the newbie Cedric requesting for help.

Well I was figuring out how to automate the process of saving files while adding password, then saving the same file into another folder using file directory.

I was looking and trying to learn over the few days, but can't seem to find a solution.

So hence I am requesting if anyone is willing to give a helping hand.

The requirements and benchmarking file are as attached in the excel file.

Quick Save.test.xlsx

you would need to create blank excel files:

Folder A
File1

File2

File3

Folder B

File4

File5

File6
File7_No PW

Folder C

File8

File9_No PW

Folder "Add PW"

Once again, thanking all for your help/inputs in advance.

Quote from royUK

Try this. Column E seems to have entries for the whole database so I've used that

Ty roy, that worked perfectly for my needs!

Quote from royUK

You can't use CurrentRegion if the data has empty Rows and/or empty columns. Try this

Code

1. Option Explicit
2. Sub x()
3. Dim oWs As Worksheet
4. Dim iX As Integer
5. For Each oWs In ThisWorkbook.Worksheets
6. With oWs
7. Select Case .Name
8. Case "Script", "Combined"
9. 'Do nothing
10. Case Else
11. iX = iX + 1
12. ''/// only copy headers first time
13. If iX = 1 Then
14. .Range(.Cells(7, 1), .Cells(.Rows.Count, 2).End(xlUp)).Copy Sheets("Combined").Range("A1")
15. Else
16. .Range(.Cells(8, 1), .Cells(.Rows.Count, 2).End(xlUp)).Copy Sheets("Combined").Cells(.Rows.Count, 1).End(xlUp).Offset(1)
17. End If
18. End Select
19. End With
20. Next oWs
21. End Sub

Display More

However Roy, even with your amendments, the script fails to combine all the data onto one. I've attached a file for benchmarking, hope you find it useful as I cannot resolve the issue, and I do appreciate your help greatly!

If everything is working, there should be 326 rows of data.

Thanks in advance!

Quote from CedricYong

Hi Roy, thank you very much for your assistance, I tried inputting your code, however it is running into error :

Run Time error 9 "Subscript out of range"

Any idea what would be causing this?

My full code if it helps

Code

1. Sub Combine()
3. Dim oWs As Worksheet, TargetWS
4. Dim rRng As Range
5. Dim iX As Integer
6. Dim ws As Worksheet
8. For Each oWs In ThisWorkbook.Worksheets
9. With oWs
10. Select Case .Name
11. Case "Script", "Combined"
12. 'Do nothing
13. Case Else
14. iX = iX + 1
15. ''/// copy headers first time
16. If iX = 1 Then
17. .Range(.Cells(7, 1), .Cells(.Rows.Count, 2)).Copy Sheets("Combined").Range("A1")
18. Else
19. .Range(.Cells(8, 1), .Cells(.Rows.Count, 2)).Copy Sheets("Combined").Cells(.Rows.Count, 1).End(xlUp).Offset(1)
20. End If
21. End Select
22. End With
23. Next oWs
25. Set ws = ActiveSheet
26. With ActiveSheet
27. Worksheets(1).Select
28. Dim last_row As Long
29. last_row = .Cells(.Rows.Count, "F").End(xlUp).Row - 1
30. MsgBox (last_row), Title:="Number of transactions"
31. End With
33. With ActiveSheet
34. Worksheets(1).Select
35. Dim sht As Worksheet
36. ThisWorkbook.Worksheets("Combined").Cells.EntireColumn.AutoFit
37. End With
38. End Sub

Display More

Display More

scratch this qn, as I forgot to add the worksheet "Combined" hence the error.

Quote from royUK

You can't use CurrentRegion if the data has empty Rows and/or empty columns. Try this

Code

1. Option Explicit
2. Sub x()
3. Dim oWs As Worksheet
4. Dim iX As Integer
5. For Each oWs In ThisWorkbook.Worksheets
6. With oWs
7. Select Case .Name
8. Case "Script", "Combined"
9. 'Do nothing
10. Case Else
11. iX = iX + 1
12. ''/// only copy headers first time
13. If iX = 1 Then
14. .Range(.Cells(7, 1), .Cells(.Rows.Count, 2).End(xlUp)).Copy Sheets("Combined").Range("A1")
15. Else
16. .Range(.Cells(8, 1), .Cells(.Rows.Count, 2).End(xlUp)).Copy Sheets("Combined").Cells(.Rows.Count, 1).End(xlUp).Offset(1)
17. End If
18. End Select
19. End With
20. Next oWs
21. End Sub

Display More

Hi Roy, thank you very much for your assistance, I tried inputting your code, however it is running into error :

Run Time error 9 "Subscript out of range"

Any idea what would be causing this?

My full code if it helps

Hi all,

I have some issue with the following code :

My worksheets tend to have some blank cells in column A and B, so the current script only pulls data where there are non-empty cells in column A, is there a workaround here as I would want to include the rows that has empty cells in column A as well in my "Combined" Sheet. Please refer to attached for a picture reference. Thanks for reading!

Quote from royUK

Why bot use a Custom NumberFormat?

Well that's another way to do it, but I was hoping to automate the process instead, since most of my process are automated already, hence trying to automate one more step.

Quote from davesexcel

Hello,

Code

1. Dim sh As Worksheet
2. Dim rng As Range, c As Range
4. Set sh = Sheets(1)
5. With sh
6. Set rng = .Range("L2:L" & .Cells(.Rows.Count, "L").End(xlUp).Row)
7. For Each c In rng.Cells
8. If c <> "" Then
9. With c
10. .NumberFormat = "@"
11. .Value = "000" & c
12. End With
13. End If
14. Next
15. End With

Display More

Hi! Thank you very much for your help as well! Tried both codes, and both works perfectly for my needs! Thank you once again

Quote from jolivanes

Depending on the size of your data range, this could be faster because it does not loop.

Code

1. Sub addvalue_2()
2. With Sheets("Sheet1").Columns(12).SpecialCells(xlCellTypeConstants, xlNumbers)
3. .Value = "'000" & .Value
4. End With
5. End Sub

Thank you very much for the advise. This code works perfectly!

Hi all,

I need help with my code as I can't seem to add the text value " '000 " at the beginning of each cell that contains value in column L, but i'm running into errors and out of options

What I hope to achieve:

1. Add " '000 " at the beginning of each cell that contains value, but ignoring the header row which is A1

my current code :

Have solved my first problem, however the code I am about to provide, only works if you do not plan on saving the files that you're importing data from.

Code to import without row 1: Worksheets(1).rows(1).EntireRow.Delete

Code to disable saving of the files:

Hi all readers! Good morning/aftn/evening.

I really do need some help over here regarding about some functions I've never learned. The following are the current problems I am facing, which I hope I can find the solution here.

All your help is deeply appreciated and thanks in advance!

1.) I am trying to copy data from multiple files onto one sheet (Done), but I want to exclude row "1" while doing so, I don't know the function for that as I am not that experienced yet.

2.) (I'm not sure if this is possible) In column "L", ignoring the header row, which is row 1 after the solution above^, otherwise row 2, I would like to add some values to the column's data in all rows.

For e.g, right now the data is a string of number "1234123412341234" and many other combination of 16 digit numbers, I am trying to add three 0s infront of each cell value, making it

"000xxxxxxxxxxxxxxxx"

x=the cell's number value

This is my current code :

Quote from royUK

You still need to use the worksheet

Code

1. For Each wksCurSheet In ThisWorkBook.Sheets
2. wksCurSheet.Range("L:Q, T:V, Z:AP, AR:DC").EntireColumn.Delete
3. Next

Right, forgot about that. Your version is the final correct one. Thanks much!

Quote from royUK

Share your solution in case it helps anyone with a similar problem, also you might still get an improved suggestion. I would think this is the corrected code

Code

1. For Each wksCurSheet In wbkSrcBook.Sheets
2. wksCurSheet.Columns("L:Q,T:V,Z:AP,AR:DC").EntireColumn.Delete

Tried that actually, had errors for that one.

The solution was to change from Columns to Range, because I am trying to delete multiple range of columns, hence range.

Solved. Thank you

Quote from CedricYong

Hi,

I have a code to delete a set of columns from my data sheet. When the code executes fully, I didn't get the end result I want as none of the columns are deleted, in addition, it always prompts up an error message and when I pressed "debug",

It's this line of code that is causing the issue, and I can't seem to figure out why.

Error message : Run time error "13", Type mismatch

ignore the "Next" as this is supposed to be part of a larger code

Code

1. Next
3. For Each wksCurSheet In wbkSrcBook.Sheets
4. Columns("L:Q,T:V,Z:AP,AR:DC").EntireColumn.Delete
7. Next

Display More

Forgot to add in, the 2nd line of the code is meant to select every sheet in the file, which works perfectly normal. So it is the 3rd line that is causing the issue

Hi,

I have a code to delete a set of columns from my data sheet. When the code executes fully, I didn't get the end result I want as none of the columns are deleted, in addition, it always prompts up an error message and when I pressed "debug",

It's this line of code that is causing the issue, and I can't seem to figure out why.

Error message : Run time error "13", Type mismatch

ignore the "Next" as this is supposed to be part of a larger code

Quote from royUK

Sorry, it should be/ The cell needs to be within the data

Code

1. ActiveSheet.Range("A1").CurrentRegion.Rows.Count-1

That worked splendid, thank you very much!