Solved: VBA Copy and Paste Help

[gatorcc]

I am very new to creating VBA scripts and I have report I need a copy and paste VBA macro. Here is what I am in need of.

I have a report that is generated daily. Each row of the report can be
divided into 15 separate items with each item having 6 sub items. All of
this is on one row.

For example on my sheet labled "data" cell C2 = item 1a, D2 = item 1b,
E2 = item 1c, F2 = item 1d, G2 = item 1e, and H2 = item 1f all the way to CN2 = item 15g.

On my "Report" Sheet cell c2 from the Data sheet needs to be pasted in cell C8 of Report Sheet , cell D2 from the Data sheet needs to be pasted in cell D8 of Report Sheet, Data E2 = Report E8, Data F2 = Report F8, Data G2 = Report G8, and Data H2 = Report H8.

Then if there is some data in Data sheet I2 place that on Reports page C9
and so on until an empty column is hit or Data Cell CN2 is hit. Once an
empty cell or CN2 is reached then the macro would start over on Data cell C3 and continue. This would continue until the end of the data is reached.

There is a catch on the Report sheet it is segmented every 25 rows. So Rows 8-32, Rows 38-62 ...etc.

I know I was long winded but I have absolutely no idea how to do this other than doing it manually and I don't want to do that. In a perfect world there would be a button I press on the Reports sheet and it does it for me!

Any help would be greatly appreciated.

[mdmackillop]

Welcome to VBAX,
Can you post a workbook sample? Use Manage Attachments in the Go Advanced reply section.
Regards
MD

[gatorcc]

Welcome to VBAX,
Can you post a workbook sample? Use Manage Attachments in the Go Advanced reply section.

MD

Here is a sample. I included a sample out put on the Data page.
10998

[mdmackillop]

It's not obvious what fields get copied to where. There is no corresponding data on your Source page

[gatorcc]

It's not obvious what fields get copied to where. There is no corresponding data on your Source page

I did the manual copy and paste to show you an idea. Pretty much the macro would search row 2 on the Data sheet then when it hits an empty cell start on row 3 and so on.
10999

[mdmackillop]

Option Explicit
Sub CopyData()
Dim wsData As Worksheet
Dim wsTgt As Worksheet
Dim tRw As Long, sRw As Long, sCol As Long
Set wsData = Sheets("Data")
Set wsTgt = Sheets("Pay Online")
tRw = 8
sCol = 3
sRw = 2
Do
'Copy first data
wsData.Cells(sRw, sCol).Resize(, 6).Copy wsTgt.Cells(tRw, 3)
'Increment target row
tRw = tRw + 1
'Move source to right
sCol = sCol + 6
'Check for Source limits
If wsData.Cells(sRw, sCol) = "" Or sCol > 92 Then
'If limit reached, start new source row
sRw = sRw + 1
sCol = 3
End If
'Check for end of Report sheet
If (tRw - 3) Mod 30 = 0 Then
'If end reached, go to first row on new sheet
tRw = tRw + 5
End If
'Exit loop at empty row
If Application.Count(wsData.Rows(sRw)) = 0 Then Exit Do
Loop
End Sub

[gatorcc]

Option Explicit
Sub CopyData()
Dim wsData As Worksheet
Dim wsTgt As Worksheet
Dim tRw As Long, sRw As Long, sCol As Long
Set wsData = Sheets("Data")
Set wsTgt = Sheets("Pay Online")
tRw = 8
sCol = 3
sRw = 2
Do
'Copy first data
wsData.Cells(sRw, sCol).Resize(, 6).Copy wsTgt.Cells(tRw, 3)
'Increment target row
tRw = tRw + 1
'Move source to right
sCol = sCol + 6
'Check for Source limits
If wsData.Cells(sRw, sCol) = "" Or sCol > 92 Then
'If limit reached, start new source row
sRw = sRw + 1
sCol = 3
End If
'Check for end of Report sheet
If (tRw - 3) Mod 30 = 0 Then
'If end reached, go to first row on new sheet
tRw = tRw + 5
End If
'Exit loop at empty row
If Application.Count(wsData.Rows(sRw)) = 0 Then Exit Do
Loop
End Sub


It works!!!
Thank you so much!!!

[gatorcc]

What would I need to add to the macro to fill in the bank number if that is needed in the future?

[mdmackillop]

Add this line

'Copy first data
wsData.Cells(sRw, 2).Copy wsTgt.Cells(tRw, 2)

[gatorcc]

Thank you.

[gatorcc]

I have added a couple of things to the macro that you helped me with now I need an if statement that says if data is in the data worksheet column CO print that under the Account heading on worsheet Pay Online else print column CP.
11078

[mdmackillop]

Just join the two cells and use the result.
Dim Account As String
'Copy first data
wsData.Cells(sRw, 2).Copy wsTgt.Cells(tRw, 2)
wsData.Cells(sRw, sCol).Resize(, 6).Copy wsTgt.Cells(tRw, 3)
Account = wsData.Cells(sRw, 93) & wsData.Cells(sRw, 94)
wsTgt.Cells(tRw, 9) = Account
wsData.Cells(sRw, 97).Copy wsTgt.Cells(tRw, 10)
wsData.Cells(sRw, 98).Copy wsTgt.Cells(tRw, 11)