Home » Forums » Open Source Learning Center » Question and Answer

Copy,Paste,Transpose in VBA loop -Thank you so much!

Submitted by mytheresa on 10 April, 2013 - 15:12

Hello! I m new to macro and I need to copy the data from row 26:27, 36:37, 46:47 cells value which are converted into E9:F39, P9:Q39, AA9:AB39, 12 columns in the other workbook. How can I do if I want to import the data directly instead of making a copy of the report inside of my workbook by using VBA? Thanks a lot if someone can help me please?

‹ Looking to Hire VBA Expert for easy Coding ActiveX control textbox font keeps changing ›
»
Nick's picture

I'll start you off: recommend

Submitted by Nick on 10 April, 2013 - 17:00.

I'll start you off:

recommend looking here:
http://excelexperts.com/Learn-VBA-Record-a-Macro

you should be able to record a macro to do this.

»

Thank you if you could help fix the error!

Submitted by mytheresa on 10 April, 2013 - 18:52.

Hello

I knew how to record macro VBA and basic info. But there are some error occur to my code that I wrote.

Public intBotRow, intTopRow As Integer
'STR data range definers
Public blExit As Boolean
'process terminator
Public strSTRReport As String
'name of the STR Report
Public strYPTool As String
'name of the YP Tool
Public d, e, f As Integer

Sub LocateRange()
'Comments : Defines the top and bottom rows of the data on the STR Report.

Dim a, b As Integer

Windows(strSTRReport).Activate

With Sheets("Daily")

d = 9
Do Until Cells(d, 2).Value = ""
d = d + 1
Loop
'd = d - 5 'YTD
d = d - 3 'YTD

'e = d - 2
e = d - 9 'MTD

Do Until Left(Cells(e, 2).Value, 5) = "Total"
e = e - 1
If e < 6 Then
e = 9
GoTo NextE
End If
Loop
e = e + 1

NextE:
intTopRow = e

intBotRow = d - 8

Cells.Select
Selection.Copy

''Range(Cells(6, 2), .Cells(600, 50)).Copy
'Windows(strYPTool).Activate
'ActiveWorkbook.Sheets("Data").Select
'ActiveWorkbook.Sheets("Data").Cells(1, 1).Select
'Selection.PasteSpecial Paste:=xlValues
'ActiveSheet.Copy After:=Workbooks(strYPTool).Sheets("Instructions")
'ActiveSheet.Visible = False

End With
Windows(strYPTool).Activate
Sheets("Data").Activate
With Sheets("Data")
Cells(1, 1).Select
'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Application.CutCopyMode = False

End With

'Windows(strYPTool).Activate
'With ActiveWorkbook.Sheets("Data")
'Range(Cells(6, 2), .Cells(600, 50)).Select
'Cells(1, 1).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
'End With

End Sub

Sub GetData()
Dim a, b, c As Integer

'Get Period to Date Data

'For a = 1 To 3
For a = 1 To 6

Select Case a
Case 1
b = 1
'MyRow = d - 1
MyRow = d - 8
MyYPRow = 5
Case 2
b = 1
MyRow = d
MyYPRow = 6
Case 3
b = intBotRow - intTopRow
MyRow = intTopRow
MyYPRow = 11
Case 4
b = 7
MyRow = intBotRow + 1
MyYPRow = 42
Case 5
b = 2 '12/07/2011
MyRow = d + 1
MyYPRow = 51
Case 6
b = 1
MyRow = d
MyYPRow = 49
End Select

For c = 1 To b

End Sub

»