how to loop data from different sheets,in VBA

apeter5

New Member
Joined
Mar 20, 2018
Messages
7
I am learning VBA,
I have data in sheet1 18 rows with 5 columns and Sheet2 3 rows with 8 columns
I would like to loop data and print in NOTEPAD like,

  • Rows 1 - 6 from sheet1 then Row 1 from sheet2
  • Rows 7 - 12 from sheet1 then Row 2 from sheet2
  • Row 13 - 18 from sheet1 then Row 3 from Sheet2, so on.
Here is my code,


<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">Sub Looping()

Dim str As String
Dim MaxStrLen As String
Dim rest As Integer
Dim Lstr As Integer
Dim LMstr As Integer
Dim MStr As Integer
Dim LR As Range
Dim CNT As Integer

Dim LastRow As Long
Dim LastCol As Long
Dim LRow As Long
Dim LCol As Long
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim i As Long
Dim j As Long
Dim Page_Break As Long
Dim k As Long
Dim PB As Long
Dim x As Long
Dim y As Long

Dim rng As Range

Set rng = Range("A1:E6")

Dim FilePath As String

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
Set ws3 = Sheets("Sheet3")

Open
"C:\Users\Antony\Music\Excel Macros\Test.txt" For Output As #2

'''''FIRST FIVE LINES WILL PRINT IN THE NOTEPAD

With ws1

LastRow
= .Cells(.Rows.Count, 1).End(xlUp).Row
LastCol
= .Cells(1, .Columns.Count).End(xlToLeft).Column

LRow
= ws2.Cells(.Rows.Count, 1).End(xlUp).Row
LCol
= ws2.Cells(1, .Columns.Count).End(xlToLeft).Column

BlkSize
= 6 'data consists of blocks of 6 rows
For i = 1 To LastRow
sOut
= vbNullString
LengthRow
= i
Do While LengthRow > BlkSize
LengthRow
= LengthRow - BlkSize
Loop

'LengthRow points to row where char length is to be taken from
For j = 1 To LastCol
str
= .Cells(i, j).Value
If str <> Empty Then
MStr
= ws2.Cells(LengthRow, j).Value
Lstr
= Len(str)
rest
= MStr - Lstr
sOut
= sOut & str & Space(rest)

Else
MStr
= ws2.Cells(LengthRow, j).Value
Lstr
= Len(str)
rest
= MStr - Lstr
sOut
= sOut & str & Space(rest)
End If
Next
Print
#2, sOut
Next

End With

'''''LAST LINE WILL PRINT IN THE SAME NOTEPAD

With ws3
LRow
= .Cells(.Rows.Count, 1).End(xlUp).Row
LCol
= .Cells(1, .Columns.Count).End(xlToLeft).Column

slast
= vbNullString

For k = 2 To LRow
str
= Join(Application.Transpose(Application.Transpose(.Cells(k, "A").Resize(1, LastCol).Value)), "@#")
str
= Replace(str, "=", vbNullString)

Print
#2, str
Next

Endtext
= "EODR"

Print
#2, slast & Endtext


End With

'Loop

Close
#2

End Sub

Kindly help me to solve this. thanks in advance.
</code>
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top