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!

    Files

    • 0411_Dr.xlsx

      (14.17 kB, downloaded 4 times, last: )
    • 0110_Dr.xlsx

      (13.56 kB, downloaded 6 times, last: )

    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.

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


    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!

    Files

    • test.xlsm

      (224 kB, downloaded 55 times, last: )

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

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


    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!


    Hello,


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

    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 :

    Code
    1. Sub addvalue()
    2. With ActiveSheet
    3. Worksheets(1).Select
    4. Dim cl As Range
    5. For Each cl In Range("L")
    6. cl = "'000" & cl
    7. Next cl
    8. End With
    9. End Sub

    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
    1. With wbkSrcBook.Sheets
    2. Worksheets(1).Select
    3. countSheets = countSheets + 1
    4. Worksheets(1).Rows(1).EntireRow.Delete
    5. Worksheets(1).Range("L:Q, T:V, Z:AP, AR:DC").EntireColumn.Delete
    6. Worksheets(1).Cells.EntireColumn.AutoFit
    7. Worksheets(1).Copy after:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)
    8. End With



    Code to disable saving of the files:

    Code
    1. wbkSrcBook.Close SaveChanges:=False

    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 :


    Code
    1. With wbkSrcBook.Sheets
    2. Worksheets(1).Select
    3. countSheets = countSheets + 1
    4. Worksheets(1).Range("L:Q, T:V, Z:AP, AR:DC").EntireColumn.Delete
    5. Worksheets(1).Cells.EntireColumn.AutoFit
    6. Worksheets(1).Copy after:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)
    7. End With

    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.


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

    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


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