Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Reference Cell

0

I am trying to use the File Merger.xlsm to merge the file (hopefully multiple files in the future) to creat one file. 

After Merging, I'm trying to make a VBA code to copy all the values in File Merger.xlsm Design basis sheet A1:D7 to all the sheets in Report.xlsx. the code I wrote works after merging it but it does not work after deleting and merging the same file again. I was hoping to make a code that will work will different files (ex. Report1, Report2...etc) that has same sheet names(Basis, Report, conclusion...etc)

Any help will be much appreciated.

excel
posted by: mkang0120 on Wed Oct 18, 2023 at 12:09 am
Answer
Discuss

Discussion

Hi MKang and welcome to the Forum

That behaviour is expected- if a cel refers to another sheet, deleting that sheet means Excel can't follow the reference . There might be a way to show the reference but why do you want to (rather that get the correct error message)?
John_Ru (rep: 6377) Oct 18, '23 at 1:28 am
I have made a vba code that merges all the excel files selected. Trying to make a report. This is done by creating a button with macro assigned on first page and the second page includes all the information for the header. I was hoping to use this master merge file to generate reports for repetitive objects.

I was hoping there is a absolute reference/code that will pick up different values as the actual sheets change with same names.
mkang0120 Oct 18, '23 at 2:26 am
Thanks MKang for the reply but that detail should have been included in your original question really.

I can think of a non-VBA way to avoid the error at a worksheet level but it might not help in you case- I'd need to know more of what you're trying to do, preferably by seeing your existing code- please edit your original question and use the Add Files... button to upload a representative Excel file (without any personal data) to show your existing data and macros. It would help me (and others) if you also expanded the question text to give an example of how the error affects your code. Then I should be able to give specific help (later in my day hopefully).
John_Ru (rep: 6377) Oct 18, '23 at 2:59 am
Thanks for adding the files and expanding the question. I have to leave home now but will look in a few hours and answer or ask any questions I have. 
John_Ru (rep: 6377) Oct 18, '23 at 3:42 am
Hi again

I took a look at your code and I'm puzzled firstly by your sub CopyManyCellsToSheetsWithCodenames. A comment in it says:
' Loop through the destination sheets using codenames
and you loop through an array of sheet codenames but only write (repeatedly) to Sheet18 (System Sketch)- is this just for test purposes?

Your sub MergeExcelFiles seems to open one or more selected files (like Report.xlsx) and copy them to File Merger - is your idea to do that, then update the headers from sheet Design Conditions (usingCopyManyCellsToSheetsWithCodename) then save back to Report.xlsx and others?If the Report files have the same header block per sheet (rows 1:7), why not write directly to them instead? If so, would you write to all sheets?

Furthermore, when you copy headers, fo you want the values to be copied or references, e.g. that below (so they can be updated as that file is updated with time)?
=@'C:\Users\skang\Desktop\PSV Test\New folder\[80717035-PR-CAL-R5023 Evaluation.xlsx]Design Conditions'!A4:D4


Please comment so I know what you're aiming for.
John_Ru (rep: 6377) Oct 18, '23 at 12:32 pm
Add to Discussion



Answer the Question

You must create an account to use the forum. Create an Account or Login