Hello,
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:
Code
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 SubBut 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??