loop for copying visible cells and delete \ dropdown list

[Krokolv]

Hi everyone,

I am still a "noob" in vba and I trying to create here a macro for looping through a dropdown list and copy everyone visible content. Until now I have the following code

Dim MF As Workbook
Dim Rng As Range
Dim Rg As Range
Dim InputRange As Range
Dim C As Range
Dim i As Long


'full file path to be referenced.
Set MF = Workbooks.Open("C:\Users\AGUIAMAR\Desktop\Masterfile.xlsm")
'Range to be copied
Set Rng = ActiveWorkbook.Worksheets("sold-to TT").Range("QS20:TD" & Cells(Rows.Count, 453).End(xlUp).Row).SpecialCells(xlCellTypeVisible)
'Filter range
Set Rg = ActiveWorkbook.Worksheets("sold-to TT").Range("TH18")
'Filter range
Set InputRange = Evaluate.Range("D75:D106").Validation.Formula1


i = 1
Application.ScreenUpdating = False
For Each C In InputRange
Rg = C.Value
Rng.Copy
Set wb = Workbooks.Open(Filename:="C:\Users\AGUIAMAR\OneDrive - adidas\TTs management files\0100\upload.csv", ReadOnly:=False)
Workbooks("upload.csv").Worksheets("upload").Range("A1").PasteSpecial Paste:=xlPasteValues
Application.ScreenUpdating = True
i = i + 1

Next C



End Sub

I have three problems until now with macro.

1 - The first is how I delete this message " this workbook contains links to one o more external ...". I tried already two methods, that didn't work.
2- the Macro it's iterating through the dropdown list but I think it's pasting over and over the last item pasted. That's why the pasted output is just the last item of the dropdown list. I think I need there count rows, off set or something. like this in the loop.
3- Is there a possibility of creating a if then during copy and paste inside of the loop. So there is a column "TE" to this variable ActiveWorkbook.Worksheets("sold-to TT").Range("QS20:TD" & Cells(Rows.Count, 453).End(xlUp).Row).SpecialCells(xlCellTypeVisible), which can be a string, so I would loop through the dropdown list and depending on the column td I would paste on differente files. lets say column td has 0100, 3800, 400 rows, so I would past the rows from 0100 on 0100 file, the rows from 3800 on 3800 file.

I hope I could make myself clear.
Thank you for the help,

Regards




it just copies what is there. The validation data is also a formula

 

[Krokolv]

I kind of solved the problem 2 but some part the code is no really copying correctly:

this is the paste of the first item of the dropdown list, but after the second it's correct.

So my new code would be :

Sub MAsterfileUpload()

Dim MF As Workbook
Dim Rng As Range
Dim Rg As Range
Dim InputRange As Range
Dim C As Range
Dim i As Long


'full file path to be referenced.
Set MF = Workbooks.Open("C:\Users\AGUIAMAR\Desktop\Masterfile.xlsm")
'Range to be copied
Set Rng = ActiveWorkbook.Worksheets("sold-to TT").Range("QS21:TD" & Cells(Rows.Count, 454).End(xlUp).Row).SpecialCells(xlCellTypeVisible)
'Dropdown list button
Set Rg = ActiveWorkbook.Worksheets("sold-to TT").Range("QT18")
'Filter range
Set InputRange = ActiveWorkbook.Worksheets("backend").Range("D75:D106").SpecialCells(xlCellTypeVisible)


i = 1
Application.ScreenUpdating = False
For Each C In InputRange
Rg = C.Value
Rng.Copy
Set wb = Workbooks.Open(Filename:="C:\Users\AGUIAMAR\OneDrive - adidas\TTs management files\0100\upload.csv", ReadOnly:=False)
Workbooks("upload.csv").Worksheets("upload").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
i = i + 1

Next C


End Sub