VBA to import cells from excel with blank rows between them

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

I have found this and I want to share with ALL of you:

Sub PasteCellsFromOtherFiles()

Dim Pasta As String
Dim Arquivo As String
Dim linha As Integer

'inputbox to set the number of lines to skip between pasted sections
linha = InputBox("Enter how many rows to skip between blocks", "Number of rows to skip")

'opens a window to select the folder where the files are
With Application.FileDialog(msoFileDialogFolderPicker)
.Show
Pasta = .SelectedItems(1)
End With

'association of the first file in the selected folder to the variable "arquivo"
Arquivo = Dir(Pasta & "\" & "*.xls*")

'repeat command that copies files
Do
Workbooks.Open Pasta & "\" & Arquivo

'copy and paste cells according to the variable "linha" in a line below the last line completed
ActiveSheet.UsedRange.Copy _
ThisWorkbook.Sheets("Sheet1").Range("A" & Cells.Rows.Count).End(xlUp).Offset(1 + linha, 0)
Workbooks(Arquivo).Close

'associate the next file in the selected folder to the variable "arquivo"
Arquivo = Dir
Loop While Arquivo <> ""
Application.CutCopyMode = False

'transform collage in paste values
Cells.Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End Sub

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)