I have made a macro to import the a excel file into a new file after truncating few initial rows
Here is the code for it:
Sub Import() ' Collect folder location, file name and sheet name from user Dim Prompt1, Prompt2, Prompt3, myDir, myFile, mySheet As String Dim ClCnt As Long 'column counter Prompt1 = "Enter the location of input file:" myDir = InputBox(Prompt1) Prompt2 = "Enter filename:" myFile = InputBox(Prompt2) Prompt3 = "Enter sheetname:" mySheet = InputBox(Prompt3) ' Source file open and copy contents after deleting 32 header lines ' Retain column headers from the input file With Workbooks.Open(myDir & myFile).Sheets(mySheet) ClCnt = .Range("[B][U]A33:CB33[/U][/B]").SpecialCells(xlConstants).Count ' Count the number of columns present With .Range("[B][U]a33[/U][/B]", .Range("a" & Rows.Count).End(xlUp)).Resize(, ClCnt) ThisWorkbook.Sheets(1).Range("a1").Resize(.Rows.Count, .Columns.Count).value = .value End With ActiveWorkbook.Close False End With End Sub
But i want to generalize this macro for every input file. In that case my starting row will not be the same as it is in this case (33 here). I want to ask the user for the starting row and then use it in this code to copy the data. What change can be made in this macro??