set rows & columns.

[sky1in5]

 ' Find the last row with data
                    rnum = LastRow(sh)
        
                    ' Set Column A for item name
                    Set destrangeName = sh.Cells(rnum + 1, "A")
                    ' Set Column B for article number
                    Set destrangeArticle = sh.Cells(rnum + 1, "B")
                    ' Set Column C for start date
                    Set destrangeStart = sh.Cells(rnum + 1, "C")
                    ' Set Column D for end date
                    Set destrangeEnd = sh.Cells(rnum + 1, "D")
                    ' Set Column E for promo price
                    Set destrangePrice = sh.Cells(rnum + 1, "E")
        
                    ' Copy item name from other worksheets and insert into current worksheet
                    GetData FName(N), SheetNum(i), ws.Range("A2"), destrangeName, False, False
                    ' Copy article number from other worksheets and insert into current worksheet
                    GetData FName(N), SheetNum(i), ws.Range("B2"), destrangeArticle, False, False
                    ' Copy start date from other worksheets and insert into current worksheet
                    GetData FName(N), SheetNum(i), ws.Range("C2"), destrangeStart, False, False
                    ' Copy end date from other worksheets and insert into current worksheet
                    GetData FName(N), SheetNum(i), ws.Range("D2"), destrangeEnd, False, False
                    ' Copy promo price from other worksheets and insert into current worksheet
                    GetData FName(N), SheetNum(i), ws.Range("E2"), destrangePrice, False, False

i set 1 variable to 1 row, 1 getdata to 1 row.
is there anywhere i can set 1 variable for counting row and columns, 1 getdata for all input.

fname = files
sheetnum = worksheet
ws.range = cell on sheet2

 

[AlphaFrog]

Maybe try something like this...

[COLOR=green]' Find the last row with data[/COLOR]
rnum = Lastrow(sh) + 1
GetData FName(N), SheetNum(i), ws.Range("A2:E2"), sh.Range("A" & rnum & ":E" & rnum), [COLOR=darkblue]False[/COLOR], False

 

[sky1in5]

Public Sub GetData(SourceFile As Variant, SourceSheet As String, _
                   SourceRange As String, TargetRange As Range, Header As Boolean, UseHeaderRow As Boolean)

GetData FName(N), SheetNum(i), ws.Range("A2:E2"), sh.Range("A" & rnum & ":E" & rnum), False, False

run error 13 type mismatch

 

[sky1in5]

Public Sub GetData(SourceFile As Variant, SourceSheet As String, _
                   SourceRange As String, TargetRange As Range, Header As Boolean, UseHeaderRow As Boolean)

 GetData FName(N), SheetNum(i), ws.Range("A2"), sh.Range("A" & rnum & ":E" & rnum), False, False

for ws.Range("A2") , i want to set 5 range. a2 b2 c2 d2 e2. can i need to do a getdata for 1 time instead of 5 times?

 

[sky1in5]

*can i do a getdata for 1 time instead of 5 times?

 

[sky1in5]

A2 B2 C2 D2 E2
this is the 5 cells which i wish to insert into

Public Sub GetData(SourceFile As Variant, SourceSheet As String, _
                   SourceRange As String, TargetRange As Range, Header As Boolean, UseHeaderRow As Boolean)


GetData FName(N), SheetNum(i), "here to insert cells", sh.Range("A" & rnum & ":E" & rnum), False, False

how do i code it? thanks in advance.

can i use ws.Range("A2").Offset(0, -1) ?

 

[sky1in5]

bump!

 

[sky1in5]

 For N = LBound(FName) To UBound(FName)
                              
            sFName = FName(N)
            
            Set SheetNum = FunctionModule.GetSheetsName(sFName)
            
            For i = 1 To SheetNum.Count
                    ' Find the last row with data
                    rnum = LastRow(sh) + 1
                          
                    ' Copy from other worksheets and insert into current worksheet
                    GetData FName(N), SheetNum(i), ws.Range("A2"), sh.Range("A" & rnum & ":E" & rnum), False, False
            Next i
        Next N

changing of input from ws.range("A2") to ws.range("A2:B2:C2:D2:E2")
changing of output from sh.range("A") to sh.range("A:B:C:D:E")
sh is worksheet1, ws is worksheet2
so result

WS:SH
A2:A B2:B C2:C D2:D E2:E

ws.range ("A2:B2:C2:D2:E2") got range in it. A2-H10 B2-U19 C2-AV6 D2-BE6 E2-W38

why i want set range in it is because the range will change frequently.

I can do it with 5 getdata, but i want to try and do it with 1 getdata because it will be faster. (700 files to generate)

 

[sky1in5]

bump

 

[sky1in5]

no expert can help a poor kid? )):