SOLVED

Hide First Row of Spreadsheet, when using macro to save CSV file

%3CLINGO-SUB%20id%3D%22lingo-sub-2333485%22%20slang%3D%22en-US%22%3EHide%20First%20Row%20of%20Spreadsheet%2C%20when%20using%20macro%20to%20save%20CSV%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2333485%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20macro%20on%20the%20attached%20which%20saves%20information%20on%20a%20tab%20as%20a%20CSV%20file%20into%20a%20designated%20folder.%20The%20file%20path%20for%20the%20folder%20is%20shown%20on%20row%201%2C%20then%20the%20headings%20for%20data%20start%20on%20row%202.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECurrently%20when%20i%20run%20the%20macro%20it%20will%20save%20the%20entire%20sheet%2C%20including%20the%20filepath%20in%20row%201.%20As%20this%20file%20is%20used%20as%20an%20upload%20into%20various%20systems%2C%20i%20wish%20to%20have%20the%20sheet%20start%20from%20row%202%20(Headings)%2C%20and%20not%20save%20row%201.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20anyone%20have%20any%20suggestions%20on%20how%20to%20approach%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2333485%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAdmin%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EUser%20Adoption%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2334702%22%20slang%3D%22en-US%22%3ERe%3A%20Hide%20First%20Row%20of%20Spreadsheet%2C%20when%20using%20macro%20to%20save%20CSV%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2334702%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364226%22%20target%3D%22_blank%22%3E%40calof1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EChange%20the%20lines%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3E'Copying%20data%20to%20new%20sheet%0AdestinationSheet.Range(destinationSheet.Cells(1%2C%20sourceStartCol)%2C%20destinationSheet.Cells(sourceEndRow%20-%20sourceStartRow%2C%20sourceEndCol)).Value%20%3D%20sourceSheet.Range(sourceSheet.Cells(sourceStartRow%2C%20sourceStartCol)%2C%20sourceSheet.Cells(sourceEndRow%2C%20sourceEndCol)).Value%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eto%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3E'Copying%20data%20to%20new%20sheet%0AdestinationSheet.Range(destinationSheet.Cells(1%2C%20sourceStartCol)%2C%20destinationSheet.Cells(sourceEndRow%20-%20sourceStartRow%20%2B%201%2C%20sourceEndCol)).Value%20%3D%20sourceSheet.Range(sourceSheet.Cells(sourceStartRow%2C%20sourceStartCol)%2C%20sourceSheet.Cells(sourceEndRow%2C%20sourceEndCol)).Value%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2339528%22%20slang%3D%22en-US%22%3ERe%3A%20Hide%20First%20Row%20of%20Spreadsheet%2C%20when%20using%20macro%20to%20save%20CSV%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2339528%22%20slang%3D%22en-US%22%3EHi%20Hans%2C%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20for%20your%20assistance%2C%20very%20much%20appreciated.%20I%20have%20updated%20the%20row%2C%20i%20still%20find%20i%20have%20the%20top%20rows%20information%20shows%20on%20my%20data%20file.%20Do%20you%20have%20any%20ideas%20of%20what%20i%20have%20done%20incorrectly%20updating%20this%3F%3CBR%20%2F%3E%3CBR%20%2F%3EMany%20thanks%20for%20all%20your%20help.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2340798%22%20slang%3D%22en-US%22%3ERe%3A%20Hide%20First%20Row%20of%20Spreadsheet%2C%20when%20using%20macro%20to%20save%20CSV%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2340798%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364226%22%20target%3D%22_blank%22%3E%40calof1%3C%2FA%3E%20I%20think%20the%20problem%20was%20that%20you%20activate%20the%20source%20sheet.%20Plus%20sourceStartRow%20should%20be%204.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2342857%22%20slang%3D%22en-US%22%3ERe%3A%20Hide%20First%20Row%20of%20Spreadsheet%2C%20when%20using%20macro%20to%20save%20CSV%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2342857%22%20slang%3D%22en-US%22%3EHi%20Hans%2C%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20again%20for%20your%20help%20with%20this%2C%20very%20much%20appreciated.%3CBR%20%2F%3E%3CBR%20%2F%3EThis%20is%20now%20working%20exactly%20as%20i%20had%20hoped.%3CBR%20%2F%3E%3CBR%20%2F%3EMany%20thanks%2C%3C%2FLINGO-BODY%3E
Regular Contributor

Hi All,

 

I have a macro on the attached which saves information on a tab as a CSV file into a designated folder. The file path for the folder is shown on row 1, then the headings for data start on row 2.

 

Currently when i run the macro it will save the entire sheet, including the filepath in row 1. As this file is used as an upload into various systems, i wish to have the sheet start from row 2 (Headings), and not save row 1.

 

Does anyone have any suggestions on how to approach this?

 

Many thanks,

4 Replies

@calof1 

Change the lines

'Copying data to new sheet
destinationSheet.Range(destinationSheet.Cells(1, sourceStartCol), destinationSheet.Cells(sourceEndRow - sourceStartRow, sourceEndCol)).Value = sourceSheet.Range(sourceSheet.Cells(sourceStartRow, sourceStartCol), sourceSheet.Cells(sourceEndRow, sourceEndCol)).Value

to

'Copying data to new sheet
destinationSheet.Range(destinationSheet.Cells(1, sourceStartCol), destinationSheet.Cells(sourceEndRow - sourceStartRow + 1, sourceEndCol)).Value = sourceSheet.Range(sourceSheet.Cells(sourceStartRow, sourceStartCol), sourceSheet.Cells(sourceEndRow, sourceEndCol)).Value
Hi Hans,

Thanks for your assistance, very much appreciated. I have updated the row, i still find i have the top rows information shows on my data file. Do you have any ideas of what i have done incorrectly updating this?

Many thanks for all your help.
best response confirmed by calof1 (Regular Contributor)
Solution

@calof1 I think the problem was that you activate the source sheet. Plus sourceStartRow should be 4.

Hi Hans,

Thanks again for your help with this, very much appreciated.

This is now working exactly as i had hoped.

Many thanks,